sql - Count of distinct values per day, excluding reoccuring until value changes -

sql - Count of distinct values per day, excluding reoccuring until value changes -

i'm struggling how explain i'll seek , give format of table below, , desired outcome.

i have table contains uniqueid, date, userid , result. i'm trying count number of results 'correct' per day, want count unique occurances based on userid column. want exclude furhter occurances of 'correct' particular userid, until result userid changes 'success'.

uid date userid result 1 01/01/2014 5 right 2 01/01/2014 5 right 3 02/01/2014 4 right 4 03/01/2014 4 right 5 03/01/2014 5 wrong 6 03/01/2014 4 wrong 7 05/01/2014 5 right 8 07/01/2014 4 right 9 08/01/2014 5 success 10 08/01/2014 4 success

based on above data, i'd expect see below:

date right success 01/01/2014 1 0 02/01/2014 1 0 03/01/2014 0 0 05/01/2014 0 0 07/01/2014 0 0 08/01/2014 0 2

can help? i'm using sql server 2008

use count(distinct) case:

select date, count(distinct case when result = 'correct' userid end) correct, count(distinct case when result = 'success' userid end) success info d grouping date order date;

edit:

the above counts correct on occurrences. if want first 1 counted:

select date, count(case when result = 'correct' , seqnum = 1 userid end) correct, count(case when result = 'success' , seqnum = 1 userid end) success (select d.*, row_number() on (partition userid, result order uid) seqnum info d ) d;

in case, distinct unnecessary.

sql sql-server sql-server-2008 distinct