ID times fdate ndate tdate etype
100 1 2020-01-01 2020-12-31
101 2 2019-01-01 2019-12-31 2019-01-01
103 3 2018-01-01 2018-12-31 2018-01-01 long
.

How to implement the following the results
Id times fdate ndate etype
100 1 2020-01-01 2020-12-31

101 1 2019-01-01 2019-12-31
101 2 2019-12-31 2020-12-31

103 1 2018-01-01 2018-12-31
103 2 2018-12-31 2019-12-31
103 3 2019-12-31 long
.

CodePudding user response:

; With TB (ID, times, fdate ndate, tdate, etype) as
(
Select 100, 1, '2020-01-01', '2020-12-31', null, null union all
Select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
Select 103, 3, '2018-01-01', '2018-12-31', '2019-12-31', 'long'
)
- select * from TB
, tb2 as (select ID, times, fdate ndate, tdate, etype from TB)
Tb3 as (select ID, times, fdate ndate, tdate, etype from TB)
The select tb3. ID, tb2. Times, tb2. Fdate, tb2. Ndate, tb2. Tdate, tb2. Etype from tb2 cross join tb3
Where tb2. Times & lt;=tb3. Times
The order by tb3. ID, tb3. Times

CodePudding user response:

reference 1st floor shoppo0505 response:

; With TB (ID, times, fdate ndate, tdate, etype) as
(
Select 100, 1, '2020-01-01', '2020-12-31', null, null union all
Select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
Select 103, 3, '2018-01-01', '2018-12-31', '2019-12-31', 'long'
)
- select * from TB
, tb2 as (select ID, times, fdate ndate, tdate, etype from TB)
Tb3 as (select ID, times, fdate ndate, tdate, etype from TB)
The select tb3. ID, tb2. Times, tb2. Fdate, tb2. Ndate, tb2. Tdate, tb2. Etype from tb2 cross join tb3
Where tb2. Times & lt;=tb3. Times
The order by tb3. ID, tb3. Times

The results of The Times and fdate and ndate don't match.

CodePudding user response:

refer to the second floor weixin_42074072 response:

Quote: refer to 1st floor shoppo0505 response:

; With TB (ID, times, fdate ndate, tdate, etype) as
(
Select 100, 1, '2020-01-01', '2020-12-31', null, null union all
Select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
Select 103, 3, '2018-01-01', '2018-12-31', '2019-12-31', 'long'
)
- select * from TB
, tb2 as (select ID, times, fdate ndate, tdate, etype from TB)
Tb3 as (select ID, times, fdate ndate, tdate, etype from TB)
The select tb3. ID, tb2. Times, tb2. Fdate, tb2. Ndate, tb2. Tdate, tb2. Etype from tb2 cross join tb3
Where tb2. Times & lt;=tb3. Times
The order by tb3. ID, tb3. Times

The results of The Times and fdate and ndate don't match.

How did you break up? Look not to understand

CodePudding user response:

reference shoppo0505 reply: 3/f

Quote: refer to the second floor weixin_42074072 response:

Quote: refer to 1st floor shoppo0505 response:

; With TB (ID, times, fdate ndate, tdate, etype) as
(
Select 100, 1, '2020-01-01', '2020-12-31', null, null union all
Select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
Select 103, 3, '2018-01-01', '2018-12-31', '2019-12-31', 'long'
)
- select * from TB
, tb2 as (select ID, times, fdate ndate, tdate, etype from TB)
Tb3 as (select ID, times, fdate ndate, tdate, etype from TB)
The select tb3. ID, tb2. Times, tb2. Fdate, tb2. Ndate, tb2. Tdate, tb2. Etype from tb2 cross join tb3
Where tb2. Times & lt;=tb3. Times
The order by tb3. ID, tb3. Times

The results of The Times and fdate and ndate don't match.

How did you break up? Can't read the

D times fdate ndate etype
100 1 2020-01-01 2020-12-31
nullnullnullnullnullnull