February 1, 2008 at 9:24 am
I have a query that returns a few rows, well I'd also like to join in a sub query that sums some stuff between a date range. My first attempt works, but I believe its pretty wastefull.
select d1,d2,d3,d4,d5,d6,d7
, t.active_code_id
, su.diff
from timecards t
join active_codes ac on t.active_code_id = ac.id
join
(
select ti.active_code_id, sum(d1+d2+d3+d4+d5+d6+d7) 'diff'
from timecards ti
join active_codes a on a.id = ti.active_code_id
where pay_date <> a.used_date
and pay_date between a.used_date and '2/03/2008'
group by ti.active_code_id
) su on su.active_code_id = t.active_code_id
where t.saved_by = 2598 AND
t.pay_date = '02/03/08'
I think that the server sums up all rows in the subquery, throws out all the ones that don't match from the ON clause and then repeats for the next row. what I'd like to do is somehow send to the subquery the active_code_id that I'm planning on summing. Don't know if its possible, but it would reduce the load on the server a bit.
February 1, 2008 at 2:02 pm
DECLARE@Sample TABLE (PkCol INT, Col1 INT, Col2 INT, Col3 INT)
INSERT@Sample
SELECT1, 1, 2, 3 UNION ALL
SELECT2, 3, 5, 7
SELECTPkCol,
Col1,
Col2,
Col3,
SUM(Col1 + Col2 + Col3) AS SumOfAllCol
FROM@Sample
GROUP BYPkCol,
Col1,
Col2,
Col3
WITHROLLUP
HAVINGGROUPING(Col1) = 0
AND GROUPING(Col2) = 0
AND GROUPING(Col3) = 0
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply