Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Null on Null Input | False |
| Schema Bound | False |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @date_start | date | 3 | |
| @date_end | date | 3 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE function dbo.aw_fn_scrap_in_join_all(@date_start date, @date_end date)
returns @tmp_scrapin_join table (kdbrg varchar(255),qty decimal(24,2))
AS
BEGIN
insert into @tmp_scrapin_join
select xgabung.kdbrg,
isnull(xgabung.qty,0) as qty
from(
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_wire else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR01%'
group by kdbrg
union all
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_copper else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR02%'
group by kdbrg
union all
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_pvc else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR03%'
group by kdbrg
union all
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_scrapkemasan else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR04%'
group by kdbrg
) as xgabung
RETURN
END
GO
GO
CREATE function dbo.aw_fn_scrap_in_join_all(@date_start date, @date_end date)
returns @tmp_scrapin_join table (kdbrg varchar(255),qty decimal(24,2))
AS
BEGIN
insert into @tmp_scrapin_join
select xgabung.kdbrg,
isnull(xgabung.qty,0) as qty
from(
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_wire else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR01%'
group by kdbrg
union all
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_copper else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR02%'
group by kdbrg
union all
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_pvc else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR03%'
group by kdbrg
union all
select kdbrg,
sum( case when date_start >= @date_start and date_end<=@date_end then total_scrapkemasan else 0 end ) as qty
from AW.DB_PASI_20201106.dbo.tbl_real_scrap_hdr where kdbrg LIKE '%SCR04%'
group by kdbrg
) as xgabung
RETURN
END
GO
Depends On
No items found