I have written this but not sure what is the best way.
So basically, the end results that I am trying to achieve is for record 6, Regular_Hours is 2 and record 7, Regular_Hours is 0.
Eight hours get deducted from 40 hours work week for each record until the 40 hours reaches 0.
I am going to continue to make attempts by myself but if anyone see a better way of writing this, please share. thanks
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
BEGIN
DROP TABLE #TEMP
END
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS My_RowNumber
, Total_Hours
, CASE
WHEN Total_Hours > 8 THEN 8
ELSE Total_Hours
END AS Regular_Hours
INTO #TEMP
FROM
(
SELECT 10 AS Total_Hours
UNION ALL
SELECT 10
UNION ALL
SELECT 6
UNION ALL
SELECT 10
UNION ALL
SELECT 14
UNION ALL
SELECT 14
UNION ALL
SELECT 10
) aT1
SELECT
My_RowNumber
, Total_Hours
, Regular_Hours
--, ( SELECT 40-SUM(Regular_Hours)
-- FROM #TEMP
-- WHERE My_RowNumber <= TT1.My_RowNumber
-- ) AS Regular_Hours_Total
FROM #TEMP TT1
Since the row order is random, there's no point in putting it out row-by-row. Just show the total result:
SELECT Total_Hours, CASE WHEN Reqular_Hours_Total >= 40 THEN 40 ELSE Reqular_Hours_Total END AS Reqular_Hours_Total
FROM (
SELECT
SUM(Total_Hours) AS Total_Hours
, SUM(CASE WHEN Total_Hours >= 8 THEN 8 ELSE Total_Hours END) AS Reqular_Hours_Total
FROM #TEMP TT1
) AS q1
Here is my code. Feel free to apply best practice to it.
IF OBJECT_ID('tempdb..#TEMPTable_Total_Hours') IS NOT NULL
BEGIN
DROP TABLE #TEMPTable_Total_Hours
END
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS My_RowNumber
, Total_Hours
, CASE
WHEN Total_Hours > 8 THEN 8 --Regular Time is default to 8 hours for Duration that is over 8 hours
Else Total_Hours --Less than 8 hours
END AS Daily_Regular_Time
INTO #TEMPTable_Total_Hours
FROM
(
SELECT 10 AS Total_Hours
UNION ALL
SELECT 10
UNION ALL
SELECT 6
UNION ALL
SELECT 10
UNION ALL
SELECT 14
UNION ALL
SELECT 14
UNION ALL
SELECT 10
) aT1
--SELECT * FROM #TEMPTable_Total_Hours aTTTH1
--DECLARE @Full_Work_Week TINYINT = 40
IF OBJECT_ID('tempdb..#TEMPTable_SUM_Hours') IS NOT NULL
BEGIN
DROP TABLE #TEMPTable_SUM_Hours
END
SELECT
aTTTH1.*
, ( SELECT SUM(Daily_Regular_Time)
FROM #TEMPTable_Total_Hours
WHERE My_RowNumber <= aTTTH1.My_RowNumber
) AS Regular_Hours_Total
INTO #TEMPTable_SUM_Hours
FROM #TEMPTable_Total_Hours aTTTH1
--SELECT * FROM #TEMPTable_SUM_Hours
SELECT
aTTTH1.Total_Hours
, IIF(Regular_Hours_Total<40, Daily_Regular_Time, IIF(40 - (SELECT Regular_Hours_Total FROM #TEMPTable_SUM_Hours WHERE My_RowNumber = (aTTTH1.My_RowNumber-1)) >= 0,
40 - (SELECT Regular_Hours_Total FROM #TEMPTable_SUM_Hours WHERE My_RowNumber = (aTTTH1.My_RowNumber-1)), 0))
FROM #TEMPTable_SUM_Hours aTTTH1
The final query is the one. The CROSS APPLY and clus index are just to help it run /much faster overall/.
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
BEGIN
DROP TABLE #TEMP
END
SELECT TOP (0)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS My_RowNumber
, Total_Hours
, Regular_Hours = CASE WHEN Total_Hours > 8 THEN 8 ELSE Total_Hours END
INTO #TEMP
FROM
(
SELECT 0 AS Total_Hours
) aT1
CREATE CLUSTERED INDEX #TEMP__CL ON #TEMP ( My_RowNumber ) WITH ( FILLFACTOR = 100 );
INSERT INTO #TEMP
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS My_RowNumber
, Total_Hours
, Regular_Hours = CASE WHEN Total_Hours > 8 THEN 8 ELSE Total_Hours END
FROM
(
SELECT 10 AS Total_Hours
UNION ALL
SELECT 10
UNION ALL
SELECT 6
UNION ALL
SELECT 10
UNION ALL
SELECT 14
UNION ALL
SELECT 14
UNION ALL
SELECT 10
) aT1
SELECT
aTT1.Total_Hours
, CASE WHEN aTT1.Regular_Hours + total.Regular_Hours <= 40 THEN aTT1.Regular_Hours
WHEN total.Regular_Hours >= 40 THEN 0
ELSE 40 - total.Regular_Hours END AS Regular_Hours
FROM #TEMP aTT1
CROSS APPLY (
SELECT ISNULL(SUM(Regular_Hours), 0) AS Regular_Hours
FROM #TEMP aTT2
WHERE aTT2.My_RowNumber < aTT1.My_RowNumber
) AS total
WOW! Thank you so much Scott, especially for your time and applying the best practice. You also kept it in all CAPS. You shrunk my query down by using the Cross Apply.
I am going to spend time to disect and learn the best practice from this. Thank You!
First, I changed the SELECT INTO #Temp to a CREATE TABLE because I remember from the Weekend Range thread that you prefer to do it like this. However, I wasn't able to add the constraint in there so I left the CREATE CLUSTERED INDEX
CREATE TABLE #TEMP
(
My_RowNumber TINYINT NOT NULL
, Total_Hours TINYINT NOT NULL
, Regular_Hours TINYINT NOT NULL
--CONSTRAINT #TEMP__CL PRIMARY KEY CLUSTERED ( My_RowNumber ) WITH ( FILLFACTOR = 100 )
)
--SELECT TOP (0)
-- ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS My_RowNumber
-- , Total_Hours
-- , Regular_Hours = CASE WHEN Total_Hours > 8 THEN 8 ELSE Total_Hours END
--INTO #TEMP
--FROM
--(
--SELECT 0 AS Total_Hours
--) aT1
CREATE CLUSTERED INDEX #TEMP__CL ON #TEMP ( My_RowNumber ) WITH ( FILLFACTOR = 100 );
Second, I finally came to an understanding why CROSS APPLY was used instead of INNER JOIN. With an INNER JOIN, this could not be achieve: aTT2.My_RowNumber < aTT1.My_RowNumber unless I added aTT2.My_RowNumber into a GROUP field since SUM is aggregated. Thus, this would have changed the result of that subquery. With CROSS APPLY, you were able to passed in the RowNumber as if it is a function which I usually use CROSS APPLY with a table function, but haven't in this manner.
I have ran into situations like this before and as you have seen, I have been using temp tables instead. Now, I know how to use CROSS APPLY more effectively.
And your 40 hour rules are much cleaner then how I had it.
I have been thinking about this CROSS APPLY throughout the night. I understand the difference usage of it versus an INNER JOIN. However, isn't CROSS APPLY just another fancy name for CORRELATED SUBQUERY? A CORRELATED SUBQUERY works in the same manner where an outer value is passed into the CORRELATED SUBQUERY.
Hi Scott, would you know why this is happening? The only thing I added is a group to the CROSS APPLY. But after doing so, it is not returning the first record for each group.
CROSS APPLY --NOTICE: CROSS APPLY like a function
(
SELECT aTT2.Group_ID, ISNULL(SUM(aTT2.Daily_Regular_Time), 0) AS SUM_Regular_Hours
FROM #TEMPTable_Regular_Hours aTT2 --NOTICE: ISNULL is for the first record
WHERE aTT2.Group_ID = aTT1.Group_ID
AND aTT2.My_Row_Number < aTT1.My_Row_Number --NOTICE: Cannot do this with an INNER JOIN or a CROSS JOIN (Feeding an outer value into this)
GROUP BY aTT2.Group_ID
) AS aSUMReg
Finally got it. Interesting, if I removed the grouping, then it worked fine.
CROSS APPLY --NOTICE: CROSS APPLY like a function
(
SELECT ISNULL(SUM(aTT2.Daily_Regular_Time), 0) AS SUM_Regular_Hours
FROM #TEMPTable_Regular_Hours aTT2 --NOTICE: ISNULL is for the first record
WHERE aTT2.Group_ID = aTT1.Group_ID
AND aTT2.My_Row_Number < aTT1.My_Row_Number --NOTICE: Cannot do this with an INNER JOIN or a CROSS JOIN (Feeding an outer value into this)
--GROUP BY aTT2.Group_ID
) AS aSUMReg
