Solved

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

Posted on 2014-07-21
13
182 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
Comment Utility
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
Comment Utility
Many thanks for the formatting!
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
| 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
Comment Utility
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
Comment Utility
can you provide examples of "but the first record that qualifies for overtime has the values reversed" please
0
 

Author Comment

by:paragonfoods
Comment Utility
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
Comment Utility
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
Comment Utility
This is exactly what I was looking for. Thanks so much!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now