help with query
I need a query that would cumulatively "count up" the hours for each month.
Data like this:
id client_id visit_date visit_hours
1 10 1/1/2011 14:00 2.0
1 10 1/21/2011 8:00 5.0
1 10 1/31/2011 9:00 1.0
1 10 2/1/2011 7:00 2.0
1 10 2/25/2011 9:00 3.5
The resulting data would need to be:
id client_id visit_date visit_hours cumulative_hours
1 10 1/1/2011 14:00 2.0 2.0
1 10 1/21/2011 8:00 5.0 7.0
1 10 1/31/2011 9:00 1.0 8.0
1 10 2/1/2011 7:00 2.0 2.0
1 10 2/25/2011 9:00 3.5 5.5
Data like this:
id client_id visit_date visit_hours
1 10 1/1/2011 14:00 2.0
1 10 1/21/2011 8:00 5.0
1 10 1/31/2011 9:00 1.0
1 10 2/1/2011 7:00 2.0
1 10 2/25/2011 9:00 3.5
The resulting data would need to be:
id client_id visit_date visit_hours cumulative_hours
1 10 1/1/2011 14:00 2.0 2.0
1 10 1/21/2011 8:00 5.0 7.0
1 10 1/31/2011 9:00 1.0 8.0
1 10 2/1/2011 7:00 2.0 2.0
1 10 2/25/2011 9:00 3.5 5.5
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That gives me NULL for cumulative hours.
ASKER
That worked. Ignore my previous comment - my mistake.
Thanks!
Thanks!
(SELECT SUM(t2.visit_hours)
FROM SomeTable t2
WHERE t2.id = t1.id AND t1.client_id = t2.client_id AND YEAR(t2.visit_date) = YEAR(t1.visit_date)
AND MONTH(t2.visit_date) = MONTH(t1.visit_date)) AS cumulative_hours
FROM SomeTable t1
ORDER BY t1.id, t1.client_id, t1.visit_date