I am probably going about this the wrong way, but my current solution is by using dynamic SQL and a loop.
Table creation:
DECLARE @dataTable TABLE (Item VARCHAR(255), Quantity INT, WeekStart DATE)
DECLARE @headerTable TABLE (Item CHAR(4), Date1 VARCHAR(25), Date2 VARCHAR(25), Date3 VARCHAR(25))
HeaderTable actually has columns Date1 through Date52. I excluded those for sake of simplicity.
These tables are used in an SSRS report. The header table is used for the first row of data (ie the header). In the headerTable, Date1 is the most recent Monday. Date2 is the following Monday and so on. So to populate the header table with sample data, the following query would do it:
INSERT INTO @headerTable
VALUES ('Item', '18/01/2021', '25/01/2021', '01/02/2021')
The header table will only ever contain 1 row. This table is auto-populated by stored procedure that truncates the table and populates it on a weekly basis. The first column is static and will ALWAYS contain the text 'Item'. Only the date vales are changing. I am using static values above to make the question easier to reproduce.
The problem comes in that my FINAL table is going to be a UNION of the header table along with a calculated table that is essentially pivoting the data table based on the values in the header table. The data table does NOT have 52 rows per item. It can have anywhere between 1 and 52. For this example though, we have 3 acceptable values for the WeekStart column which are in the header table. The data table MAY contain values larger than the last date (date52 with actual data, date3 with the sample data), but will never contain dates that fall on a Tuesday through Sunday; only Monday. Dates greater than 52 need to be ignored and not included.
Sample data for the data table:
INSERT INTO @dataTable
SELECT 'WidgetA' AS Item,
10 AS Quantity,
CAST('18/01/2021' AS DATE) AS WeekStart
UNION
SELECT 'WidgetA' AS Item,
10 AS Quantity,
CAST('01/02/2021' AS DATE) AS WeekStart
UNION
SELECT 'WidgetB' AS Item,
10 AS Quantity,
CAST('18/01/2021' AS DATE) AS WeekStart
The output I need is in this format:
SELECT Item, DAte1, Date2, Date3
FROM @headerTable
UNION
SELECT 'WidgetA' AS Item, CAST(10 AS VARCHAR(25)) AS Date1, CAST(0 AS VARCHAR(25)) AS Date2, CAST(10 AS VARCHAR(25)) AS Date3
UNION
SELECT 'WidgetB' AS Item, CAST(10 AS VARCHAR(25)) as Date1, CAST(0 AS VARCHAR(25)) AS Date2, CAST(0 AS VARCHAR(25)) AS Date3
We do need to cast the Date1 through Date3 to VARCHAR as the headers are in VARCHAR format. Without that, the UNION fails.
My current method is dynamic SQL to update DateX where X is a counter in a while loop by comparing WeekStart to DateX in the header table where X is that counter again.
I would prefer to do this without the loop, but I can't think of a good way to do so. The loop is efficient as there are under 2000 distinct Items at the moment and there should never, and I don't expect that getting much bigger if at all. I just think there should be an easier way to do this without looping.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 22, 2021 at 3:47 pm
This was removed by the editor as SPAM
January 22, 2021 at 3:48 pm
This was removed by the editor as SPAM
January 22, 2021 at 5:58 pm
Yeah, this can be definitely done in a standard SELECT.
Do you want to show only the weeks that had a match or do you want to show all 52 weeks, with, say, NULL for any Item that didn't have data for that week?
Showing a variable number of weeks is trickier, since you'd have to determine matched dates across all items in order to to the final output.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 22, 2021 at 6:07 pm
The HEADER table will always have a value for each week There is never a case where the header table will have a NULL for a week.
In the data table, there MAY not be a match for that week (see the sample data). If there is no match, then a 0 MUST be in place of the NULL.
The final table (using my method with a loop and dynamic SQL) gives me exactly 1610 rows in the final table. With the loop logic is it pretty easy to do, but is not how SQL likes to work and makes it more difficult to tune the execution plan view has 52 entries for populating that final data table. First being an insert the rest being updates.
The loop + dynamic SQL works, I just don't like that method. I hate using loops in SQL and hate using dynamic SQL if there is a better method. But in this case, I just couldn't think of anything. Especially since on Monday, that header table is going to change Date1 to Date2, Date2 to Date3 and Date3 to DateAdd(day,7,Date3) (not exactly how the logic works behind that stored procedure, but that is the general effect, using the sample data that I did above. Real data would do all the way up to 52 and 52 would get the DATEADD on it).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Maybe something like this?:
SELECT Item, Date1, Date2, Date3
FROM @headerTable
UNION ALL
SELECT
Item,
CAST(MAX(CASE WHEN week# = 1 THEN Quantity END) AS varchar(10)),
CAST(MAX(CASE WHEN week# = 2 THEN Quantity END) AS varchar(10)),
CAST(MAX(CASE WHEN week# = 3 THEN Quantity END) AS varchar(10))
FROM (
SELECT
dt.Item, SUM(dt.Quantity) AS Quantity, dt.WeekStart,
DATEDIFF(DAY, MAX(ht.Date1), dt.WeekStart) / 7 + 1 AS week#
FROM @dataTable dt
CROSS APPLY (
SELECT TOP (1) ht.Date1, ht.Date3 /*Date52*/ AS Date3 /*Date52*/
FROM @headerTable ht
) AS ht
WHERE dt.WeekStart >= ht.Date1 AND
dt.WeekStart <= ht.Date3 /*Date52*/
GROUP BY
dt.Item, dt.WeekStart
) AS dt
GROUP BY Item
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 25, 2021 at 2:09 pm
yep! That looks like it should work and looks more efficient than my loop approach! And easier to tune as it isn't 52 loops!
That one looks much nicer than my approach. Thanks Scott!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 25, 2021 at 7:38 pm
You're welcome! I'm glad it helped. Interesting q to play with.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 29, 2021 at 12:51 pm
A general solution with PIVOT.
USE master;
GO
DROP DATABASE IF EXISTS TestDB;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE dbo.Datatable
(
ItemVARCHAR(255),
QuantityINT,
WeekStartDATE
);
GO
INSERT INTO dbo.Datatable VALUES
('WidgetX', 10, '2019-1-18'),-- before
('WidgetA', 10, '2021-1-25'),
('WidgetA', 11, '2021-2-1'),
('WidgetB', 12, '2021-1-25'),
('WidgetC', 22, '2021-2-1'),
('WidgetC', 23, '2021-12-6'),
('WidgetY', 10, '2022-2-18');-- after
GO
-- Many data
INSERT INTO dbo.Datatable
SELECTItem,
Quantity,
DATEADD(WEEK, 1, WeekStart)
FROM dbo.Datatable
GO 10
INSERT INTO dbo.Datatable
SELECTCONCAT(Item, RIGHT(Item, 1)),
Quantity,
DATEADD(WEEK, 1, WeekStart)
FROM dbo.Datatable
GO 5
SELECT *
FROM dbo.Datatable
ORDER BY Item, WeekStart;
GO
SET DATEFIRST 1;-- monday
DECLARE @DateDATE= SYSDATETIME();
DECLARE @SqlNVARCHAR(MAX);
WITH AllWeeks (WeekID, Monday)
AS
(
SELECT1 AS WeekID,
DATEADD(DAY, - DATEPART (WEEKDAY, @Date) + 1, @Date) AS Monday
UNION ALL
SELECTWeekID + 1 AS WeekID,
DATEADD(DAY, 7, Monday) AS Monday
FROM AllWeeks
WHERE WeekID < 52
),
Projektion
AS
(
SELECT STRING_AGG (CONCAT('[', Monday, ']'), ', ') WITHIN GROUP (ORDER BY WeekID) AS Columns
FROM AllWeeks
),
RowConstructor
AS
(
SELECT STRING_AGG (CONCAT('(''', Monday, ''')'), ', ') WITHIN GROUP (ORDER BY WeekID) AS Columns
FROM AllWeeks
)
SELECT @Sql = CONCAT('SELECT Item, ',
(SELECT Columns FROM Projektion),
' FROM(SELECT Item, Quantity, WeekStart FROM dbo.Datatable INNER JOIN
(VALUES', (SELECT Columns FROM RowConstructor),
') AS W (Monday) ON Datatable.WeekStart = W.Monday ) AS Data',
' PIVOT (SUM(Quantity) FOR WeekStart IN (',
(SELECT Columns FROM Projektion),
')) AS pvt;');
-- PRINT @Sql;
EXEC sp_executesql @Sql;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply