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

paragonfoodsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paragonfoodsAuthor Commented:
This is exactly what I was looking for. Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.