Solved

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

Posted on 2014-07-21
13
188 Views
Last Modified: 2014-07-28
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) 
                      AS PAD_OtherPayReason, AP.ACP_Week
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) AS PAD_Date, dbo.EmployeeHours.Tracked1, 
                                              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
                      db_datareader.AccountingPeriod AS AP ON p.PAD_Date = AP.ACP_Date
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

Open in new window

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

Open in new window

0
Comment
Question by:paragonfoods
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40209891
Here is some formatting just to help any experts who jump in...
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) AS PAD_OtherPayReason, AP.ACP_Week

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
		 ) AS PAD_Date, dbo.EmployeeHours.Tracked1, 
	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 db_datareader.AccountingPeriod AS AP ON p.PAD_Date = AP.ACP_Date
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

Open in new window

0
 

Author Comment

by:paragonfoods
ID: 40209896
Many thanks for the formatting!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40210117
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210645
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

by:paragonfoods
ID: 40211348
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

by:David L. Hansen
ID: 40212944
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

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

        sum(PAD_WorkHours) over (partition by PAD_EmployeeNumber, ACP_Week
                                order by PAD_Date) as run_tot

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?)
desired result
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40213478
| 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 |
		

Open in new window

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
      CTE.PAD_EmployeeNumber
    , convert(varchar(10),CTE.PAD_Date,120) PAD_Date
    , CTE.PAD_DepartmentNumber
    , CTE.PAD_WorkHours
    , CTE.PAD_WorkHours
      - CASE
            WHEN SUM(PREV.PAD_WorkHours) > 40 AND
            (SUM(PREV.PAD_WorkHours) - CTE.PAD_WorkHours) < 40 THEN 40 - (SUM(PREV.PAD_WorkHours) - CTE.PAD_WorkHours)
            WHEN SUM(PREV.PAD_WorkHours) > 40 THEN CTE.PAD_WorkHours
            ELSE 0 END        AS PAD_NormalTime
    , CASE
            WHEN SUM(PREV.PAD_WorkHours) > 40 AND
            (SUM(PREV.PAD_WorkHours) - CTE.PAD_WorkHours) < 40 THEN 40 - (SUM(PREV.PAD_WorkHours) - CTE.PAD_WorkHours)
            WHEN SUM(PREV.PAD_WorkHours) > 40 THEN CTE.PAD_WorkHours
            ELSE 0 END        AS PAD_OverTime
    , CTE.PAD_PTOHours
    , CTE.PAD_HolidayHours
    , CTE.PAD_OtherPay
    , CTE.PAD_OtherPayReason
    , CTE.ACP_Week
FROM CTE
      LEFT JOIN CTE AS PREV
                  ON CTE.PAD_EmployeeNumber = PREV.PAD_EmployeeNumber
                        AND CTE.ACP_Week = PREV.ACP_Week
                        AND PREV.PAD_Date <= CTE.PAD_Date
GROUP BY
      CTE.PAD_EmployeeNumber
    , CTE.PAD_Date
    , CTE.PAD_DepartmentNumber
    , CTE.PAD_WorkHours
    , CTE.PAD_PTOHours
    , CTE.PAD_HolidayHours
    , CTE.PAD_OtherPay
    , CTE.PAD_OtherPayReason
    , CTE.ACP_Week
ORDER BY
      CTE.ACP_Week
    , CTE.PAD_EmployeeNumber


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

Open in new window

0
 

Author Comment

by:paragonfoods
ID: 40222742
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

by:PortletPaul
ID: 40223227
can you provide examples of "but the first record that qualifies for overtime has the values reversed" please
0
 

Author Comment

by:paragonfoods
ID: 40223244
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

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

Try this instead:
;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
      CTE.PAD_EmployeeNumber
    , convert(varchar(10),CTE.PAD_Date,120) PAD_Date
    , CTE.PAD_DepartmentNumber
    , CTE.PAD_WorkHours
    , CTE.PAD_WorkHours
      - CASE
            WHEN SUM(PREV.PAD_WorkHours) > 40 AND
            (SUM(PREV.PAD_WorkHours) - CTE.PAD_WorkHours) < 40 THEN SUM(PREV.PAD_WorkHours) - 40
            WHEN SUM(PREV.PAD_WorkHours) > 40 THEN CTE.PAD_WorkHours
            ELSE 0 END        AS PAD_NormalTime
    , CASE
            WHEN SUM(PREV.PAD_WorkHours) > 40 AND
            (SUM(PREV.PAD_WorkHours) - CTE.PAD_WorkHours) < 40 THEN ABS(40 - SUM(PREV.PAD_WorkHours)) 
            WHEN SUM(PREV.PAD_WorkHours) > 40 THEN CTE.PAD_WorkHours
            ELSE 0 END        AS PAD_OverTime
    , CTE.PAD_PTOHours
    , CTE.PAD_HolidayHours
    , CTE.PAD_OtherPay
    , CTE.PAD_OtherPayReason
    , CTE.ACP_Week
FROM CTE
      LEFT JOIN CTE AS PREV
                  ON CTE.PAD_EmployeeNumber = PREV.PAD_EmployeeNumber
                        AND CTE.ACP_Week = PREV.ACP_Week
                        AND PREV.PAD_Date <= CTE.PAD_Date
GROUP BY
      CTE.PAD_EmployeeNumber
    , CTE.PAD_Date
    , CTE.PAD_DepartmentNumber
    , CTE.PAD_WorkHours
    , CTE.PAD_PTOHours
    , CTE.PAD_HolidayHours
    , CTE.PAD_OtherPay
    , CTE.PAD_OtherPayReason
    , CTE.ACP_Week
ORDER BY
      CTE.ACP_Week
    , CTE.PAD_EmployeeNumber

Open in new window

| 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 |

Open in new window

http://sqlfiddle.com/#!3/b5a2c/1
0
 

Author Closing Comment

by:paragonfoods
ID: 40223896
This is exactly what I was looking for. Thanks so much!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question