July 29, 2019 at 8:03 pm
I am wondering if there is a way to query a subquery and add extra rows so a running total will tie with a different column? For example, in the temp tables below...#mytable is the raw subquery data. I want to sum the Labor Hours row totals by employee/date and add an additional row to make sure the sum of Labor Hours ties to the Pay Hours per employee/date. The #mytable2 example shows an example of the output I am looking for and has an 'ADD' text for the added rows.
It would be nice to accomplish this in a query rather than a stored procedure if possible. Alternatively, is there a better way to accomplish this?
Thanks,
Ross
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
EmployeeNum VARCHAR(14),
LaborDate date,
PayHours DECIMAL(5,2),
LaborType VARCHAR(4),
LaborHours DECIMAL(5,2)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, EmployeeNum, LaborDate, PayHours, LaborType, LaborHours)
SELECT 1,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'P',CAST(3 AS DECIMAL(5,2)) UNION ALL
SELECT 2,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'S',CAST(2 AS DECIMAL(5,2)) UNION ALL
SELECT 3,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'I',CAST(2 AS DECIMAL(5,2)) UNION ALL
SELECT 4,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'P',CAST(4 AS DECIMAL(5,2)) UNION ALL
SELECT 5,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'I',CAST(3 AS DECIMAL(5,2)) UNION ALL
SELECT 6,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'P',CAST(1 AS DECIMAL(5,2)) UNION ALL
SELECT 7,'EMP3','7/15/2019',CAST(8.5 AS DECIMAL(5,2)),'P',CAST(4 AS DECIMAL(5,2)) UNION ALL
SELECT 8,'EMP3','7/15/2019',CAST(8.5 AS DECIMAL(5,2)),'P',CAST(4 AS DECIMAL(5,2))
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
SELECT * FROM #mytable
-------------------------------------------------------
--desired result after new query on #mytable1
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable2
--===== Create the test table with
CREATE TABLE #mytable2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
EmployeeNum VARCHAR(14),
LaborDate date,
PayHours DECIMAL(5,2),
LaborType VARCHAR(4),
LaborHours DECIMAL(5,2)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable2 ON
--===== Insert the test data into the test table
INSERT INTO #mytable2
(ID, EmployeeNum, LaborDate, PayHours, LaborType, LaborHours)
SELECT 1,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'P',CAST(3 AS DECIMAL(5,2)) UNION ALL
SELECT 2,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'S',CAST(2 AS DECIMAL(5,2)) UNION ALL
SELECT 3,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'I',CAST(2 AS DECIMAL(5,2)) UNION ALL
SELECT 4,'EMP1','7/15/2019',CAST(8 AS DECIMAL(5,2)),'ADD',CAST(1 AS DECIMAL(5,2)) UNION ALL
SELECT 5,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'P',CAST(4 AS DECIMAL(5,2)) UNION ALL
SELECT 6,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'I',CAST(3 AS DECIMAL(5,2)) UNION ALL
SELECT 7,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'P',CAST(1 AS DECIMAL(5,2)) UNION ALL
SELECT 8,'EMP2','7/15/2019',CAST(9 AS DECIMAL(5,2)),'ADD',CAST(1 AS DECIMAL(5,2)) UNION ALL
SELECT 9,'EMP3','7/15/2019',CAST(8.5 AS DECIMAL(5,2)),'P',CAST(4 AS DECIMAL(5,2)) UNION ALL
SELECT 10,'EMP3','7/15/2019',CAST(8.5 AS DECIMAL(5,2)),'P',CAST(4 AS DECIMAL(5,2)) UNION ALL
SELECT 11,'EMP3','7/15/2019',CAST(8.5 AS DECIMAL(5,2)),'ADD',CAST(.5 AS DECIMAL(5,2))
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable2 OFF
SELECT * FROM #mytable2
July 29, 2019 at 9:47 pm
This gives the expected results, but it may not handle unexpected data such as PayHours being inconsistent for a given employee/date.
SELECT ROW_NUMBER() OVER(ORDER BY m.EmployeeNum, m.LaborDate, GROUPING(m.ID), m.ID) AS ID
,m.EmployeeNum
,m.LaborDate
,m.PayHours
,CASE
WHEN GROUPING(m.ID) = 0 THEN m.LaborType
ELSE 'ADD'
END AS LaborType
,CASE
WHEN GROUPING(m.ID) = 0 THEN SUM(m.LaborHours)
ELSE m.PayHours - SUM(m.LaborHours)
END AS LaborHours
FROM #mytable AS m
GROUP BY GROUPING SETS((m.ID, m.EmployeeNum, m.LaborDate, m.PayHours, m.LaborType), (m.EmployeeNum, m.LaborDate, m.PayHours))
HAVING SUM(m.LaborHours) < m.PayHours;
I'm also not sure that you actually want include the ID as part of the output, but I have given a method that will do so.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 29, 2019 at 11:32 pm
That is so great, thank you! It works perfectly. I am going to study it to wrap my head around what is happening here.
I am assuming that I can change the #mytable piece to a (sub select) statement and all works the same?
Thank you,
Ross
July 30, 2019 at 4:20 pm
That is so great, thank you! It works perfectly. I am going to study it to wrap my head around what is happening here.
I am assuming that I can change the #mytable piece to a (sub select) statement and all works the same?
Thank you,
Ross
You can absolutely substitute a derived table (sub select) for #mytable, but I prefer using CTEs.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply