The Timesheet < SPBCodes

The Timesheet

This is a live, editable timesheet as demonstrated in the video. User input into the timesheet will be reset every night. Bear in mind someone may be fiddling with this at the same time as you.

Please be gentle with the demo. It only has basic client-side validation and, especially given that others may be using at the same time, it is likely that overlaps and clashes will occur. The data is reset at midnight (UK local time) every day.

April 2025
DateDetailWorkedExpectedClaimedDifference
Balance to Carry Forward

The SQL

This is the SQL I came up with. The original query was at least double the length of the one shown below as since I wrote the original I have learned more advanced features of SQL (see below).. Although the SQL is an improvement on the original, I am sure it can be improved upon further. I did ask ChatGPT to optimise the query. The AI offered some suggestions but it failed to produce a revised/improved query that worked even after I provided further information (e.g. initially I didn't tell it which database server I was using).

This query utilises the following, more advanced (new-ish to me), features of SQL:

  • Use of variables and parameters (e.g. to provide some initial filtering parameters and to calculate the cumulative TOIL - Time off In Lieu - balance). Note that the calculated balances are not cast as TIME fields owing to the capacity limitation of the field type (i.e. '-838:59:59' to '838:59:59'). Instead the value is calculated from the number of seconds accumulated and then appropriately formatted.
  • JSON aggregation and extracting values from JSON objects on the fly (e.g. to obtain the expected hours for a particular day from the work pattern that is in effect on that day).
  • Use of CTE (Common Table Expression) to generate the list of dates in the year without the dates having to be present in any of the database tables.
/* Set starting parameters */
SET
	@CurrentYearStart = "2024-04-01";
SET
	@CurrentYearEnd = "2025-03-31";
SET
	@ExtractStart = "2024-12-01";
SET
	@ExtractEnd = "2024-12-31";
SET
	@JobID = 352;
SELECT
	TIME_TO_SEC(TOILBalance) INTO @Balance
FROM
	jobs
WHERE
	ID = @JobID;
	/* Retrieve just the extract range */
SELECT
	DAYNAME(DATE) AS DAY,
	DATE,
	StartingBalance,
	Worked,
	Shifts,
	`Leave`,
	LeaveNotes,
	Expected,
	Claimed,
	Difference,
	EndingBalance
FROM
	(

		/* Finalise some calculations, convert calculated decimal values to TIME values and give calculated columns meaningful names for the whole year  */
		SELECT
			`Date`,
			CONCAT(
				FLOOR(@Balance / 3600),
				":",
				LPAD(
					FLOOR(@Balance % 3600 / 60),
					2,
					"0"
				),
				":00"
			) AS StartingBalance,
			CAST(
				SEC_TO_TIME(
					IF(WorkedSeconds, WorkedSeconds, 0)
				) AS TIME
			) AS Worked,
			Shifts,
			CAST(
				SEC_TO_TIME(ExpectedSeconds * LeaveAllowance) AS TIME
			) AS `Leave`,
			LeaveNotes,
			CAST(
				SEC_TO_TIME(ExpectedSeconds) AS TIME
			) AS Expected,
			CAST(
				SEC_TO_TIME(
					(
						IF(
							ExpectedSeconds, ExpectedSeconds,
							0
						)* IF(
							LeaveAllowance, LeaveAllowance, 0
						)
					)+ IF(WorkedSeconds, WorkedSeconds, 0)
				) AS TIME
			) AS Claimed,
			CAST(
				SEC_TO_TIME(
					(
						ExpectedSeconds * IF(
							LeaveAllowance, LeaveAllowance, 0
						) + IF(WorkedSeconds, WorkedSeconds, 0)
					) - ExpectedSeconds
				) AS TIME
			) AS Difference,
			@Balance := @Balance +(
				(
					ExpectedSeconds * IF(
						LeaveAllowance, LeaveAllowance, 0
					) + IF(WorkedSeconds, WorkedSeconds, 0)
				) - ExpectedSeconds
			),
			CONCAT(
				FLOOR(@Balance / 3600),
				":",
				LPAD(
					FLOOR(@Balance % 3600 / 60),
					2,
					"0"
				),
				":00"
			) AS EndingBalance
		FROM
			(

				/* Retrieve the raw data for the entire year  */
				SELECT
					Dates.Date,
					SUM(
						IF(
							`leave`.ID,
							IF(
								Dates.Date = `From`,
								`First`,
								IF(Dates.Date = `To`, `Last`, 1)
							),
							NULL
						)
					) AS LeaveAllowance,
					WorkedSeconds,
					Shifts,
					GROUP_CONCAT(`Type` SEPARATOR " and ") AS LeaveNotes,
					TIME_TO_SEC(
						JSON_UNQUOTE(
							JSON_EXTRACT(
								WorkingPattern,
								CONCAT(
									"$.",
									DAYNAME(Dates.Date)
								)
							)
						)
					) AS ExpectedSeconds
				FROM
					(

						/* CTE to generate a list of dates for whole year */
						WITH
							RECURSIVE Date_Ranges AS (
								SELECT
									@CurrentYearStart AS DATE
								UNION ALL
								SELECT
									DATE + INTERVAL 1 DAY
								FROM
									Date_Ranges
								WHERE
									DATE & lt; @CurrentYearEnd
							)
						SELECT
							*
						FROM
							Date_Ranges
					) AS Dates
					LEFT JOIN
					/* Foreach day retrieve whether there is any leave recorded  */
					`leave` ON Dates.Date BETWEEN `From`
					AND `To`
					AND `leave`.JobID = @JobID
					LEFT JOIN
					/* For each day, calculate the total worked hours in seconds. Also, store the shift data in a JSON object  */
					(
						SELECT
							`checkins`.Date,
							SUM(
								TIME_TO_SEC(`End`)- TIME_TO_SEC(`Start`)
							) AS WorkedSeconds,
							JSON_ARRAYAGG(
								JSON_OBJECT(
									"ID", checkins.ID, "Start", checkins.`Start`,
									"End", checkins.`End`
								)
							) AS Shifts
						FROM
							`checkins`
						GROUP BY
							`checkins`.Date
					) AS `checkins` ON Dates.Date = `checkins`.Date
					INNER JOIN
					/* for each day, retrieve the applicable working pattern into a JSON object. as an inner join is used only dates that have a pattern defined will be retrieved */
					(
						SELECT
							`StartDate`,
							`EndDate`,
							JSON_OBJECT(
								"Monday", Monday, "Tuesday", Tuesday,
								"Wednesday", Wednesday, "Thursday",
								Thursday, "Friday", Friday, "Saturday",
								Saturday, "Sunday", Sunday
							) AS WorkingPattern
						FROM
							`pattern`
						WHERE
							JobID = @JobID
					) AS `pattern` ON Dates.Date BETWEEN `pattern`.StartDate
					AND `pattern`.EndDate
				GROUP BY
					Dates.Date
			) AS CurrentYearCalculated
	) AS EXTRACT
HAVING
	Extract.Date BETWEEN @ExtractStart
	AND @ExtractEnd