We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# Prices – From the certain period to certain month RSS

Last post May 16, 2018 05:05 AM by Deepak Panchal

• Pete12345

None

0 Points

1 Post

### Prices – From the certain period to certain month

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

• Deepak Panch...

Contributor

2990 Points

1210 Posts

### Re: Prices – From the certain period to certain month

May 16, 2018 05:05 AM|Deepak Panchal|LINK

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

MSDN Community Support