help with query

HLRosenberger
HLRosenberger used Ask the Experts™
on
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                            
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
SELECT t1.id, t1.client_id, t1.visit_date, t1.visit_hours,
    (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
Top Expert 2010
Commented:
Sorry, needs another condition:


SELECT t1.id, t1.client_id, t1.visit_date, t1.visit_hours,
    (SELECT SUM(t2.visit_hours)
    FROM SomeTable t2
    WHERE t2.id = t1.id AND t1.client_id = t2.client_id AND t2.visit_date <= t1.visit_date 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 

Open in new window

Author

Commented:
That gives me NULL for cumulative hours.

Author

Commented:
That worked.  Ignore my previous comment  - my mistake.

Thanks!
Top Expert 2010

Commented:
:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial