| Author |
 Topic  |
|
|
TazMania
Yak Posting Veteran
Denmark
63 Posts |
 Posted - 04/22/2009 : 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
USA
933 Posts |
Posted - 04/22/2009 : 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 |
Edited by - nathans on 04/22/2009 14:15:24 |
 |
|
|
TazMania
Yak Posting Veteran
Denmark
63 Posts |
Posted - 04/22/2009 : 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 |
Edited by - TazMania on 04/22/2009 14:45:23 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/22/2009 : 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
United Kingdom
12543 Posts |
Posted - 04/22/2009 : 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
Denmark
63 Posts |
Posted - 04/22/2009 : 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 |
|
|
| |
Topic |
|
|
|
A very strange result.
