jeudi 18 décembre 2014

Merging 2 queries together


I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before:



tdate period staff tdate period seats
------------------------- ---------------------------
20141001 9 1 20141001 9 2
20141001 10 2 20141001 10 4
20141001 11 7 20141001 11 2
20141001 12 20 20141001 14 16
20141001 13 22 20141002 9 3
20141001 14 15 20141002 10 7
20141002 8 1 20141002 11 9
20141002 9 4 20141002 12 20
20141002 10 9
20141002 11 12
20141002 12 18
20141002 13 14


With this:



DECLARE @max_hr INT =(SELECT Max(CAST(LEFT(StartingTime, 2) AS INT))
FROM view_TimeRecords_COMBINED);

WITH cte
AS (SELECT Min(CAST(LEFT(EndingTime, 2) AS INT)) [period]
FROM view_TimeRecords_COMBINED a
UNION ALL
SELECT [period] + 1
FROM cte
WHERE [period] < @max_hr)

SELECT convert(char(8), StartingDate, 112) AS tDate, [period], Count(t.TimeKeeperStaffCode) AS Staff
FROM cte a
JOIN view_TimeRecords_COMBINED t
ON a.[period] BETWEEN CAST(LEFT(StartingTime, 2) AS INT) AND CAST(LEFT(EndingTime, 2) AS INT)
WHERE convert(char(8), StartingDate, 112) BETWEEN 20141002 AND 20141002 AND TimeKeeperJobFunctionCode NOT IN ('offic', 'maint', 'mgr', 'laun')
GROUP BY [period], t.StartingDate


SELECT convert(char(8), POSChitDate, 112) AS tDate, LEFT(POSChitTime, 2) AS period, SUM(Covers) AS seats
FROM tblPOSChits s
WHERE convert(char(8), POSChitDate, 112) BETWEEN 20141002 AND 20141002
GROUP BY LEFT(POSChitTime, 2), POSChitDate


I need to get to this:



tdate period staff seats
----------------------------------
20141001 9 1 2
20141001 10 2 4
20141001 11 7 2
20141001 12 20 0
20141001 13 22 0
20141001 14 15 16
20141002 8 1 0
20141002 9 4 3
20141002 10 9 7
20141002 11 12 9
20141002 12 18 20
20141002 13 14 0


I eventually will also need to group by the day,week & month if you need to take that in consideration as well. It tracks the number of people working -vs- people served by hour. Using MSSQL 2014.





Aucun commentaire:

Enregistrer un commentaire