SQL Server: Returns a specific number of periods
We have the following SQL Server 2008 database data table that shows payroll data per employee for each pay period (weekly pay is 52 or 53 weeks per year, after UK tax year, so pay period is 1st week - April 6 and beyond).
I have limited the sample to one employee for a number of pay periods, the actual data goes back many years.
I need to produce totals for each employee for the last 12 weeks of payment since the request was made.
+------------+------------+---------+-------+
| EMPLOYEEID | PAYELEMENT | AMOUNT | HOURS |
+------------+------------+---------+-------+
| 160062 | 1.0 Basic | 2724.64 | 468 |
+------------+------------+---------+-------+
However, I have some issues returning 12 separate periods due to the following ...
- Period 1 has two entries
- On holidays, no period is recorded, for example, for week 30, 38 and 39 are missing. In this case, it must return up to 12 recorded periods.
- Transition to the end of the year from period 52 to 1
I tried using the last 12 records for each employee, but item 1 above only caused 11 periods.
I've also tried using the date difference on paydate, but point 2 above results in no periods.
Do I need to add an index per employee to display 12 separate periods?
+------------+------------+------+--------+--------+-------+------+------------------+
| EMPLOYEEID | PAYELEMENT | YEAR | PERIOD | AMOUNT | HOURS | RATE | PAYDATE |
+------------+------------+------+--------+--------+-------+------+------------------+
| 160062 | 1.0 Basic | 2017 | 29 | 311.22 | 39 | 7.98 | 20/10/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 31 | 311.22 | 39 | 7.98 | 03/11/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 32 | 311.22 | 39 | 7.98 | 10/11/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 33 | 311.22 | 39 | 7.98 | 17/11/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 34 | 311.22 | 39 | 7.98 | 24/11/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 35 | 311.22 | 39 | 7.98 | 01/12/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 36 | 183.54 | 23 | 7.98 | 08/12/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 37 | 311.22 | 39 | 7.98 | 15/12/2016 00:00 |
| 160062 | 1.0 Basic | 2017 | 40 | 311.22 | 39 | 7.98 | 05/01/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 41 | 311.22 | 39 | 7.98 | 12/01/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 42 | 311.22 | 39 | 7.98 | 19/01/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 43 | 311.22 | 39 | 7.98 | 26/01/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 44 | 311.22 | 39 | 7.98 | 02/02/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 45 | 311.22 | 39 | 7.98 | 09/02/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 46 | 311.22 | 39 | 7.98 | 16/02/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 47 | 311.22 | 39 | 7.98 | 23/02/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 48 | 127.68 | 16 | 7.98 | 02/03/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 49 | 311.22 | 39 | 7.98 | 09/03/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 50 | 247.38 | 31 | 7.98 | 16/03/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 51 | 311.22 | 39 | 7.98 | 23/03/2017 00:00 |
| 160062 | 1.0 Basic | 2017 | 52 | 311.22 | 39 | 7.98 | 30/03/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 1 | 247.38 | 31 | 7.98 | 06/04/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 1 | 0 | 0 | 7.75 | 06/04/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 2 | 311.22 | 39 | 7.98 | 13/04/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 3 | 255.36 | 32 | 7.98 | 20/04/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 4 | 247.38 | 31 | 7.98 | 27/04/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 5 | 311.22 | 39 | 7.98 | 04/05/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 6 | 127.68 | 16 | 7.98 | 11/05/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 7 | 247.38 | 31 | 7.98 | 18/05/2017 00:00 |
| 160062 | 1.0 Basic | 2018 | 8 | 277.31 | 34.75 | 7.98 | 25/05/2017 00:00 |
+------------+------------+------+--------+--------+-------+------+------------------+
source to share
You must try:
;WITH Top12Periods AS (
SELECT TOP 12 [YEAR], [PERIOD]
FROM @employeeTable
GROUP BY [YEAR], [PERIOD]
ORDER BY [YEAR] DESC, [PERIOD] DESC
)
SELECT [EMPLOYEEID], [PAYELEMENT], SUM([AMOUNT]) AS TOTAL_AMOUNT, SUM([HOURS]) AS TOTAL_HOURS, AVG([RATE]) AS AVERAGE_RATE, MIN ([PAYDATE]) [MIN_PAYDATE]
FROM @employeeTable et
JOIN Top12Periods p ON et.[YEAR] = p.[YEAR] AND et.[PERIOD] = p.[PERIOD]
GROUP BY [EMPLOYEEID], [PAYELEMENT]
source to share
Here's a different approach:
SELECT
EmployeeID,
PayElement,
sum(Amount) as Amount,
sum(Hours) as Hours
FROM
(
SELECT *
,dense_rank() OVER (PARTITION BY EmployeeId ORDER BY PayDate DESC) as rank
FROM
Pay
) ranked
WHERE
rank <= 12
GROUP BY
EmployeeID,
PayElement
If your null values are causing problems here, just add WHERE Amount > 0
(or maybe <> 0
) to the inner query. Depending on your details, you might also need to add PayElement
to the sentence Partition By
(or internal sentence WHERE
).
You can check it out at rextester.com/YDBL47689
source to share