How do I add one column and another???

[KOVMoe]

With out giving away too many trade secrets... Here is my code.

SELECT S.SHR_NA_TE,
COUNT(DISTINCT(A.PKG_TCK_NR))AS PKG,
SUM(CASE WHEN(DATEPART(HH,(DATEADD(HH,CAST(A.UTC_TCF_MT_QY AS REAL),A.PKG_DEL_TM)))<=08)AND(A.PKG_DEL_DT BETWEEN @dtPuDate1 AND @dtPuDate2)THEN 1 ELSE 0 END)AS PU08
FROM TSPHDTL_DETAILA A
LEFT OUTER JOIN dbo.TSPHSHP_SHIPPER S ON A.SHR_ID_INF_TE = S.SHR_AC_NR
WHERE A.PKG_DEL_DT BETWEEN @dtPuDate1 AND @dtPuDate2
AND S.SHR_NA_TE = 'HAL'
GROUP BY S.SHR_NA_TE

PKG COMES BACK AS CORRECT, 750... So between the 8 different pick up times, i total 5000 deliveries. That is way off. How do I get distinct deliveries? The real question is can i add the distinct to the sum, or a subquery?


Thank you,

Moe-King of the Village Idiots.

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

 

[rac2]

Well I dont know

but I wonder what is the point of GROUP BY S.SHR_NA_TE when that is a constant, S.SHR_NA_TE = 'HAL' ?

and I wonder what is the point of JOINing SPHSHP_SHIPPER ? It looks like you merely wish to count things in TSPHDTL_DETAILA.

So why not

SELECT 
       COUNT(DISTINCT(A.PKG_TCK_NR))AS PKG, 
       SUM(
           CASE  
             WHEN ( 
                   DATEPART(HH,
                       DATEADD(HH, 
                           CAST(UTC_TCF_MT_QY AS INT),
                            PKG_DEL_TM
                       )
                   ) <= 8
                  )
                  AND
                  (PKG_DEL_DT BETWEEN @dtPuDate1 
                   AND @dtPuDate2) 
                THEN 1 
             ELSE 0 
           END
       ) AS PU08

FROM TSPHDTL_DETAILA 

WHERE PKG_DEL_DT BETWEEN @dtPuDate1 AND @dtPuDate2 
  AND SHR_NA_TE = 'HAL'