sql server - SQL Query X Days back excluding date ranges (Confusing!) -

sql server - SQL Query X Days back excluding date ranges (Confusing!) -

ok, have tough sql query, , i'm not sure how go writing it.

i summing number of "bananas collected" employee within lastly x days, utilize help on determining x.

the "last x days" value defined lastly 100 days employee not out due violet fever, starting chosendate (we'll today, 6/24/14). say, if person sick violet fever 3 days, want on lastly 103 days chosendate rather lastly 100 days. other reason employee may have been out not impact our calculation.

table personoutincident +----------------------+----------+-------------+ | personoutincidentid | personid | reasonout | +----------------------+----------+-------------+ | 1 | sarah | purplefever | | 2 | sarah | papercut | | 3 | jon | purplefever | | 4 | sarah | purplefever | +----------------------+----------+-------------+ table personoutdetail +-------------------+----------------------+-----------+-----------+ | personoutdetailid | personoutincidentid | begindate | enddate | +-------------------+----------------------+-----------+-----------+ | 1 | 1 | 1/1/2014 | 1/3/2014 | | 2 | 1 | 1/7/2014 | 1/13/2014 | | 3 | 2 | 2/1/2014 | 2/3/2014 | | 4 | 3 | 1/15/2014 | 1/20/2014 | | 5 | 4 | 5/1/2014 | 5/15/2014 | +-------------------+----------------------+-----------+-----------+

the tables established. many personoutdetail records can associated 1 personoutincident record , there may multiple personoutincident records single employee (that say, there 2 or 3 personoutincident records identical reasonout column, because represent particular incident or event , not-necessarily-continuous days lost due particular incident)

the nature of requirement complicates things, conceptually me.

the best can think of check begindate/enddate pair within 100 day base of operations period, determine number of days begindate enddate , add together base of operations 100 days. have check 1 time again new range doesn't overlap or contain additional begindate/enddate pairs , add, if so, add together days well. can tell isn't method want use, can't wrap mind quite around how need start/structure query. have thought might steer me in right direction? realize might not clear , apologize if i'm confusing things.

one way work table or with clause contains list of days. let's days table 1 column contains lastly 200 days. (this means query break if employee had more 100 sick days in lastly 200 days).

now can list of working days of employee (replace ? employee id):

with t1 ( select day, row_number() on (order day desc) 'rownumber' days d not exists (select * personoutdetail pd inner bring together personoutincidentid po on po.personoutincidentid = pd.personoutincidentid d.day between pd.begindate , pd.enddate , po.reasonout = 'purplefever' , po.personid = ?) ) select * t1 rownumber <= 100;

alternatively, can obtain '100th day' replacing rownumber <= 100 rownumber = 100.

sql sql-server