none
SSRS Data for Last 6 Weeks as separate columns RRS feed

  • Question

  • Hi all,

    I have the below query:

    SELECT distinct sending_organisation AS [Supplier], t1.date, COUNT(Status) AS [Transactions],  Month(t1.Date) SO_Month, Year(t1.date) SO_Year FROM TX   CROSS APPLY ( VALUES (CONCAT(left(DATENAME(mm, Date_Reported),3),' ',                             DATENAME(yyyy, Date_Reported)),                      DATEPART(mm, Date_Reported)                     )            ) t1 (Date, Morder) where  Status not in ('defect') and Date >= DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))GROUP BY t1.date, t1.Morder, sending_organisationORDER BY sending_organisation, date DESC;

    I created a dataset and then a report from this query. I created a matrix table, where supplier is row, date is the column (this is made up by month and year as per sql query). The above query also gives me the last 6 weeks of data as that is all i am interested in.

    Currently, the columns on the matrix table are presented as months, so it being last 6 weeks I will at most have only 2 months on my matrix. What I want is not the month, but columns as weeks (starting from Monday to Sunday) and hence I will have 6 columns on  my matrix- so basically like this if i looked ta last 6 weeks from today:

    4/06/18 | 11/06/18 | 18/06/18 | 25/06/18 | 02/07/18 | 09/07/18


    Friday, July 13, 2018 8:34 AM

Answers

  • Sounds like this is what you're after

    SELECT  sending_organisation AS [Supplier],
     t1.date, 
    COUNT(Status) AS [Transactions],  
    Month(t1.Date) SO_Month, 
    Year(t1.date) SO_Year 
    FROM TX   
    CROSS APPLY ( 
    VALUES (DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),                      
    FORMAT(DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),'dd/MM/yyyy')                   )
    ) t1 (Date, Morder) 
    where  Status not in ('defect') 
    and Date >= DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))    
    GROUP BY t1.date, t1.Morder, sending_organisation 
    ORDER BY sending_organisation, date DESC;
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by taz 91 Friday, July 13, 2018 1:28 PM
    Friday, July 13, 2018 9:29 AM

All replies

  • Sounds like this is what you're after

    SELECT  sending_organisation AS [Supplier],
     t1.date, 
    COUNT(Status) AS [Transactions],  
    Month(t1.Date) SO_Month, 
    Year(t1.date) SO_Year 
    FROM TX   
    CROSS APPLY ( 
    VALUES (DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),                      
    FORMAT(DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),'dd/MM/yyyy')                   )
    ) t1 (Date, Morder) 
    where  Status not in ('defect') 
    and Date >= DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))    
    GROUP BY t1.date, t1.Morder, sending_organisation 
    ORDER BY sending_organisation, date DESC;
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by taz 91 Friday, July 13, 2018 1:28 PM
    Friday, July 13, 2018 9:29 AM
  • That is perfect Visakh. Thank you :)
    • Edited by taz 91 Friday, July 13, 2018 1:28 PM
    Friday, July 13, 2018 1:27 PM
  • Visakh, based on your query above i have another question.

    The above gives me what i needed, but now i need to add another column which gives latest date reported for each supplier. So i am only interested in one date for each supplier but this is the latest date out of all dates for that supplier.

    My final table should look like this

    4/06/18 | 11/06/18 | 18/06/18 | 25/06/18 | 02/07/18 | 09/07/18 | LATEST DATE

    SUPPLIER A 2 | 3 | 5 | 7 | 4 | 2 | 13/07/18

    Wednesday, July 18, 2018 11:48 AM
  • Visakh, based on your query above i have another question.

    The above gives me what i needed, but now i need to add another column which gives latest date reported for each supplier. So i am only interested in one date for each supplier but this is the latest date out of all dates for that supplier.

    My final table should look like this

    4/06/18 | 11/06/18 | 18/06/18 | 25/06/18 | 02/07/18 | 09/07/18 | LATEST DATE

    SUPPLIER A 2 | 3 | 5 | 7 | 4 | 2 | 13/07/18

    you can take it like this

    SELECT  sending_organisation AS [Supplier],
    t1.date,
    t1Morder, 
    COUNT(Status) AS [Transactions],  
    Month(t1.Date) SO_Month, 
    Year(t1.date) SO_Year,
    0 AS Ord
    FROM TX   
    CROSS APPLY ( 
    VALUES (DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),                      
    FORMAT(DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),'dd/MM/yyyy')                   )
    ) t1 (Date, Morder) 
    where  Status not in ('defect') 
    and Date >= DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))    
    GROUP BY t1.date, t1.Morder, sending_organisation 
    
    UNION ALL
    
    SELECT  sending_organisation AS [Supplier],
    MAX(t1.date),
    'Latest Date', 
    MAX(t1.date) AS [Transactions],  
    Month(t1.Date) SO_Month, 
    Year(t1.date) SO_Year,
    1
    FROM TX   
    CROSS APPLY ( 
    VALUES (DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),                      
    FORMAT(DATEADD(dd,DATEDIFF(dd,0,Date_Reported)/7 * 7,0),'dd/MM/yyyy')                   )
    ) t1 (Date, Morder) 
    where  Status not in ('defect') 
    and Date >= DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))    
    GROUP BY t1.date, t1.Morder, sending_organisation 
    ORDER BY Supplier,Ord, date DESC;

    then do the pivot to get latest date as the last column


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 18, 2018 12:03 PM
  • hi visakh it doesn't quite get what i am looking for

    so for each supplier they have a date reported and from this you have helped me split it up into 6 weeks and a value for each week representing the no. of transactions. But now i want to add an extra column which gives me the lats date reported (from date reported) for all the 6 weeks. So obviously it will be in the latest week

    Wednesday, July 18, 2018 2:42 PM