Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
micnie_2020
Posting Yak Master
232 Posts |
Posted - 2013-06-25 : 05:06:22
|
Hi All,Create table #testLeave(User varchar(200),DateJoin Datetime null,[Start_Date] Datetime null,End_Date Datetime null,GivenDay int null,rulesincrementyr int null,increday int null,maxday int null)insert into #testLeave ('Ali','15/Mar/2007','01/Apr/2005','31/Mar/2006','10','1','1','5')How can i write an stored procedure to exceute the output as above?Please advise.Thank you.Regards,Micheale |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-25 : 05:19:37
|
[code];With CTEAS(SELECT Name,DATEADD(yy,1,Start_Date) AS Start_Date,GivenDay AS LeaveEntitlement,IncrementDay,MaxIncrement,IncrementDay AS CounterFROM #testLeaveUNION ALLSELECT Name,DATEADD(yy,1,Start_Date)+1,LeaveEntitlement + CASE WHEN Counter + IncrementDay < = MaxIncrement THEN IncrementDay else 0 END,IncrementDay,MaxIncrement,Counter + IncrementDayFROM CTEWHERE DATEADD(yy,1,Start_Date) <= '20150101')SELECT *FROM CTEOPTION (MAXRECURSION 0)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|