SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 A very strange result.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TazMania
Yak Posting Veteran

Denmark
63 Posts

Posted - 04/22/2009 :  14:05:22  Show Profile  Reply with Quote
Hi,

I am experiencing a very strange query result.
The below code show a query, i previously got some help with in here :)


SELECT time_projektNummer,
sum((time_tegneTimer * 60) + time_tegneMin) / 60 as [Sum Of time_tegneTimer],
sum((time_tegneTimer * 60) + time_tegneMin) % 60 as [Sum Of time_tegneMin],
sum((time_tegneIngTimer * 60) + time_tegneMin) / 60 as [Sum Of time_tegneIngTimer],
sum((time_tegneIngTimer * 60) + time_tegneIngMin) % 60 as [Sum Of time_tegneIngMin] 
FROM dcasTimesheet LEFT OUTER JOIN dcasMembers ON dcasTimeSheet.time_ParentMemID=dcasMembers.M_ID 
WHERE time_CreateDate BETWEEN '04-01-2009' AND '04-20-2009' AND dcasMembers.M_Navn='mrv'
GROUP BY time_projektNummer ORDER BY dcasTimesheet.time_ProjektNummer asc


When the above query executes the result looks like this :

(time_projektNummer, Sum Of time_tegneTimer, Sum Of time_tegneMin, Sum Of time_tegneIngTimer, Sum Of time_tegneIngMin)
('0', '6', '30', '0', '0'), 
('0906', '15', '30', '0', '0'), 
('0907', '10', '45', '1', '0'), 
('0908', '35', '15', '2', '0'), 
('0912', '12', '0', '1', '0')


But what it should look like is this:

(time_projektNummer, Sum Of time_tegneTimer, Sum Of time_tegneMin, Sum Of time_tegneIngTimer, Sum Of time_tegneIngMin)
('0', '6', '30', '0', '0'), 
('0906', '15', '30', '0', '0'), 
('0907', '10', '45', '0', '0'), 
('0908', '35', '15', '0', '0'), 
('0912', '12', '0', '0', '0')


I've double checked the data, and the user "mrv" has not entered any data for [sum of time_tegneIngTimer] and [sum of time_tegneIngMin]

But for some strange reason, the query shows values for [sum of time_tegneIngTimer] and [sum of time_tegneIngMin] for the particularly user, any of you who please can explain to me why that is ?

Best regards
Taz

nathans
Aged Yak Warrior

USA
938 Posts

Posted - 04/22/2009 :  14:14:44  Show Profile  Reply with Quote
You are LEFT joining to the member table, which means that you will return *all* rows from dcasTimesheet with NULLs in place for rows that do not match in dcasMembers. Since you are summing the result your where filter on M_Navn='mrv' does not exclude other members' totals from affecting the sum.

Try using a INNER join instead. This will exclude all other members (other than mrv). Post back with table creates and sample / desired data if this introduces other issues, which it probably will.



Nathan Skerl

Edited by - nathans on 04/22/2009 14:15:24
Go to Top of Page

TazMania
Yak Posting Veteran

Denmark
63 Posts

Posted - 04/22/2009 :  14:31:14  Show Profile  Reply with Quote
Hi Nathan,

Thanks for you quick reply.
But that didn't help.

This is what my query looks like :

SELECT time_projectNumber,
sum((time_tegneHours * 60) + time_tegneMin) / 60 as [Sum Of time_tegneHours],
sum((time_tegneHours * 60) + time_tegneMin) % 60 as [Sum Of time_tegneMin],
sum((time_tegneIngHours * 60) + time_tegneMin) / 60 as [Sum Of time_tegneIngHours],
sum((time_tegneIngHours * 60) + time_tegneIngMin) % 60 as [Sum Of time_tegneIngMin] 
FROM dcasTimesheet INNER JOIN dcasMembers ON dcasTimeSheet.time_ParentMemID=dcasMembers.M_ID 
WHERE time_CreateDate BETWEEN '04-01-2009' AND '04-20-2009' AND dcasMembers.M_name='mrv'
GROUP BY time_projektNumber ORDER BY dcasTimesheet.time_ProjektNumber asc


Which in the end gave me the same result.

I also just for the kick of it, tried to exclude the join once a for all.

so my query looked like this :

SELECT time_projectNumber,
sum((time_tegneHours * 60) + time_tegneMin) / 60 as [Sum Of time_tegneHours],
sum((time_tegneHours * 60) + time_tegneMin) % 60 as [Sum Of time_tegneMin],
sum((time_tegneIngHours * 60) + time_tegneMin) / 60 as [Sum Of time_tegneIngHours],
sum((time_tegneIngHours * 60) + time_tegneIngMin) % 60 as [Sum Of time_tegneIngMin] 
FROM dcasTimesheet 
WHERE time_CreateDate BETWEEN '04-01-2009' AND '04-20-2009' AND time_ParentMemID=1
GROUP BY time_projektNumber ORDER BY dcasTimesheet.time_ProjektNumber asc


But that ended up with the exact same result.

(time_projectNumber, Sum Of time_tegneHours, Sum Of time_tegneMin, Sum Of time_tegneIngHours, Sum Of time_tegneIngMin)
('0', '6', '30', '0', '0'), 
('0906', '15', '30', '0', '0'), 
('0907', '10', '45', '1', '0'), 
('0908', '35', '15', '2', '0'), 
('0912', '12', '0', '1', '0')


Best regards
Taz

Edited by - TazMania on 04/22/2009 14:45:23
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/22/2009 :  14:31:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Also prefix all your columns with correct table name.
We can't know which column belongs to which table otherwise.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/22/2009 :  14:42:13  Show Profile  Visit nr's Homepage  Reply with Quote
try
select *
from dcasTimesheet
WHERE time_CreateDate BETWEEN '04-01-2009' AND '04-20-2009' AND time_ParentMemID=1
and time_projektNummer = '0908'

select sum((time_tegneIngTimer * 60) + time_tegneMin) / 60
from dcasTimesheet
WHERE time_CreateDate BETWEEN '04-01-2009' AND '04-20-2009' AND time_ParentMemID=1
and time_projektNummer = '0908'

Should give you a clue as to what's happening.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

TazMania
Yak Posting Veteran

Denmark
63 Posts

Posted - 04/22/2009 :  16:35:20  Show Profile  Reply with Quote
Thanks nr.

Didn't even notice the small error of missing inserting Ing to sum clause.
Have been starring at that code for hours :)

The calculation was all wrong..

So now the code are as follows :

SELECT time_projektNummer,
sum((time_tegneTimer * 60) + time_tegneMin) / 60 as [Sum Of time_tegneTimer],
sum((time_tegneTimer * 60) + time_tegneMin) % 60 as [Sum Of time_tegneMin],
sum((time_tegneIngTimer * 60) + time_tegneIngMin) / 60 as [Sum Of time_tegneIngTimer],
sum((time_tegneIngTimer * 60) + time_tegneIngMin) % 60 as [Sum Of time_tegneIngMin] 
FROM dcasTimesheet LEFT OUTER JOIN dcasMembers ON dcasTimeSheet.time_ParentMemID=dcasMembers.M_ID 
WHERE time_CreateDate BETWEEN '04-01-2009' AND '04-20-2009' AND dcasMembers.M_Navn='mrv'
GROUP BY time_projektNummer ORDER BY dcasTimesheet.time_ProjektNummer asc

what I missed out on was this part :

sum((time_tegneIngTimer * 60) + time_tegneMin) / 60 as [Sum Of time_tegneIngTimer],

which should be :

sum((time_tegneIngTimer * 60) + time_tegneIngMin) / 60 as [Sum Of time_tegneIngTimer],


Thanks :)


Best regards
Taz
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000