Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

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
paragonfoods
Asked:
paragonfoods
  • 5
  • 5
  • 2
  • +1
1 Solution
 
David L. HansenProgrammer AnalystCommented:
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
 
paragonfoodsAuthor Commented:
Many thanks for the formatting!
0
 
hnasrCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
PortletPaulCommented:
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
 
paragonfoodsAuthor Commented:
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
 
David L. HansenProgrammer AnalystCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
| 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
 
paragonfoodsAuthor Commented:
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
 
PortletPaulCommented:
can you provide examples of "but the first record that qualifies for overtime has the values reversed" please
0
 
paragonfoodsAuthor Commented:
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
 
PortletPaulCommented:
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
 
paragonfoodsAuthor Commented:
This is exactly what I was looking for. Thanks so much!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now