# 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

Tuesday, May 15, 2018 9:23 AM

### All replies

• 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

Wednesday, May 16, 2018 5:05 AM