Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using DATEADD to determine Del Dates Expand / Collapse
Author
Message
Posted Thursday, February 10, 2011 9:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:20 AM
Points: 251, Visits: 927
Hi All,

I am trying to determine when deliveries have occurred but the results that I am getting seem to be all the same for each week. Here is part of my script.

COUNT(CASE WHEN DATEADD(WK,1,Delivery_date) = 1 THEN 1 ELSE 0 END) AS [Delwithin1weeks]
COUNT(CASE WHEN DATEADD(WK,2,Delivery_date) = 1 THEN 1 ELSE 0 END) AS [Delwithin2weeks]
COUNT(CASE WHEN DATEADD(WK,3,Delivery_date) = 1 THEN 1 ELSE 0 END) AS [Delwithin3weeks]

Would someone let me know how else I can write the script please.

Thank you
Post #1062105
Posted Thursday, February 10, 2011 9:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 2, 2012 10:49 AM
Points: 228, Visits: 197
Hi,
Do you want the count of deliveries where deliverydate within one week, two weeks and three weeks?


Post #1062119
Posted Thursday, February 10, 2011 10:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:20 AM
Points: 251, Visits: 927
vishal#SQL (2/10/2011)
Hi,
Do you want the count of deliveries where deliverydate within one week, two weeks and three weeks?


Thank you for the reply. Yes I do need the count of deliveries where deliverydate within one week, two weeks and three weeks.

Thank you
Post #1062122
Posted Thursday, February 10, 2011 10:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 2, 2012 10:49 AM
Points: 228, Visits: 197
Try This

SELECT SUM(CASE WHEN delivery_date BETWEEN DATEADD(WK,-1,getDAte()) AND getDAte() THEN 1 ELSE 0 END) AS [Delwithin1weeks]
, SUM(CASE WHEN delivery_date BETWEEN DATEADD(WK,-2,getDAte()) AND getDAte() THEN 1 ELSE 0 END) AS [Delwithin2weeks]
, SUM(CASE WHEN delivery_date BETWEEN DATEADD(WK,-3,getDAte()) AND getDAte() THEN 1 ELSE 0 END) AS [Delwithin2weeks]

You need to USE SUM function instead of COUNT.


Post #1062158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse