cross apply failing - SQL Server Forums

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)

cross apply failing

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-03 : 16:20:35

can anyone see why this script is failing? it was working before i put the CROSS APPLY in. any help would be appreciated. thanks.

Declare @Startdate datetime;
set @Startdate = '05/09/2009';

SELECT
PayWeek,EmployeeNumber,TransNumber,CostCenter,PayCategory,
MAX(Date)Date,MAX(EmployeeName)EmployeeName,MAX(JobTitle)JobTitle,
MAX(Hours)Hours,MAX(Hours1)Hours1,WorkLocation
FROM(

SELECT
ROW_NUMBER() OVER (PARTITION BY PayWeek,EmployeeNumber ORDER BY Date) AS TransNumber,
Date, EmployeeNumber, EmployeeName, JobTitle, Hours,
Hours1, CostCenter, PayCategory, PayWeek, WorkLocation
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber='101503' AND (Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)

) AS X
GROUP BY PayWeek,EmployeeNumber,TransNumber,CostCenter,PayCategory,WorkLocation

CROSS APPLY (
SELECT
SUM(Hours1) AS TotalHours
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber=X.EmployeeNumber AND PayWeek =X.PayWeek AND TransNumber<=X.TransNumber)Y

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-03 : 23:22:36

What do you want to do with the TotalHours? CROSS APPLY is the wrong thing to use here, but i'm not certain what you're trying to do, perhaps this?

Declare @Startdate datetime;
set @Startdate = '05/09/2009';

SELECT
PayWeek,EmployeeNumber,TransNumber,CostCenter,PayCategory,
MAX(Date)Date,MAX(EmployeeName)EmployeeName,MAX(JobTitle)JobTitle,
MAX(Hours)Hours,MAX(Hours1)Hours1,WorkLocation,
(SELECT SUM(Hours1)
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber=X.EmployeeNumber AND PayWeek =X.PayWeek AND TransNumber<=X.TransNumber) AS TotalHours
FROM(

SELECT
ROW_NUMBER() OVER (PARTITION BY PayWeek,EmployeeNumber ORDER BY Date) AS TransNumber,
Date, EmployeeNumber, EmployeeName, JobTitle, Hours,
Hours1, CostCenter, PayCategory, PayWeek, WorkLocation
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber='101503' AND (Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)
) AS X
GROUP BY PayWeek,EmployeeNumber,TransNumber,CostCenter,PayCategory,WorkLocation

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-04 : 09:59:35

Here is what I have with you script. I am wanting to have the Total Hours be a running total within the PayWeek. If over 40 hours then the hours over would be moved to OT hours. It is not working correctly. It is showing Total Hours as some erroneous total that is over 40 so detail row hours is showing as OT Hours. Here is the code with the result set to follow. Thanks for your help.

Declare @Startdate datetime;
set @Startdate = '05/09/2009';

WITH PAYROLL AS(

SELECT
PayWeek,EmployeeNumber,TransNumber,CostCenter,PayCategory,
MAX(Date)Date,MAX(EmployeeName)EmployeeName,MAX(JobTitle)JobTitle,
MAX(Hours)Hours,MAX(Hours1)Hours1,WorkLocation,
(SELECT SUM(Hours1)
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber=X.EmployeeNumber AND PayWeek =X.PayWeek AND TransNumber<=X.TransNumber) AS TotalHours
FROM(

SELECT
ROW_NUMBER() OVER (PARTITION BY PayWeek,EmployeeNumber ORDER BY Date) AS TransNumber,
Date, EmployeeNumber, EmployeeName, JobTitle, Hours,
Hours1, CostCenter, PayCategory, PayWeek, WorkLocation
FROM VW_WellService_PayrollView2
WHERE EmployeeNumber='101503' AND (Date BETWEEN DATEADD(DAY,-14,@StartDate) AND @StartDate)
) AS X
GROUP BY PayWeek,EmployeeNumber,TransNumber,CostCenter,PayCategory,WorkLocation

)

SELECT

TransNumber,PayWeek,Date,EmployeeNumber,EmployeeName,WorkLocation,CostCenter,
Hours, PayCategory,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-27 00:00:00.000 101503 Ates, William MWSBOW MER020 8.5 FLOOR 63 0 8.5
2 1 2009-04-27 00:00:00.000 101503 Ates, William MWSBOW MER020 1.5 FLOOR 63 0 1.5
3 1 2009-04-28 00:00:00.000 101503 Ates, William MWSBOW MER020 1.5 FLOOR 63 0 1.5
4 1 2009-04-28 00:00:00.000 101503 Ates, William MWSBOW MER020 6.5 FLOOR 63 0 6.5
5 1 2009-04-28 00:00:00.000 101503 Ates, William MWSBOW MER020 3 FLOOR 63 0 3
6 1 2009-04-29 00:00:00.000 101503 Ates, William MWSBOW MER020 5 FLOOR 63 0 5
7 1 2009-04-30 00:00:00.000 101503 Ates, William MWSBOW MER020 8 FLOOR 63 0 8
1 2 2009-05-04 00:00:00.000 101503 Ates, William MWSBOW MER020 2 FLOOR 71.5 0 2
2 2 2009-05-04 00:00:00.000 101503 Ates, William MWSBOW MER020 7 FLOOR 71.5 0 7
3 2 2009-05-04 00:00:00.000 101503 Ates, William MWSBOW MER020 2.5 FLOOR 71.5 0 2.5
4 2 2009-05-05 00:00:00.000 101503 Ates, William MWSBOW MER020 3 FLOOR 71.5 0 3
5 2 2009-05-05 00:00:00.000 101503 Ates, William MWSBOW MER020 8 FLOOR 71.5 0 8
6 2 2009-05-06 00:00:00.000 101503 Ates, William MWSBOW MER020 8 FLOOR 71.5 0 8
7 2 2009-05-07 00:00:00.000 101503 Ates, William MWSBOW MER020 6 FLOOR 71.5 0 6
8 2 2009-05-07 00:00:00.000 101503 Ates, William MWSBOW MER020 3.5 FLOOR 71.5 0 3.5
9 2 2009-05-08 00:00:00.000 101503 Ates, William MWSBOW MER020 10.5 FLOOR 71.5 0 10.5

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-04 : 11:03:01

I have closed this topic and re-opened one under the heading of 'Running Total' error. Thanks for all the help. I appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 12:59:13

i have answered there. see if that's what you asked for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources