SQL Query to update the date range

Posted by Santosh4u under Sql Server on 5/11/2015 | Points: 10 | Views : 510 | Status : [Member] | Replies : 1
Hi
i am trying to find the output for the below but couldnt do ..some one please provide the solution

i am passing startdate and enddate , and for the startdate if any record is there then that record will move to next date of this range.keep on updating startdate and enddate until its not falling in any range.

in sample i would say duplicate record should not be there in any range.

Type Start Date End Date
1 Test1 03-05-2015 07-05-2015
2 Test1 12-05-2015 15-05-2015
3 Test1 17-05-2015 22-05-2015
4 Test1 23-05-2015 28-05-2015
5 Test1 05-06-2015 09-06-2015
6 Test2 05-05-2015 10-05-2015

INPUT
while Test2 is updating as test1 then output should be like below.
here startdate(05-05-2015) is falling between(03-05-2015-07-05-2015) of Sr 1 so this start date is changed to next of end date(07-05-201 +1 and 07-05-2015+1+(10-05-2015-05-05-2015)) .
OUTPUT:
1 Test1 03-05-2015 07-05-2015
6 Test1 08-05-2015 13-05-2015 Test2 will update as Test1 and startdate and end date
2 Test1 14-05-2015 17-05-2015
3 Test1 18-05-2015 23-05-2015
4 Test1 24-05-2015 29-05-2015
5 Test1 05-06-2015 10-06-2015 no change 29-05-2015 is not between this range

if two record ranges are falling then 2 should move accordingly.
i'e if INPUT is (05-05-2015 to 26-05-2015)
this 2 records are falling in range
1 Test1 03-05-2015 07-05-2015
2 Test1 12-05-2015 15-05-2015

Please refer the attachment for more details

Thanks
Santosh




Responses

Posted by: Santosh4u on: 5/12/2015 [Member] Bronze | Points: 25

Up
0
Down
Please check the attachment.it will help you to understand the requirement.
using courser this can be achieved but i don't find the logic for this.


 Download source file

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response