SUM CASE WHEN DISTINCT or MAX

[KOVMoe]

he numbers for the pkg are correct and the scan are wrong. I have several packages for each scan and cannot get distinct or max to work. Any one have any ideas?


select reg_nr, v.loc_na,v.loc_sys_nr,count(distinct pkg_tck_nr)as pkg,
sum(case when(convert(decimal(5,2),substring(pkg_xcp_rpt_tm,1,5))+UTC_TCF_MT_QY)<08.00 then 1 else 0 end)as scan,
sum(case when (pkg_del_dt = '2006-05-02') then 1 else 0 end)as del,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))between 08.00 and 08.59)and (pkg_del_dt = '2006-05-02') then 1 else 0 end)as pu8,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))between 09.00 and 09.59)and (pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu9,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))between 10.00 and 10.59)and (pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu10,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))between 11.00 and 11.59)and (pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu11,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))>= 12.00)and (pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu12

from tsphdtl_detail d
inner join vlocref v on d.dis_nr = v.loc_nr
inner join vlocref l on v.loc_nr = l.loc_sys_nr
where v.par_loc_sys_nr = '7'
and PKG_XCP_RPT_DT = '2006-05-02'
and SUBSTRING(pkg_tck_nr,9,2)in('01')
group by reg_nr, v.loc_na,v.loc_sys_nr
order by reg_nr



Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[SQLBill]

DATEPART(HOUR, ......) returns an integer, not a decimal. Plus it drops the leading zero (01 becomes 1). Try:
sum(case when((DATEPART(HOUR, PKG_DEL_TM)) = 8)
etc.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

sum... case... when... distinct... max... ... what... is... query... supposed... to... do?



Seriously - few words about what is query supposed to do would be nice

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[KOVMoe]

sorry, posted in haste. I am looking to see what time a package was picked up and the date it was delivered. The problem i have is that the unique identifier is scanned several times and i need to get the last scan and count it. Currently the location that i am looking at has 13 packages for date of 5/2 and the pick up before 8 shows 46 packages because the scanning.

select reg_nr, v.loc_na,v.loc_sys_nr,count(distinct pkg_tck_nr)as pkg,
sum(case when(convert(decimal(5,2),substring(pkg_xcp_rpt_tm,1,5))+UTC_TCF_MT_QY)<08.00 then 1 else 0 end)as scan,
sum(case when (pkg_del_dt = '2006-05-02'and pkg_del_rsl_cd = 'del') then 1 else 0 end)as del,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))= 8)and(pkg_del_dt = '2006-05-02') then 1 else 0 end)as pu8,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))=9)and(pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu9,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))= 10)and(pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu10,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))= 11)and(pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu11,
sum(case when((DATEPART(HOUR, PKG_DEL_TM))>= 12.00)and(pkg_del_dt = '2006-05-02')then 1 else 0 end)as pu12

from tsphdtl_detail d
inner join vlocref v on d.dis_nr = v.loc_nr
inner join vlocref l on v.loc_nr = l.loc_sys_nr
where v.par_loc_sys_nr = '7'
and PKG_XCP_RPT_DT = '2006-05-02'
and SUBSTRING(pkg_tck_nr,9,2)in('01')
group by reg_nr, v.loc_na,v.loc_sys_nr
order by reg_nr


Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[SQLBill]

Could you give us some sample data?

-SQLBill

Posting advice: FAQ481-4875