pulling data from function NOT correct

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)

pulling data from function NOT correct

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-09-22 : 16:57:45

I have a function that returns correct data. It is as follows:

@PayPeriodEnd datetime,
@HomeLoc nvarchar (10)

)
RETURNS TABLE
AS RETURN (

WITH payroll
AS(
SELECT
ROW_NUMBER() OVER (PARTITION BY employeeNumber, payweek ORDER BY date) AS TransNumber,
t.PayWeek, t.ID, t.Date, t.HomeLocation, t.EmployeeNumber, t.Hours, t.Hours1
FROM VW_WellService_PayrollView2 t
WHERE Date >= DATEADD(DAY,-13,@PayPeriodEnd ) AND Date <= DATEADD(DAY,-0,@PayPeriodEnd )
AND HomeLocation=@HomeLoc
)
SELECT
transNumber,payweek,ID,date,homelocation,employeeNumber,hours,hours1,ca.TotalHours

CASE
WHEN TotalHours <=40 THEN (Hours +Hours1)
WHEN TotalHours >40 AND TotalHours-40 <= Hours THEN ((Hours-(TotalHours-40))+Hours1)
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 p

cross apply (
select
sum(hours) as TotalHours
from payroll
where
employeeNumber = p.employeeNumber and payweek = p.payweek and transNumber <= p.transNumber
) ca
)

It returns the following with me specifying a date and employee:

transNumber payweek ID date homelocation employeeNumber hours hours1 TotalHours HrsREG HrsOT
1 1 9169 2009-09-12 00:00:00.000 MWSJAC 101756 7 0 7 7 0
2 1 9173 2009-09-12 00:00:00.000 MWSJAC 101756 2 0 9 2 0
3 1 9970 2009-09-16 00:00:00.000 MWSJAC 101756 11 0 20 11 0
4 1 9975 2009-09-16 00:00:00.000 MWSJAC 101756 2 0 22 2 0
5 1 10494 2009-09-17 00:00:00.000 MWSJAC 101756 12 0 34 12 0
6 1 10499 2009-09-17 00:00:00.000 MWSJAC 101756 2 0 36 2 0
7 1 10758 2009-09-18 00:00:00.000 MWSJAC 101756 7 0 43 4 3
8 1 10762 2009-09-18 00:00:00.000 MWSJAC 101756 1.5 0 44.5 0 1.5
9 1 10763 2009-09-18 00:00:00.000 MWSJAC 101756 6 0 50.5 0 6
10 1 10767 2009-09-18 00:00:00.000 MWSJAC 101756 1 0 51.5 0 1
1 2 10768 2009-09-19 00:00:00.000 MWSJAC 101756 8 0 8 8 0
2 2 10772 2009-09-19 00:00:00.000 MWSJAC 101756 2.5 0 10.5 2.5 0
3 2 11206 2009-09-21 00:00:00.000 MWSJAC 101756 7 0 17.5 7 0
4 2 11210 2009-09-21 00:00:00.000 MWSJAC 101756 1.5 0 19 1.5 0
5 2 11211 2009-09-21 00:00:00.000 MWSJAC 101756 5.5 0 24.5 5.5 0
6 2 11215 2009-09-21 00:00:00.000 MWSJAC 101756 1 0 25.5 1 0

This function is showing correct data. Please notice transaction # 7 above in red.

When I try to pull the data in a query FROM this function:

SELECT
transNumber, payweek, ID, date, employeeNumber, employeename, jobtitle, hours, hours1,
TotalHours, HrsREG, HrsOT
FROM dbo.FCTN_WellService_PayrollTableFunction(@PayPeriodEnd,@HomeLocation) AS FCTN_WellService_PayrollTableFunction_1

This is what is returned (you'll notice that the original transaction # 7 has moved down to transaction #10 and the hours calculations are now off.

transNumber payweek ID date employeeNumber employeename jobtitle hours hours1 TotalHours HrsREG HrsOT
1 1 9169 2009-09-12 00:00:00.000 101756 Lopez, Mauro Operator 7 0 7 7 0
2 1 9173 2009-09-12 00:00:00.000 101756 Lopez, Mauro Driver 2 0 9 2 0
3 1 9970 2009-09-16 00:00:00.000 101756 Lopez, Mauro Operator 11 0 20 11 0
4 1 9975 2009-09-16 00:00:00.000 101756 Lopez, Mauro Driver 2 0 22 2 0
5 1 10494 2009-09-17 00:00:00.000 101756 Lopez, Mauro Operator 12 0 34 12 0
6 1 10499 2009-09-17 00:00:00.000 101756 Lopez, Mauro Driver 2 0 36 2 0
7 1 10767 2009-09-18 00:00:00.000 101756 Lopez, Mauro Driver 1 0 37 1 0
8 1 10762 2009-09-18 00:00:00.000 101756 Lopez, Mauro Driver 1.5 0 38.5 1.5 0
9 1 10763 2009-09-18 00:00:00.000 101756 Lopez, Mauro Operator 6 0 44.5 1.5 4.5
10 1 10758 2009-09-18 00:00:00.000 101756 Lopez, Mauro Operator 7 0 51.5 0 7
1 2 10768 2009-09-19 00:00:00.000 101756 Lopez, Mauro Operator 8 0 8 8 0
2 2 10772 2009-09-19 00:00:00.000 101756 Lopez, Mauro Driver 2.5 0 10.5 2.5 0
3 2 11210 2009-09-21 00:00:00.000 101756 Lopez, Mauro Driver 1.5 0 12 1.5 0
4 2 11211 2009-09-21 00:00:00.000 101756 Lopez, Mauro Operator 5.5 0 17.5 5.5 0
5 2 11215 2009-09-21 00:00:00.000 101756 Lopez, Mauro Driver 1 0 18.5 1 0
6 2 11206 2009-09-21 00:00:00.000 101756 Lopez, Mauro Operator 7 0 25.5 7 0

Would anyone know why this is. I thought the function returned a table and that in my second query i was just pulling from this. I don't need to re-calculate. Any help is appreciated. Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-22 : 17:20:07

"ROW_NUMBER() OVER (PARTITION BY employeeNumber, payweek ORDER BY date)"

You are ordering the rows by date but rows 7 through 10 all have the same date. In the event of a tie, there is no guarantee of which order these four rows will be returned. You could define additional ordering columns (e.g., Date, Employeenumber).

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

osupratt
Posting Yak Master

238 Posts

Posted - 2009-09-22 : 18:28:16

ok, but again, i thought that since in the function it states 'RETURNS TABLE' that when querying from the function implied that i was basically querying from a table where the records were set. such as transaction number, etc. i mean, it looks as though it is re-calculating the data. ???

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-22 : 19:00:28

Let's back up a bit and cover the basics. When you query a table, whether it is real or virtual, like from a function, there is no implicit order to the data returned. You can't rely on the rows coming back in a specific order. You can impose an order by using the ORDER BY clause. You are using one in the ROW_NUMBER function but, since there are ties, the order within those rows is undefined.

In short, if you want a specific row to be the seventh one, you need to refine the definition of what makes that row seventh.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

osupratt
Posting Yak Master

238 Posts

Posted - 2009-09-22 : 19:13:14

so if i query this function (table) and put where transnumber =7 ... i can't expect to find the data that shows when the function is run? i'm confused. it seems like the table is re-calculating rather than just listing what is in it. sorry, my knowledge on this is null and i'm learning as i go. thanks for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-22 : 19:27:42

You are correct, the data that gets returned from the function call by itself will not necessarily be the same when used within your larger SQL statement. BUT if you were to call your function by itself often enough, you would eventually get different data, too. The "table" data is the same but the order of the rows would be different and since we are creating a data column (ROW_NUMBER) that IS the data order, the ROW_NUMBER data column will bounce around. You have not given the SQL engine enough information to CONSISTENTLY order the data in the same way every time. While it is true that you told it to order by the DATE column, the SQL engine has no way of knowing which row you want seventh when there are four different rows that are equally ranked by that column.

You will notice that the data returned will OFTEN come back in the same desired order - it's just not guaranteed. If you want the guarantee, you need to define the order you want. You do this by changing the ORDER BY clause to use multiple columns.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources