I was trying to find an aggregate of all the packages and package items which are late based on the Ship By Date.This is what I have so far and the counts are not right at the last aggregate query.I am expecting a count of 1 for packages(PackageId 123456) and Package Items which are due for shipping today(Assuming today is 06/27/2015)
Below are the sample code am stuck at WHName and WHId represent the warehouse name and Id Greatly appreciate your help Thanks
use test
go
create table #packages
(
PackageId int ,
WHName varchar(100),
WHId int,
ShipByDate date
)
insert into #packages
values
(
123,
'JAX',
1,
'06/25/2015'
),
(
1234,
'OH',
2,
'06/26/2015'
),
(
12345,
'JAX',
1,
'06/24/2015'
),
(
123456,
'DS',
3,
'06/27/2015'
),
(
1234567,
'DS',
3,
'06/27/2015'
)
create table #packageItems
(
PackageItemId int,
PackageId int,
Qty int
)
insert into #PackageItems
values
(
1,
123,
2
),
(
2,
123,
0
),
(
3,
1234,
3
),
(
4,
12345,
2
),
(
5,
123,
3
),
(
6,
123456,
3
),
(
7,
12345,
10
)
create table #PackageSum
(
PackageId int,
ItemsCount int
)
insert into #PackageSum(PackageId,ItemsCount)
select
pki.PackageId,
count(pki.PackageItemId) as Items
from
#packageItems pki
inner join #packages pk
on pki.PackageId = pk.PackageId
where
pki.Qty > 0
group by
pki.PackageId
select * from #Packages
select * from #PackageItems
select * from #PackageSum
declare @Now datetime2(7) = getdate();
select
pk.WHId,
pk.WHName,
SUM(case when datediff(day,pk.ShipByDate,@Now) = 0 then 1 else 0 end) as PackagesDueToday,
SUM(case when datediff(day,pk.ShipByDate,@Now) = 0 then pks.ItemsCount else 0 end) as PackageItemsDueToday,
SUM(case when datediff(day,pk.ShipByDate,@Now) between 1 and 2 then 1 else 0 end) as Packages1To2DaysOld,
SUM(case when datediff(day,pk.ShipByDate,@Now) between 1 and 2 then pks.ItemsCount else 0 end) as PackageItems1To2DaysOld
from
#packages pk
inner join #PackageSum pks
on pk.PackageId = pks.PackageId
group by
pk.WHId,
pk.WHName
drop table #packages
drop table #PackageItems
drop table #PackageSum
Aucun commentaire:
Enregistrer un commentaire