A very strange result. - 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)

A very strange result.

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-04-22 : 14:05:22

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

938 Posts

Posted - 2009-04-22 : 14:14:44

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

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-04-22 : 14:31:14

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 14:31:42

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"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 14:42:13

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.

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-04-22 : 16:35:20

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources