none
SQL statement to get weekly data RRS feed

  • Question

  • I have a table named DailyTransactionCount with columns AppId, LogDate and DailyCount.

    I am trying to get total number of transactions per week,  for every Monday for each appId in a given date range. I needed in the below format that I can display the data in my web application with very minimum code.

    Any help would be appreciated. I needed to display total number of transactions per week for each application. Thanks.

    If take the date range 11/17/2010 to 12/29/2010.

     
    LogWeek AppId1 AppId2 AppId3 AppId4 AppId5 AppId6

    17-NOV-2010 20 29 12 35 72 34
    22-NOV-2010 40 54 12 32 32 34
    29-NOV-2010 20 24 32 30 22 32
    06-DEC-2010 20 24 12 32 32 34
    13-DEC-2010 10 24 12 32 92 30
    20-DEC-2010 20 34 12 42 32 34
    27-DEC-2010 20 34 12 42 32 34

     

    Tuesday, April 5, 2011 5:00 PM

Answers

  • Try a simple change:

    ;with cte as (select AppId, SUM(DailyCount) as Cnt,
    case when dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW) < @StartDate then @StartDate else
     dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW) end as [MondayDate]
    from @tbl
    
    where LogDate >= '11/17/2010' and LogDate < '12/29/2010'
    group by AppId, datediff(day,@FIRST_BOW,LogDate)), 
    cte2 as (select cnt, MondayDate, ROW_NUMBER() over (PARTITION by MondayDate order by AppID) as Row from cte)
    
    select * from cte2 PIVOT (sum(cnt) FOR Row in ([1],[2],[3],[4],[5])) pvt
    
    

     

    I used @StartDate instead of '11/17/2010' 


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Tuesday, April 5, 2011 7:34 PM
    Moderator

All replies

  • In your query you may want to use:

    Group by datepart(year, LogWeek), datepart(week, LogWeek)
    

    Re-reading your question, I think this is what you want:

    DECLARE @tbl TABLE (AppId INT, LogDate DATETIME, DailyCount INT)
    
    INSERT INTO @TBL
    SELECT 1,'11/17/2010',1 UNION ALL 
    SELECT 1,'11/17/2010',1 UNION ALL 
    SELECT 1,'11/18/2010',5 UNION ALL 
    SELECT 1,'11/29/2010',1 UNION ALL 
    SELECT 2,'11/17/2010',1 UNION ALL 
    SELECT 2,'11/29/2010',1
    declare @FIRST_BOW datetime
    -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
    
     -- Find first day on or after 1753/1/1 (-53690)
    	-- matching day of week of @WEEK_START_DAY
    	-- 1753/1/1 is earliest possible SQL Server date.
    	select @FIRST_BOW = convert(datetime,-53690+((2+5)%7))
    
    ;with cte as (select AppId, SUM(DailyCount) as Cnt, dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW) as [MondayDate]
    from @tbl 
    group by AppId, dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW)), 
    cte2 as (select cnt, MondayDate, ROW_NUMBER() over (PARTITION by MondayDate order by AppID) as Row from cte)
    
    select * from cte2 PIVOT (sum(cnt) FOR Row in ([1],[2],[3],[4],[5])) pvt

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Tuesday, April 5, 2011 5:03 PM
    Moderator
  • DECLARE @tbl TABLE (AppId INT, LogDate DATETIME, DailyCount INT)
    
    INSERT INTO @TBL
    SELECT 1,'11/17/2010',1 UNION ALL 
    SELECT 1,'11/17/2010',1 UNION ALL 
    SELECT 1,'11/29/2010',1 UNION ALL 
    SELECT 2,'11/17/2010',1 UNION ALL 
    SELECT 2,'11/29/2010',1 
    
    
    SELECT LogDate, [1] as AppID1, [2] AppID2 
    fROM @tbl 
    PIVOT (SUM(DailyCount) FOR AppID IN ([1],[2])) pvt
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, April 5, 2011 5:07 PM
  • Thanks a lot. This is exactly what I am looking for. One small thing, how do I get the first MondayDate the given from date? Here I am using date range 11/17/2010 to 12/29/2010. 11/17/2011 is wednesday. The below query gives the transaction count from 11/17/2010 to 11/21/2010 in first row with MondayDate 11/15/2010. I wanted the 11/17/2010 in the first row.  

    ;with cte as (select AppId, SUM(DailyCount) as Cnt, dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW) as [MondayDate]
    from @tbl

    where LogDate >= '11/17/2010' and LogDate < '12/29/2010'
    group by AppId, dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW)),
    cte2 as (select cnt, MondayDate, ROW_NUMBER() over (PARTITION by MondayDate order by AppID) as Row from cte)

    select * from cte2 PIVOT (sum(cnt) FOR Row in ([1],[2],[3],[4],[5])) pvt

     

     


    Tuesday, April 5, 2011 7:23 PM
  • Try a simple change:

    ;with cte as (select AppId, SUM(DailyCount) as Cnt,
    case when dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW) < @StartDate then @StartDate else
     dateadd(day,(datediff(day,@FIRST_BOW,LogDate)/7)*7,@FIRST_BOW) end as [MondayDate]
    from @tbl
    
    where LogDate >= '11/17/2010' and LogDate < '12/29/2010'
    group by AppId, datediff(day,@FIRST_BOW,LogDate)), 
    cte2 as (select cnt, MondayDate, ROW_NUMBER() over (PARTITION by MondayDate order by AppID) as Row from cte)
    
    select * from cte2 PIVOT (sum(cnt) FOR Row in ([1],[2],[3],[4],[5])) pvt
    
    

     

    I used @StartDate instead of '11/17/2010' 


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Tuesday, April 5, 2011 7:34 PM
    Moderator
  • Great. your answer really helped me a lot. Currently the web application reads the data (LogDate, AppId and Count ) from the database tables, then there is lot of coding involved in order to display the data in the format I mentioned above. I am looking for alternate way to display the data with less coding. I never used PIVOT table before. How is PIVOT table performance?

     

    Tuesday, April 5, 2011 8:01 PM
  • I think the code I used is as less code as possible. Alternative solutions involving CASE statements are a bit more complex and more typing. For one column pivoting PIVOT should be a good and performing solution. If you need to pivot on many columns, then you'd need to use CASE statement pivot instead.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 5, 2011 8:46 PM
    Moderator
  • I've posted a short blog based on this thread

    Get weekly transactions showing Monday's date


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, April 11, 2011 9:12 PM
    Moderator
  • I have a query I have inserted another entry with appID 3 I dont get the correct result

    LogDate           AppId  DailyCount

    2012-04-17  1        1
    2012-04-17  2 1
    2012-04-18  1 5
    2012-04-29    3 1
    2012-04-17 1 1
    2012-04-29 2 1
    2012-05-01 1 1
    2012-05-08 4 1

    on executing the above specified query I get the below result

    Monday Date                      1     2      3       4     5

    2012-04-16 00:00:00.000 7   1     NULL  NULL NULL 
    2012-04-23 00:00:00.000 1   1     NULL NULL NULL
    2012-04-30 00:00:00.000 1 NULL NULL NULL NULL
    2012-05-07 00:00:00.000 1 NULL NULL NULL NULL (display data under 1 not under 4 as it is under app id 4).

    Tuesday, May 8, 2012 9:00 AM