Big Problem or Little Mind? 1

[KOVMoe]

I am trying to look at 3 fileds to get information when a certain object was found, scanned and the number of the part. I can count the individual part numbers, but I run into problems when I try to count the number of parts picked up between 2 times. Here is my code.

declare @dtPuDate datetime
set @dtPuDate = '04/18/2006'
declare @dtYesterday datetime
set @dtYesterday = '04/18/2006'
SELECT
V.REG_LOC_SYS_NR
,F.LOC_NA
,COUNT(DISTINCT(D.PKG_ALT_REF_NR))AS PACKAGES
,count((CASE WHEN (LEFT(D.DCV_TM,4) + (convert(decimal(4,2),GMT_FCR_NR)) <= 8.5) THEN 1 ELSE 0 END))AS SCANTIME
,SUM((CASE WHEN ((SUBSTRING(CAST(D.PKG_DEL_TM AS CHAR),13,2) BETWEEN 8 AND 9) AND (D.PKG_PU_DT = @dtPuDate))THEN 1 ELSE 0 END))PU8
,SUM((CASE WHEN ((SUBSTRING(CAST(D.PKG_DEL_TM AS CHAR),13,2) BETWEEN 9 AND 10) AND (D.PKG_PU_DT = @dtPuDate))THEN 1 ELSE 0 END))PU9
,SUM((CASE WHEN ((SUBSTRING(CAST(D.PKG_DEL_TM AS CHAR),13,2) BETWEEN 10 AND 11) AND (D.PKG_PU_DT = @dtPuDate))THEN 1 ELSE 0 END))PU10
,SUM((CASE WHEN ((SUBSTRING(CAST(D.PKG_DEL_TM AS CHAR),13,2) BETWEEN 11 AND 12) AND (D.PKG_PU_DT = @dtPuDate))THEN 1 ELSE 0 END))PU11
,SUM((CASE WHEN ((SUBSTRING(CAST(D.PKG_DEL_TM AS CHAR),13,2) BETWEEN 12 AND 17) AND (D.PKG_PU_DT = @dtPuDate))THEN 1 ELSE 0 END))PU12
,SUM((CASE WHEN ((DATEDIFF(DD,D.DAT_VAL_DT, D.PKG_PU_DT)BETWEEN 1 AND 6))THEN 1 ELSE 0 END))PUDAT
FROM TSPHDTL_DETAIL D
INNER JOIN DBO.VLOCREF V ON D.PKG_DTN_BLD_CD = V.LOC_SYS_NR
INNER JOIN DBO.VLOCREF F ON V.REG_LOC_SYS_NR = F.LOC_SYS_NR

WHERE(D.DAT_VAL_DT = @dtYesterday)
AND D.SHR_AC_NR IN(SELECT SHR_AC_NR FROM DBO.TSPHSHP_SHIPPER)
AND V.CNY_CD = 'US'
GROUP BY V.REG_LOC_SYS_NR, F.LOC_NA
ORDER BY V.REG_LOC_SYS_NR, F.LOC_NA


I know this will be an easy solution, but I cannot see it. Thanks

Thank you,

Moe-King of the Village Idiots.

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

 

[JohnDTampaBay]

This is the part that I believe you are having issues with.

SUBSTRING(CAST(D.PKG_DEL_TM AS CHAR),13,2)

You're trying to count based on the the hour portion of a datetime/smalldatetime column. I would imagine your counts are a little wonky because CASE picks the first statement that resolves as TRUE. The hour of a part picked up at 9:15 is 9 and BETWEEN 8 AND 9 is TRUE, so the the part counted. You will need to include a time factor to get accurate counts.

SUM(CASE WHEN ( [b](Convert(varchar(13), D.PKG_DEL_TM, 114) BETWEEN '08:00:00:000' AND '08:59:59:999')[/b]
		AND	D.PKG_PU_DT = @dtPuDate	)
	THEN 1 ELSE 0 END) PU8

Does that do the trick? Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[KOVMoe]

YOU DA MAN!!!!!! Now do see why I am King ov the Village Idiots? I work very hard for that title.

Thanx

Thank you,

Moe-King of the Village Idiots.

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

 

[JohnDTampaBay]

LOL - There is a thin line between genius and insanity and every day I criss-cross that line many times.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)