dimanche 28 juin 2015

T SQL aggregate query not working

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