Prices – From the certain period to certain month

Question

User923858955 posted

Hi All,
First off all I am new to the forum and I am new to SSRS and I need some guidance. I have a table with prices for certain periods:

ItemID	Price	FromDate	ToDate
1	£20	01/07/2018	30/09/2018
1	£22	01/10/2018	31/12/2018
2	£10	01/07/2018	31/12/2018
3	£31	01/07/2018	31/07/2018
3	£35	01/08/2018	31/10/2018
3	£37	01/11/2018	31/12/2018

This is just an example, I have over 200 records in my table. I would like to convert the above table to;

ItemID	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
1	£20	£20	£20	£22	£22	£22
2	£10	£10	£10	£10	£10	£10
3	£31	£35	£35	£35	£37	£37

Is it possible to that with Matrix? Any help would be appreciated.

Piotr

Answer 1

User347430248 posted

Hi Pete,

I suggest you to develop a query that can produce the desired result.

Then you can directly use this query in SSRS to generate the result.

Below is one example to get an idea.

I use query below as a dataset in SSRS.

SELECT *
FROM (
    SELECT  
          t.Code
        , [Range] = 
            CASE 
                WHEN DaysSinceStart BETWEEN 0 AND 9   THEN '0-9'
                WHEN DaysSinceStart BETWEEN 10 AND 19 THEN '10-19'
                WHEN DaysSinceStart BETWEEN 20 AND 29 THEN '20-29'
                WHEN DaysSinceStart BETWEEN 30 AND 39 THEN '30-39'
                WHEN DaysSinceStart BETWEEN 40 AND 49 THEN '40-49'
                WHEN DaysSinceStart BETWEEN 50 AND 59 THEN '50-59'
                WHEN DaysSinceStart BETWEEN 60 AND 69 THEN '60-69'
                WHEN DaysSinceStart BETWEEN 70 AND 79 THEN '70-79'
                WHEN DaysSinceStart BETWEEN 80 AND 89 THEN '80-89'
                WHEN DaysSinceStart BETWEEN 90 AND 99 THEN '90-99' 
                ELSE 'Over 100' 
            END
    FROM tblJobs t
) o
PIVOT
(
    COUNT(o.[Range])
    FOR [Range] IN (
        [0-9], [10-19], [20-29],
        [30-39], [40-49], [50-59],
        [60-69], [70-79], [80-89], 
        [90-99], [Over 100]
    )
) pt

In SSRS:

Output:

So you can try to create a query as per your requirement and you can also try to use PIVOT in your T-SQL Query to get result like Metrix.

FROM - Using PIVOT and UNPIVOT

Regards

Deepak