Write T-SQL script to return required result

Question

I have difficulties to write a T-SQL script to return required result.

Here is my data:

And the required result is like this:

Here is my Oracle script for this result:

CREATE TABLE t 
(
    contract_no  VARCHAR2(100),
    begin_date   DATE,
    end_date     DATE
);

INSERT INTO t VALUES ('AAA',to_date('22-12-2020','DD-MM-YYYY'),to_date('13-05-2021','DD-MM-YYYY'));
INSERT INTO t VALUES ('BBB',to_date('20-03-2019','DD-MM-YYYY'),to_date('01-06-2019','DD-MM-YYYY'));

SELECT *
FROM t;

select contract_no
       , begin_date
       , end_date
       , l
       , last_day(add_months(trunc(begin_date,'MONTH'),l-1))
from t, lateral (select level l 
                 from dual 
                 connect by level <= round(months_between(last_day(end_date),trunc(begin_date,'MONTH')))-1);

I need the equivalent script in T-SQL.

Thanks in advance

Your query is far from [*minimal* reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) and too complex, so the question is unclear. What actually is the problem you cannot resolve? — astentx, Jul 12 '21 at 20:36

score 0 · Accepted Answer · answered Jul 12 '21 at 15:47

I would use a recursive CTE in both databases. In SQL Server, this looks like:

with cte as (
      select contract_no, begin_date, end_date, eomonth(begin_date) as date
      from t
      union all
      select contract_no, begin_date, end_date, eomonth(date, 1) as date
      from cte
      where date < eomonth(end_date, -1)
     )
select *
from cte;

The syntax for a recursive CTE is slightly different in Oracle.

Here is a db<>fiddle.

Write T-SQL script to return required result

1 Answers1