Using DATEADD to determine Del Dates

• tt-615680

  SSCertifiable

  Points: 7835

  More actions

  February 10, 2011 at 9:44 am

  #245098

  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

• vishal#SQL

  Mr or Mrs. 500

  Points: 560

  More actions

  February 10, 2011 at 9:56 am

  #1285310

  Hi,

  Do you want the count of deliveries where deliverydate within one week, two weeks and three weeks?

  :w00t:

• tt-615680

  SSCertifiable

  Points: 7835

  More actions

  February 10, 2011 at 10:01 am

  #1285313

  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

• vishal#SQL

  Mr or Mrs. 500

  Points: 560

  More actions

  February 10, 2011 at 10:44 am

  #1285342

  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.

  :w00t: