Post reply

Using DATEADD to determine Del Dates

  • 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

  • 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:

  • 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

  • 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:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply