lets have table
date,personid 1/1/2001 1 1/2/2001 3 1/3/2001 2 1/4/2001 2 1/5/2001 5 1/6/2001 5 1/7/2001 6 and i'm going either update 1/2/2001 or 1/5/2001 personid 2 before can update have make sure passes rule says can't have person 3 days in row. how can solve in mssql stored procedure?
update: need solve layout i'd update 1/5/2001
date,personid 1/1/2001 1 1/2/2001 3 1/3/2001 2 1/4/2001 2 1/5/2001 1 1/6/2001 2 1/7/2001 2 1/8/2001 5 1/9/2001 5 1/10/2001 6
i've assumed date unique let me know if not case!
declare @basedata table ([date] unique date,personid int) insert @basedata select getdate()+1, 2 union select getdate()+2, 3 union select getdate()+3, 2 union select getdate()+4, 2 union select getdate()+5, 5 union select getdate()+6, 5 union select getdate()+7, 6 declare @date date = getdate()+5 declare @personid int = 2 ;with t ( select top 2 [date],personid @basedata [date] < @date order [date] desc union select @date, @personid union select top 2 [date],personid @basedata [date] > @date order [date] ),t2 ( select *, row_number() on (order [date]) - row_number() on (partition personid order [date]) grp t ) select count(*) /*will return result if date/personid cause sequence of 3*/ t2 group personid,grp having count(*) >=3 
Comments
Post a Comment