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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.
 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
30421 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  
 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