Recursive Query Issues in SQL Server
I have a problem with recursive query in SQL Server.
Suppose I have 2 tables:
-
Holiday
: this table stores all the holidays (holidayDate
that don't work) -
Invoice
: this table stores the next payment date (nextPaymentDate
)
If nextPaymentDate
is in a table holidayDate
in a holiday table, I need to update it:
nextPaymentDate = nextPaymentDate + 1 day
This step should be processed until it is nextPaymentDate
no longer on holidayDate
.
See sample data examples below:
Holiday
table:
HolidyaID HolidayDate
-----------------------------
1 2012-01-02
2 2012-01-03
3 2012-01-04
4 2012-01-08
5 2012-01-12
6 2012-01-13
7 2012-01-20
8 2012-01-21
9 2012-01-22
10 2012-01-23
11 2012-01-29
12 2012-01-30
Invoice
table
InvoiceID NextPaymentDate
------------------------------
1 2012-01-01
2 2012-01-02
3 2012-01-09
4 2012-01-20
After running this query, I want to see the data in the table Invoice
as shown
InvoiceID NextPaymentDate
-------------------------------
1 2012-01-01
2 2012-01-05
3 2012-01-09
4 2012-01-24
How can I create a SQL query to output this result?
You can check this request at http://sqlfiddle.com/#!6/de346/3
Thank!
source to share
select InvoiceID,
Case when [holidaydate] is null then [NextPaymnetDate]
else
DateAdd(dd,1,
(Select min([holidaydate]) from holiday h
where h.[holidaydate]>[NextPaymnetDate]
and not exists(Select * from holiday h2 where h2.[holidaydate]=DateAdd(dd,1,h.[holidaydate]))
))
end
from invoice
left Join holiday on [holidaydate]=[NextPaymnetDate]
source to share
with holidayBatch as
(
select workingdate = dateadd(dd, 1, holidayDate)
from Holiday h
where not exists (select 1 from Holiday nxt
where nxt.holidaydate = dateadd(dd, 1, h.holidaydate))
)
select i.invoiceid,
case when h.holidaydate is null then i.NextPaymentDate else hb.workingdate end
from Invoice i
left join Holiday h on i.NextPaymentDate = h.holidaydate
outer apply (select top 1 workingdate
from holidayBatch hb where workingdate > NextPaymentDate
order by workingdate) hb
order by i.invoiceid
source to share
select invoiceid, coalesce(nd,NextPaymnetDate) dd from invoice i left join
(
select *,(select min(dateADD(dd,1,holidaydate)) from holiday
where holidaydate>=h.holidaydate and
dateADD(dd,1,holidaydate) not in (select holidaydate from holiday)) nd
from holiday h) h on NextPaymnetDate = holidaydate;
source to share