sql - Count status for items with particular flags at different times -

sql - Count status for items with particular flags at different times -

i have items can flagged status time range. in each row, have start , stop times item flag.

for example:

start stop itemid flag 1-jan 1-feb 1 1-feb 1-mar 1 b 1-feb 1-mar 2 1-jan 1-mar 3 1-jan null 4

what need subcount of count. is, 2 dates given (say 1-jan , 1-mar), need count number of items had flag a @ first date not flag a @ sec date. (and other counts, other 3 combinations of a , not-a @ 2 dates.)

i need somehow business relationship no flag @ either date (but not both), can assume no flag b.

essentially, need count next cases: (a,a), (a,b), (a,null), (b,a), (b,b), (b,null); (x,y), x flag each item @ first date, , y flag @ sec date.

existing, can count how many flags a or b exist date.

select count(1) table start <= '2014-02-15' , (stop >= '2014-02-15' or stop null) , flag =

this should counts.

notice changed "stop >= '2014-02-15'" "stop > '2014-02-15'".

you need decide if "stop" date within interval or not. i'm assuming it's not since have "1-feb" on both start , stop item 1. otherwise item 1 in both state , b on 1-feb.

select sum(case when d1.flag='a' , d2.flag='a' 1 else 0 end) aa ,sum(case when d1.flag='a' , d2.flag='b' 1 else 0 end) ab ,sum(case when d1.flag='a' , d2.flag null 1 else 0 end) anull ,sum(case when d1.flag='b' , d2.flag='a' 1 else 0 end) ba ,sum(case when d1.flag='b' , d2.flag='b' 1 else 0 end) bb ,sum(case when d1.flag='b' , d2.flag null 1 else 0 end) bnull ( select * table start <= '2014-01-15' , (stop > '2014-01-15' or stop null) , flag = 'a' ) d1 left bring together ( select * table start <= '2014-02-15' , (stop > '2014-02-15' or stop null) , flag = 'a' ) d2 on d1.itemid = d2.itemid

sql sql-server tsql