Solved

# How to make a running total in a SQL 2005 view>

Posted on 2014-07-21
182 Views
I have a view in SQL Server 2005 that calculates total employee hours for each day worked.  The query also outputs the work week.  I would like to take it a step further and have it split the hours into "regular" and "overtime" based on a 40 hour week and have separate fields for "Regular Hours" and "Overtime Hours".  This way, I can see which shift in the week put them overtime.  I need help with the logic that keeps a running total and breaks out the hours accordingly.

Here is my query, and some sample output:

``````SELECT     TOP (100) PERCENT p.EmployeeId AS PAD_EmployeeNumber, p.PAD_Date, CAST((CASE WHEN JobCode IN (901, 902, 903, 904) THEN Department ELSE JobCode END)
AS Int) AS PAD_DepartmentNumber, CAST(ISNULL(SUM(CASE WHEN JobCode IN (200, 400, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609)
THEN CONVERT(decimal(18, 2), DATEDIFF(MINUTE, TimeIn, TimeOut) / 60.00) END), 0) AS DECIMAL(10, 2)) AS PAD_WorkHours,
CAST(ISNULL(SUM(CASE WHEN JobCode IN (901) THEN CONVERT(decimal(18, 2), DATEDIFF(MINUTE, TimeIn, TimeOut) / 60.00) END), 0) AS DECIMAL(10, 2))
AS PAD_PTOHours, CAST(ISNULL(SUM(CASE WHEN JobCode IN (902, 903, 904) THEN CONVERT(decimal(18, 2), DATEDIFF(MINUTE, TimeIn, TimeOut) / 60.00) END), 0)
AS DECIMAL(10, 2)) AS PAD_HolidayHours, ISNULL(CAST(SUM(p.Tracked1) AS decimal(18, 2)), 0) AS PAD_OtherPay, MIN(p.OTHERDESCRIPTION)
FROM         (SELECT     dbo.EmployeeHours.EmployeeId, dbo.EmployeeHours.TimeOut, dbo.EmployeeHours.TimeIn, CAST(CASE WHEN JobCode NOT IN (603, 605)
THEN CONVERT(varchar(10), TimeOut, 101) WHEN (JobCode IN (603, 605) AND DATEDIFF(DAY, TimeIn, TimeOut) > 0) OR
(DATEPART(HOUR, TimeIn) < 12 AND DATEPART(HOUR, TimeOut) < 12) THEN CONVERT(varchar(10), TimeOut, 101) ELSE CONVERT(varchar(10),
dbo.EmployeeShiftNotes.ShiftNote AS OTHERDESCRIPTION, dbo.EmployeeList.Department, dbo.EmployeeHours.JobCode
FROM          dbo.EmployeeHours LEFT OUTER JOIN
dbo.EmployeeShiftNotes ON dbo.EmployeeHours.RecordId = dbo.EmployeeShiftNotes.RecordId INNER JOIN
dbo.EmployeeList ON dbo.EmployeeHours.EmployeeId = dbo.EmployeeList.EmployeeId) AS p INNER JOIN
WHERE     (0 = 0) AND (p.TimeOut IS NOT NULL)
GROUP BY p.EmployeeId, p.PAD_Date, CAST((CASE WHEN JobCode IN (901, 902, 903, 904) THEN Department ELSE JobCode END) AS Int), AP.ACP_Week
``````
``````PAD_EmployeeNmber	PAD_Date	PAD_DepartmentNumber	PAD_WorkHours	PAD_PTOHours	PAD_HolidayHours	PAD_OtherPay	PAD_OtherPayReason	ACP_Week
1004	7/15/2013 0:00	400	9.5	0	0	0	NULL	29
``````
0
Question by:paragonfoods
• 5
• 5
• 2
• +1

LVL 15

Expert Comment

Here is some formatting just to help any experts who jump in...
``````SELECT
TOP (100) PERCENT
CAST(
(CASE WHEN JobCode IN (901, 902, 903, 904)
THEN Department ELSE JobCode END)
AS Int

CAST(
ISNULL(SUM(CASE WHEN JobCode IN (200, 400, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609)
THEN CONVERT(decimal(18, 2), DATEDIFF(MINUTE, TimeIn, TimeOut) / 60.00) END), 0) AS DECIMAL(10, 2)

CAST(
ISNULL(SUM(CASE WHEN JobCode IN (901)
THEN CONVERT(decimal(18, 2), DATEDIFF(MINUTE, TimeIn, TimeOut) / 60.00) END), 0) AS DECIMAL(10, 2)

CAST(
ISNULL(SUM(CASE WHEN JobCode IN (902, 903, 904)
THEN CONVERT(decimal(18, 2), DATEDIFF(MINUTE, TimeIn, TimeOut) / 60.00) END), 0) AS DECIMAL(10, 2)

ISNULL(CAST(SUM(p.Tracked1) AS decimal(18, 2)), 0) AS PAD_OtherPay,

FROM
(
SELECT
dbo.EmployeeHours.EmployeeId,
dbo.EmployeeHours.TimeOut,
dbo.EmployeeHours.TimeIn,
CAST(
CASE WHEN JobCode NOT IN (603, 605)
THEN CONVERT(varchar(10), TimeOut, 101) WHEN (JobCode IN (603, 605) AND DATEDIFF(DAY, TimeIn, TimeOut) > 0) OR (DATEPART(HOUR, TimeIn) < 12 AND DATEPART(HOUR, TimeOut) < 12) THEN CONVERT(varchar(10), TimeOut, 101) ELSE CONVERT(varchar(10), DATEADD(DAY, 1, TimeIn), 101)
END AS SMALLDATETIME
dbo.EmployeeShiftNotes.ShiftNote AS OTHERDESCRIPTION,
dbo.EmployeeList.Department,
dbo.EmployeeHours.JobCode

FROM dbo.EmployeeHours
LEFT OUTER JOIN dbo.EmployeeShiftNotes ON dbo.EmployeeHours.RecordId = dbo.EmployeeShiftNotes.RecordId
INNER JOIN dbo.EmployeeList ON dbo.EmployeeHours.EmployeeId = dbo.EmployeeList.EmployeeId
) AS p
WHERE (0 = 0) AND (p.TimeOut IS NOT NULL)
GROUP BY p.EmployeeId, p.PAD_Date, CAST((CASE WHEN JobCode IN (901, 902, 903, 904) THEN Department ELSE JobCode END) AS Int), AP.ACP_Week
``````
0

Author Comment

Many thanks for the formatting!
0

LVL 30

Expert Comment

Why not simplify the query?
It will be helpful if you use few fields needed to recreate the issue. Avoid other calculated fields.

Once satisfied with a solution, you may expand and use other required fields.
0

LVL 48

Expert Comment

I think you need to provide a larger sample of the result plus the wanted changes (i.e. an "expected result") you might just do this in Excel and upload it.

Providing data, for each of the tables, to work with helps, Ideally as inserts.
0

Author Comment

Please see the attached Excel spreadsheet for a list of the query source fields and their associated data types.  I've also included some of the returned data, along with the desired result.  Thanks to everyone for your help with this!Datatypes-with-desired-result.xlsx
0

LVL 15

Expert Comment

We're getting a bit out of the realm of where databases shine.  Even though we can do what you are shooting for from within MS SQL, it isn't clean or easy, and does not lend itself well to changes down the road.  I think what you are after is a dashboard solution.  That being said, I think I'd recommend starting with a simple Excel sheet "dashboard" which pulls its data from your existing query and then keeps the running totals for you within the workbook.  Is that something you'd like to pursue?

BTW, more formal dashboard solutions are also an option (just google "dashboard manager employee").  Here's a site to wet your appetite (there are many more out there):
www.kaptasystems.com/
0

LVL 48

Expert Comment

Mmm, the problem here is that SQL 2005 and 2008 don't support "order by" in the following:

So it is much more difficult to produce the running total, and in my opinion the methods needed for producing the running total would not suit the existing view. (i.e. you would have to produce the running sum after the existing view was used.)

The division between regular and overtime is simply when the weekly running sum > 40 (correct?)
0

LVL 48

Expert Comment

``````| PAD_EMPLOYEENUMBER |   PAD_DATE | PAD_DEPARTMENTNUMBER | PAD_WORKHOURS | PAD_NORMALTIME | PAD_OVERTIME | PAD_PTOHOURS | PAD_HOLIDAYHOURS | PAD_OTHERPAY | PAD_OTHERPAYREASON | ACP_WEEK |
|--------------------|------------|----------------------|---------------|----------------|--------------|--------------|------------------|--------------|--------------------|----------|
|               1004 | 2013-07-15 |                  400 |           9.5 |            9.5 |            0 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-16 |                  400 |            10 |             10 |            0 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-17 |                  400 |         11.75 |          11.75 |            0 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-18 |                  400 |            10 |           1.25 |         8.75 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-19 |                  400 |         10.08 |              0 |        10.08 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-23 |                  400 |         10.08 |          10.08 |            0 |            0 |                0 |            0 |             (null) |       30 |
|               1004 | 2013-07-24 |                  400 |         11.68 |          11.68 |            0 |            0 |                0 |            0 |             (null) |       30 |
|               1004 | 2013-08-08 |                  400 |         11.63 |          11.63 |            0 |            0 |                0 |            0 |             (null) |       32 |
|               1004 | 2013-08-20 |                  400 |         10.18 |          10.18 |            0 |            0 |                0 |            0 |             (null) |       34 |
|               1004 | 2013-08-21 |                  400 |         12.12 |          12.12 |            0 |            0 |                0 |            0 |             (null) |       34 |
|               1004 | 2013-08-22 |                  400 |         10.08 |          10.08 |            0 |            0 |                0 |            0 |             (null) |       34 |
|               1004 | 2013-08-27 |                  400 |           9.6 |            9.6 |            0 |            0 |                0 |            0 |             (null) |       35 |
|               1004 | 2013-08-28 |                  400 |         12.21 |          12.21 |            0 |            0 |                0 |            0 |             (null) |       35 |
|               1004 | 2013-08-29 |                  400 |         10.23 |          10.23 |            0 |            0 |                0 |            0 |             (null) |       35 |
|               1004 | 2013-09-25 |                  400 |         11.86 |          11.86 |            0 |            0 |                0 |            0 |             (null) |       39 |
|               1004 | 2013-09-26 |                  400 |          9.77 |           9.77 |            0 |            0 |                0 |            0 |             (null) |       39 |
|               1004 | 2013-09-27 |                  400 |          9.62 |           9.62 |            0 |            0 |                0 |            0 |             (null) |       39 |
|               1004 | 2013-10-01 |                  400 |          9.75 |           9.75 |            0 |            0 |                0 |            0 |             (null) |       40 |
|               1004 | 2013-10-02 |                  400 |         11.91 |          11.91 |            0 |            0 |                0 |            0 |             (null) |       40 |
|               1004 | 2013-10-03 |                  400 |          9.62 |           9.62 |            0 |            0 |                0 |            0 |             (null) |       40 |

``````
produced by:
``````;WITH
CTE AS (

-- this would be some query using your existing view
-- we use a CTE because we need to join back to this result
select * from CurrentResults
)
SELECT
- CASE
, CASE
, CTE.ACP_Week
FROM CTE
LEFT JOIN CTE AS PREV
AND CTE.ACP_Week = PREV.ACP_Week
GROUP BY
, CTE.ACP_Week
ORDER BY
CTE.ACP_Week

see: http://sqlfiddle.com/#!3/b8158/19
``````
0

Author Comment

This is very close, although it seems to be swapping the overtime and normal time for the entry that triggers overtime.  The rest of the overtime entries would be correct, but the first record that qualifies for overtime has the values reversed.
0

LVL 48

Expert Comment

can you provide examples of "but the first record that qualifies for overtime has the values reversed" please
0

Author Comment

If you look at the result set that you posted above, the 8.75 hours in the "PAD_OVERTIME" column for "PAD_EMPLOYEENUMBER" 1004 on "PAD_DATE" 2013-07-18 should be in the "PAD_NORMALTIME" column.  The 1.25 hours in the "PAD_NORMALTIME" column of the same record should be considered "PAD_OVERTIME".
0

LVL 48

Accepted Solution

PortletPaul earned 500 total points
I'm sorry I just hadn't noticed that, my bad. the right figures, wrong columns.

``````;WITH
CTE AS (

-- this would be some query using your existing view
-- we use a CTE because we need to join back to this result
select * from CurrentResults
)
SELECT
- CASE
, CASE
, CTE.ACP_Week
FROM CTE
LEFT JOIN CTE AS PREV
AND CTE.ACP_Week = PREV.ACP_Week
GROUP BY
, CTE.ACP_Week
ORDER BY
CTE.ACP_Week
``````
``````| PAD_EMPLOYEENUMBER |   PAD_DATE | PAD_DEPARTMENTNUMBER | PAD_WORKHOURS | PAD_NORMALTIME | PAD_OVERTIME | PAD_PTOHOURS | PAD_HOLIDAYHOURS | PAD_OTHERPAY | PAD_OTHERPAYREASON | ACP_WEEK |
|--------------------|------------|----------------------|---------------|----------------|--------------|--------------|------------------|--------------|--------------------|----------|
|               1004 | 2013-07-15 |                  400 |           9.5 |            9.5 |            0 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-16 |                  400 |            10 |             10 |            0 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-17 |                  400 |         11.75 |          11.75 |            0 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-18 |                  400 |            10 |           8.75 |         1.25 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-19 |                  400 |         10.08 |              0 |        10.08 |            0 |                0 |            0 |             (null) |       29 |
|               1004 | 2013-07-23 |                  400 |         10.08 |          10.08 |            0 |            0 |                0 |            0 |             (null) |       30 |
|               1004 | 2013-07-24 |                  400 |         11.68 |          11.68 |            0 |            0 |                0 |            0 |             (null) |       30 |
|               1004 | 2013-08-08 |                  400 |         11.63 |          11.63 |            0 |            0 |                0 |            0 |             (null) |       32 |
|               1004 | 2013-08-20 |                  400 |         10.18 |          10.18 |            0 |            0 |                0 |            0 |             (null) |       34 |
|               1004 | 2013-08-21 |                  400 |         12.12 |          12.12 |            0 |            0 |                0 |            0 |             (null) |       34 |
|               1004 | 2013-08-22 |                  400 |         10.08 |          10.08 |            0 |            0 |                0 |            0 |             (null) |       34 |
|               1004 | 2013-08-27 |                  400 |           9.6 |            9.6 |            0 |            0 |                0 |            0 |             (null) |       35 |
|               1004 | 2013-08-28 |                  400 |         12.21 |          12.21 |            0 |            0 |                0 |            0 |             (null) |       35 |
|               1004 | 2013-08-29 |                  400 |         10.23 |          10.23 |            0 |            0 |                0 |            0 |             (null) |       35 |
|               1004 | 2013-09-25 |                  400 |         11.86 |          11.86 |            0 |            0 |                0 |            0 |             (null) |       39 |
|               1004 | 2013-09-26 |                  400 |          9.77 |           9.77 |            0 |            0 |                0 |            0 |             (null) |       39 |
|               1004 | 2013-09-27 |                  400 |          9.62 |           9.62 |            0 |            0 |                0 |            0 |             (null) |       39 |
|               1004 | 2013-10-01 |                  400 |          9.75 |           9.75 |            0 |            0 |                0 |            0 |             (null) |       40 |
|               1004 | 2013-10-02 |                  400 |         11.91 |          11.91 |            0 |            0 |                0 |            0 |             (null) |       40 |
|               1004 | 2013-10-03 |                  400 |          9.62 |           9.62 |            0 |            0 |                0 |            0 |             (null) |       40 |
``````
http://sqlfiddle.com/#!3/b5a2c/1
0

Author Closing Comment

This is exactly what I was looking for. Thanks so much!
0

## Featured Post

### Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…