CODEWITHSUNDEEP
sqlsql-servert-sqldateunion
Dom
Dom

Reputation: 130

SQL Union as Subquery to create Date Ranges from Start Date

I have three tabels, each of them has a date column (the date column is an INT field and needs to stay that way). I need a UNION accross all three tables so that I get the list of unique dates in accending order like this:

20040602  
20051215  
20060628  
20100224  
20100228  
20100422  
20100512  
20100615

Then I need to add a column to the result of the query where I subtract one from each date and place it one row above as the end date. Basically I need to generate the end date from the start date somehow and this is what I got so far (not working):

With Query1 As (
    Select date_one As StartDate
    From table_one
     Union
     Select date_two As StartDate
     From table_two
     Union
     Select date_three e As StartDate
     From table_three
     Order By Date Asc
     )
Select Query1.StartDate - 1 As EndDate
From Query1

Thanks a lot for your help!

Upvotes: 1

Views: 1381

Answers (1)

GMB
GMB

Reputation: 222512

Building on your existing union cte, we can use lead() in the outer query to get the start_date of the next record, and withdraw 1 from it.

with q as (
    select date_one start_date  from table_one
    union select date_two from table_two
    union select date_three from table_three
)
select 
    start_date,
    dateadd(day, -1, lead(start_date) over(order by start_date)) end_date
from q
order by start_date

If the datatype the original columns are numeric, then you need to do some casting before applying date functions:

with q as (
    select cast(cast(date_one as varchar(8)) as date) start_date  from table_one
    union select cast(cast(date_two as varchar(8)) as date) from table_two
    union select cast(cast(date_three as varchar(8)) as date) from table_three
)
select 
    start_date,
    dateadd(day, -1, lead(start_date) over(order by start_date)) end_date
from q
order by start_date

Upvotes: 2

Related Questions