SQL: Find difference between dates with grouping
- by ajbeaven
I have a problem that seems similar to this fellow - I just want to display the data slightly differently. I'm pretty terrible with SQL so can't modify it to suit, but perhaps someone else can.
My table looks similar to this (date format is dd/mm/yyyy):
ID User Date_start Role
1 Andy 01/04/2010 A
2 Andy 10/04/2010 B
3 Andy 20/04/2010 A
4 John 02/05/2010 A
I want to show the total number of days that anyone was in a certain role. Users stay in the role until there is another entry into the table. Users can only be in one role at a time. So the summary data would look like this (assuming that the date is 04/05/2010):
A: 26 days
B: 10 days
Thanks for any help :)
