Prices – From the certain period to certain month.
-
Hi All,
First off all I am new to the forum and I am new to TSQL and I need some guidance. I have a table with prices for certain periods:
And I would like to convert it to:
I have no idea how to do that. Any help would be appreciated.
Piotr F -
You can use PIVOT, but it will force you to either hard code it each run, or have to use dynamic SQL. If you take out the PIVOT and use a reporting tool such as SSRS or Crystal Reports, just the RawData CTE columns are needed in a Matrix within either of those reporting tools. Here's the PIVOT query:
CREATE TABLE #ItemPrices (
ItemID int NOT NULL,
Price money NOT NULL,
FromDate date NOT NULL,
ToDate date NOT NULL,
CONSTRAINT PK_TEMP_ItemPrices_ItemID_FromDate_ToDate PRIMARY KEY CLUSTERED
(
ItemID ASC,
FromDate ASC,
ToDate ASC
)
);
INSERT INTO #ItemPrices (ItemID, Price, FromDate, ToDate)
VALUES (1, 20., '20180601', '20180930'),
(1, 22., '20181001', '20181231'),
(2, 10., '20180601', '20181231'),
(3, 31., '20180601', '20180731'),
(3, 35., '20180801', '20181031'),
(3, 37., '20181101', '20181231');DECLARE @MAX_DATE AS date, @MIN_DATE AS date;
SELECT @MAX_DATE = DATEADD(day, 1, MAX(IP.ToDate)),
@MIN_DATE = MIN(IP.FromDate)
FROM #ItemPrices AS IP;WITH Numbers AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Months AS (SELECT TOP (DATEDIFF(month, @MIN_DATE, @MAX_DATE))
DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @MIN_DATE) AS TheDate
FROM Numbers AS N1
CROSS APPLY Numbers AS N2
),
RawData AS (SELECT M.TheDate, IP.ItemID, IP.Price
FROM Months AS M
LEFT OUTER JOIN #ItemPrices AS IP
ON M.TheDate BETWEEN IP.FromDate AND IP.ToDate
)
SELECT ItemID, [2018-06-01], [2018-07-01], [2018-08-01], [2018-09-01], [2018-10-01], [2018-11-01], [2018-12-01]
FROM RawData
PIVOT (MAX(Price) FOR TheDate IN ([2018-06-01], [2018-07-01], [2018-08-01], [2018-09-01], [2018-10-01], [2018-11-01], [2018-12-01])) AS PVT
ORDER BY ItemID;DROP TABLE #ItemPrices;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks a lot. I need something more dynamic, my table with prices has got over 2000 rows. I was going to use that statement in SSRS anyway. @ Steve (aka sgmunson) can you please explain to me how to do that in SSRS
just the RawData CTE columns are needed in a Matrix
?
- pete_80 - Monday, May 14, 2018 2:21 PM
You would lop off the comma just before the "RawData", and down through "RawData", then eliminate everything after the end of that "RawData" query, starting with the parenthesis that otherwise would have marked the end of the "RawData" CTE. That forms your query for your dataset. Then you add a Matrix to the report surface, and configure it using the column TheDate as the horizontal axis, and the ItemID column as the vertical, with Price being the "aggregate". Despite calling an aggregate, as long as your query never provides more than one row for a given date and ItemID, you will never actually be aggregating data - just pivoting it. Give it a try and post back with questions.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply