********How to get every week records?********

• ranis.bo

  Grasshopper

  Points: 13

  More actions

  March 6, 2008 at 10:12 am

  #93990

  Hi

  I have two tables REQUEST and TIMEDIMENSION

  TIMEDIMENSION table columns are YEAR|WEEK|THEDATE

  REQUEST table Columns are REQUEST_ID|START_DATE|STATUS|

  I need to get How many requests are opened in every week?

  In the total open requests

  How many are 30 days older?

  How many are 10 days older?

  How many are 1 day older?

  AND Status not in(closed)

  CAN ANYBODY PLEASE HELP ME????

  I am able to get the data every week. previous week data is changing if request is closed in that week,But If I run the same query in the next week, I would like to see the same result set in previous week even if request is closed,and open requests in the prior week.

  select

  count(request_id),

  REQUEST.REQUEST_ID,

  TIMEDIMENSION.YEAR,

  TIMEDIMENSION.WEEK,

  count(case when (getDate()-REQUEST.START_DATE)>30 then 1 end)30Days old,

  count(case when (getDate()-REQUEST.START_DATE) <= 10 then 1 end)1Day old ,

  count(case when ( (getDate()-REQUEST.START_DATE)>10 and (getDate()-REQUEST.START_DATE) <= 30 ) then 1 end)10Days old

  from

  REQUEST,

  TIMEDIMENSION

  where

  (REQUEST.START_DATE=TIMEDIMENSION.THEDATE )

  and REQUEST.STATUS NOT IN ('Closed')

  GROUP BY

  REQUEST.REQUEST_ID,

  TIMEDIMENSION.YEAR,

  TIMEDIMENSION.WEEK

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738713

  More actions

  March 6, 2008 at 10:27 am

  #786182

  This sounds like homework. Also, please don't use all caps.

  If you show some effort here, we can try to help.