none
Case of the Age

    Question

  • Hi Friends,

                     I want to find out the age of the case every week. For example: I have in my table like below

    Create table #Case
    (
    caseopened date,
    caseclosed date
    )
    insert into #Case
    values ('2012-01-04','2012-02-16') 
    
    SElect * from #case
    

    caseopened 2012-01-04  caseclosed 2012-02-16. So Now from datedimension or from calendar i can find that 2012-01-08
    2012-01-15,2012-01-22,2012-01-29,2012-02-05,2012-02-12,2012-02-19 are weeks between that above mentioned case. Now i want to find out the age of the case for every week. 

    like for 2012-01-08 age is 4 for 2012-01-15 age is 11, etc . Any help is highly appreciated. I wanted to present this in ssrs chart where x axis is weekendingsunday and y axis is age. 

    Thanks.


    Thursday, January 30, 2014 8:53 PM

Answers

  • Create table  #Week (WeekEnd date);
    declare @dt date = '20120101';
    
    while @dt < '20120301'
    begin
    insert #Week values (@dt);
    set @dt = dateadd (day, 7, @dt);
    end
    
    Create table #Case
    (
    caseopened date,
    caseclosed date
    )
    insert into #Case
    values ('2012-01-04','2012-02-16') 
    
    SElect 
    w.WeekEnd
    , DATEDIFF (day, c.caseopened, w.WeekEnd) as Age
     from #case c
    inner join #Week w
    on c.caseopened <= w.WeekEnd and c.caseclosed >= w.WeekEnd 
    
    drop table #Case
    drop table #Week


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Murali dhar Thursday, January 30, 2014 9:51 PM
    Thursday, January 30, 2014 9:32 PM

All replies

  • Create table  #Week (WeekEnd date);
    declare @dt date = '20120101';
    
    while @dt < '20120301'
    begin
    insert #Week values (@dt);
    set @dt = dateadd (day, 7, @dt);
    end
    
    Create table #Case
    (
    caseopened date,
    caseclosed date
    )
    insert into #Case
    values ('2012-01-04','2012-02-16') 
    
    SElect 
    w.WeekEnd
    , DATEDIFF (day, c.caseopened, w.WeekEnd) as Age
     from #case c
    inner join #Week w
    on c.caseopened <= w.WeekEnd and c.caseclosed >= w.WeekEnd 
    
    drop table #Case
    drop table #Week


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Murali dhar Thursday, January 30, 2014 9:51 PM
    Thursday, January 30, 2014 9:32 PM
  • Thanks a lot Sir. And we can also make this dynamic right? . Thanks again.

    Thursday, January 30, 2014 9:52 PM
  • What I would do is to get the min caseopened date and get the previous Sunday.  You can adjust the script on this page to get Sunday: http://blog.sqlauthority.com/2007/08/20/sql-server-find-monday-of-the-current-week/

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, January 30, 2014 10:18 PM