Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Running Total

Author  Topic 

osupratt
Posting Yak Master

238 Posts
Posted - 2009-06-04 : 11:11:42
I have some script that is working except for the Running Total part. I have a column TotalHrs which should give a Running Total, but it's giving a Total (per payweek). Does anyone see anything on why this is not working? I have the result set following. Thanks to anyone who can point me in the right direction.

Declare @Startdate datetime;
set @Startdate = '05/09/2009';
WITH PAYROLL AS(

SELECT
PayWeek,TransNumber,MAX(Date)Date, EmployeeNumber,
MAX(Hours)Hours, MAX(Hours1)Hours1,MAX(TotalHours)TotalHours
FROM(
SELECT
ROW_NUMBER() OVER (PARTITION BY PayWeek,EmployeeNumber ORDER BY Date) AS TransNumber,
Date, EmployeeNumber, Hours, Hours1, PayWeek
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber='101671'AND(Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)
) AS t

CROSS APPLY(
SELECT
SUM(Hours1) AS TotalHours
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber=t.EmployeeNumber AND PayWeek=t.PayWeek AND TransNumber<=t.TransNumber AND
EmployeeNumber='101671' AND (Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)) t1
GROUP BY PayWeek,TransNumber,EmployeeNumber
)

SELECT
TransNumber,PayWeek,Date,EmployeeNumber,Hours, TotalHours,

CASE
WHEN TotalHours <=40 THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (Hours-(TotalHours-40))
ELSE 0 END AS HrsREG,

CASE
WHEN TotalHours >40 AND TotalHours-40 > Hours THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (TotalHours-40)
ELSE 0 END AS HrsOT

FROM PAYROLL


result set:

1 1 2009-04-25 00:00:00.000 101671 12.5 51.5 1 11.5
2 1 2009-04-25 00:00:00.000 101671 1 51.5 0 1
3 1 2009-04-27 00:00:00.000 101671 2.5 51.5 0 2.5
4 1 2009-04-27 00:00:00.000 101671 1 51.5 0 1
5 1 2009-04-27 00:00:00.000 101671 6 51.5 0 6
6 1 2009-04-28 00:00:00.000 101671 12.5 51.5 1 11.5
7 1 2009-04-28 00:00:00.000 101671 1 51.5 0 1
8 1 2009-05-01 00:00:00.000 101671 1 51.5 0 1
9 1 2009-05-01 00:00:00.000 101671 0.5 51.5 0 0.5
10 1 2009-05-01 00:00:00.000 101671 12.5 51.5 1 11.5
11 1 2009-05-01 00:00:00.000 101671 1 51.5 0 1
1 2 2009-05-02 00:00:00.000 101671 13 76.5 0 13
2 2 2009-05-02 00:00:00.000 101671 1 76.5 0 1
3 2 2009-05-04 00:00:00.000 101671 12 76.5 0 12
4 2 2009-05-04 00:00:00.000 101671 1 76.5 0 1
5 2 2009-05-05 00:00:00.000 101671 13.5 76.5 0 13.5
6 2 2009-05-05 00:00:00.000 101671 1 76.5 0 1
7 2 2009-05-06 00:00:00.000 101671 13.5 76.5 0 13.5
8 2 2009-05-06 00:00:00.000 101671 1 76.5 0 1
9 2 2009-05-07 00:00:00.000 101671 12.5 76.5 0 12.5
10 2 2009-05-07 00:00:00.000 101671 1 76.5 0 1
11 2 2009-05-08 00:00:00.000 101671 7 76.5 0 7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-04 : 12:56:39
quote:
Originally posted by osupratt

I have some script that is working except for the Running Total part. I have a column TotalHrs which should give a Running Total, but it's giving a Total (per payweek). Does anyone see anything on why this is not working? I have the result set following. Thanks to anyone who can point me in the right direction.

Declare @Startdate datetime;
set @Startdate = '05/09/2009';
WITH PAYROLL AS(

SELECT
PayWeek,TransNumber,MAX(Date)Date, EmployeeNumber,
MAX(Hours)Hours, MAX(Hours1)Hours1,MAX(TotalHours)TotalHours
FROM(
SELECT
ROW_NUMBER() OVER (PARTITION BY PayWeek,EmployeeNumber ORDER BY Date) AS TransNumber,
Date, EmployeeNumber, Hours, Hours1, PayWeek
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber='101671'AND(Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)
) AS t

CROSS APPLY(
SELECT
SUM(Hours1) AS TotalHours
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber=t.EmployeeNumber AND PayWeek=t.PayWeek AND TransNumber<=t.TransNumber AND
EmployeeNumber='101671' AND (Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)) t1
GROUP BY PayWeek,TransNumber,EmployeeNumber
)

SELECT
TransNumber,PayWeek,Date,EmployeeNumber,Hours, TotalHours,

CASE
WHEN TotalHours <=40 THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (Hours-(TotalHours-40))
ELSE 0 END AS HrsREG,

CASE
WHEN TotalHours >40 AND TotalHours-40 > Hours THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (TotalHours-40)
ELSE 0 END AS HrsOT

FROM PAYROLL


result set:

1 1 2009-04-25 00:00:00.000 101671 12.5 51.5 1 11.5
2 1 2009-04-25 00:00:00.000 101671 1 51.5 0 1
3 1 2009-04-27 00:00:00.000 101671 2.5 51.5 0 2.5
4 1 2009-04-27 00:00:00.000 101671 1 51.5 0 1
5 1 2009-04-27 00:00:00.000 101671 6 51.5 0 6
6 1 2009-04-28 00:00:00.000 101671 12.5 51.5 1 11.5
7 1 2009-04-28 00:00:00.000 101671 1 51.5 0 1
8 1 2009-05-01 00:00:00.000 101671 1 51.5 0 1
9 1 2009-05-01 00:00:00.000 101671 0.5 51.5 0 0.5
10 1 2009-05-01 00:00:00.000 101671 12.5 51.5 1 11.5
11 1 2009-05-01 00:00:00.000 101671 1 51.5 0 1
1 2 2009-05-02 00:00:00.000 101671 13 76.5 0 13
2 2 2009-05-02 00:00:00.000 101671 1 76.5 0 1
3 2 2009-05-04 00:00:00.000 101671 12 76.5 0 12
4 2 2009-05-04 00:00:00.000 101671 1 76.5 0 1
5 2 2009-05-05 00:00:00.000 101671 13.5 76.5 0 13.5
6 2 2009-05-05 00:00:00.000 101671 1 76.5 0 1
7 2 2009-05-06 00:00:00.000 101671 13.5 76.5 0 13.5
8 2 2009-05-06 00:00:00.000 101671 1 76.5 0 1
9 2 2009-05-07 00:00:00.000 101671 12.5 76.5 0 12.5
10 2 2009-05-07 00:00:00.000 101671 1 76.5 0 1
11 2 2009-05-08 00:00:00.000 101671 7 76.5 0 7

modify like above and see
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-04 : 13:08:38
One suspicious thing is the two columns: [Hours] and [Hours1]
Your [TotalHours] running total is based on [Hours1] but your detail is base on [Hours].

But I think this is the problem:
Your CROSS APPLY statment "...AND TransNumber<=t.TransNumber...". TransNumber doesn't exist in VW_WellService_PayrollView2 so your are just comparing t.TransNumber with itself. Try changing that to: "date<=t.date"


Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-04 : 13:14:02
Yeah, that seems to be the issue:

declare @VW_WellService_PayrollView2 table 
       (employeeNumber int
       ,PayWeek int
       ,Hours int
       ,Hours1 int
       ,date datetime)
insert @VW_WellService_PayrollView2
select 101671,1,1,1,'05/09/2009' union all
select 101671,1,1,1,dateadd(day, -1, '05/09/2009') union all
select 101671,1,1,1,dateadd(day, -2, '05/09/2009') union all
select 101671,2,1,1,'05/09/2009' union all
select 101671,2,1,1,dateadd(day, -1, '05/09/2009') union all
select 101671,2,1,1,dateadd(day, -2, '05/09/2009') 


Declare @Startdate datetime;
set @Startdate = '05/09/2009';
WITH PAYROLL AS(

SELECT
PayWeek,TransNumber,MAX(Date)Date, EmployeeNumber,
MAX(Hours)Hours, MAX(Hours1)Hours1,MAX(TotalHours)TotalHours
FROM(
SELECT
ROW_NUMBER() OVER (PARTITION BY PayWeek,EmployeeNumber ORDER BY Date) AS TransNumber,
Date, EmployeeNumber, Hours, Hours1, PayWeek
FROM @VW_WellService_PayrollView2
WHERE EmployeeNumber='101671'AND(Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)
) AS t

CROSS APPLY(
SELECT
SUM(Hours1) AS TotalHours
FROM @VW_WellService_PayrollView2
WHERE EmployeeNumber=t.EmployeeNumber AND PayWeek=t.PayWeek AND  date<=t.date  AND
EmployeeNumber='101671' AND (Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)) t1
GROUP BY PayWeek,TransNumber,EmployeeNumber
)

SELECT
TransNumber,PayWeek,Date,EmployeeNumber,Hours, TotalHours,

CASE
WHEN TotalHours <=40 THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (Hours-(TotalHours-40))
ELSE 0 END AS HrsREG,

CASE
WHEN TotalHours >40 AND TotalHours-40 > Hours THEN Hours
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN (TotalHours-40)
ELSE 0 END AS HrsOT

FROM PAYROLL

OUTPUT:
TransNumber          PayWeek     Date                    EmployeeNumber Hours       TotalHours  HrsREG      HrsOT
-------------------- ----------- ----------------------- -------------- ----------- ----------- ----------- -----------
1                    1           2009-05-07 00:00:00.000 101671         1           1           1           0
2                    1           2009-05-08 00:00:00.000 101671         1           2           1           0
3                    1           2009-05-09 00:00:00.000 101671         1           3           1           0
1                    2           2009-05-07 00:00:00.000 101671         1           1           1           0
2                    2           2009-05-08 00:00:00.000 101671         1           2           1           0
3                    2           2009-05-09 00:00:00.000 101671         1           3           1           0


Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-04 : 13:22:52
Then you can simplify your statement to something like this:

select row_number() over (partition by employeeNumber, payweek order by date) as TransNumber
       ,t.PayWeek
       ,t.Date
       ,t.EmployeeNumber
       ,t.Hours
       ,ca.totalHours
       --CASE statements
from   VW_WellService_PayrollView2 t
cross apply (
       select sum(hours) as totalHours
       from   @VW_WellService_PayrollView2
       where  employeeNumber = t.employeeNumber
       and    payweek = t.payweek
       and    date <= t.date
       ) ca


Be One with the Optimizer
TG
Go to Top of Page

osupratt
Posting Yak Master

238 Posts
Posted - 2009-06-04 : 14:06:32
This is almost correct TG, but since there can be more than one detail row per DATE it is still not doing the running total correctly. Here is the result set with revising the WHERE statement in the cross apply to date<=t.date. That is why I was trying to use the TransNumber. Is there anyway around this?

result set.... notice dates and TotalHrs.

1 1 2009-04-25 00:00:00.000 101671 12.5 13.5 12.5 0
2 1 2009-04-25 00:00:00.000 101671 1 13.5 1 0
3 1 2009-04-27 00:00:00.000 101671 2.5 23 2.5 0
4 1 2009-04-27 00:00:00.000 101671 1 23 1 0
5 1 2009-04-27 00:00:00.000 101671 6 23 6 0
6 1 2009-04-28 00:00:00.000 101671 12.5 36.5 12.5 0
7 1 2009-04-28 00:00:00.000 101671 1 36.5 1 0
8 1 2009-05-01 00:00:00.000 101671 1 51.5 0 1
9 1 2009-05-01 00:00:00.000 101671 0.5 51.5 0 0.5
10 1 2009-05-01 00:00:00.000 101671 12.5 51.5 1 11.5
11 1 2009-05-01 00:00:00.000 101671 1 51.5 0 1
1 2 2009-05-02 00:00:00.000 101671 13 14 13 0
2 2 2009-05-02 00:00:00.000 101671 1 14 1 0
3 2 2009-05-04 00:00:00.000 101671 12 27 12 0
4 2 2009-05-04 00:00:00.000 101671 1 27 1 0
5 2 2009-05-05 00:00:00.000 101671 13.5 41.5 12 1.5
6 2 2009-05-05 00:00:00.000 101671 1 41.5 0 1
7 2 2009-05-06 00:00:00.000 101671 13.5 56 0 13.5
8 2 2009-05-06 00:00:00.000 101671 1 56 0 1
9 2 2009-05-07 00:00:00.000 101671 12.5 69.5 0 12.5
10 2 2009-05-07 00:00:00.000 101671 1 69.5 0 1
11 2 2009-05-08 00:00:00.000 101671 7 76.5 0 7


thanks for your help. i was excited because it almost worked.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-04 : 14:45:44
Within a set of detail rows with the same date does the sequence matter? Is there any columns in the row which makes these (same date) detail rows unique?


Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-04 : 14:53:55
If not then we can go back to a simpler version of your CTE and TransNumber:
The problem is that since the order of these "dupe" rows is not guaranteed one execution to the next could give different orders.

;with payroll
as
       (
       select row_number() over (partition by employeeNumber, payweek order by date) as TransNumber
              ,t.PayWeek
              ,t.Date
              ,t.EmployeeNumber
              ,t.Hours
       from   VW_WellService_PayrollView2 t
       ) 
select transNumber
       ,payweek
       ,employeeNumber
       ,hours
       ,ca.totalHours
       ,--CASE statements
from   Payroll p
cross apply (
       select sum(hours) as totalHours
       from   payroll
       where  employeeNumber = p.employeeNumber
       and    payweek = p.payweek
       and    transNumber <= p.transNumber
       ) ca


Be One with the Optimizer
TG
Go to Top of Page

osupratt
Posting Yak Master

238 Posts
Posted - 2009-06-04 : 16:31:21
That looks as though it is working. There are some columns in this query that were left out to make things a bit easier to read. The main thought here is that an employee may work at one location for 4 hours on day 1 and then 4 hours at another location on day 1 and also have 2 hours travel time on the same day. Then the thought is that there is a working classification, a driving classification, and more.

I will graciously take what you have given me and try to make this work. If I have any other issues I will possibly post to the same topic. Thanks for your awesome help.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-06-04 : 16:49:29
No problem :) It looks like you've got a good handle on it now

Be One with the Optimizer
TG
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site