Calculate date ranges

Question

Hi,

I am trying to calculate the items sold 90 days prior to 6/1/2009 and 90 days after 6/1/2009 with the query below. it shows some error. Would someone kindly edcuate me please???

SELECT
location,                       
SUM((CASE WHEN t.order_date DATEADD (DAY, -90, '6/1/2009') THEN t.Item ELSE NULL END) as Prior_Items,
SUM(CASE WHEN t.order_date DATEADD (DAY, 89,  '6/1/2009') THEN t.Item ELSE NULL END) as Post_Items
ELSE NULL
END)
FROM mytable t
where date = '6/1/2009'
group by location

Answer 1

your WHEN condition is not properly formed.

CASE WHEN t.order_date DATEADD (DAY, -90, '6/1/2009') THEN t.Item ELSE NULL END
          ^^^^^^^^^^^^^^^^^^^^

most likely you want something like:

CASE WHEN t.order_date>=DATEADD (DAY, -90, '6/1/2009') THEN t.Item ELSE NULL END
                      ^^

Answer 2

I assume you want something like this.

DECLARE @d DATETIME

SET @d = '20090106'


SELECT
location,                       
SUM(CASE WHEN t.order_date < @d and  t.order_date > DATEADD (DAY, -90, @d) THEN t.Item END) as Prior_Items,
SUM(CASE WHEN t.order_date >=@d AND  t.order_date <  DATEADD (DAY, 89,   @d) THEN t.Item END) as Post_Items
FROM mytable t
where t.order_date BETWEEN  DATEADD (DAY, -90, @d) AND DATEADD (DAY, 89,   @d)
group by location