CODEWITHSUNDEEP
sql-server
FJerezano
FJerezano

Reputation: 5

SQL Query - Given two dates, repeat the records of a table

I need to make a query

I have two tables, TableA and TableB

TableA is as following:

Id Name
1 Car
2 Plane
3 Ship
4 Bike
5 Skate

And my TableB is like the following:

ID MyDate FID
1 29-08-2021 1
2 29-08-2021 2
3 29-08-2021 3
4 30-08-2021 1
5 30-08-2021 5

FID: is the column that relates TableB to TableA.

So, I need to show every record of TableA, with the records of TableB.

I know how to do it with a single Date. Some Like this:

SELECT ID, NAME, MyDATE
FROM TableA
LEFT JOIN TableB ON FID = ID AND MyDate = SomeDate
ORDER BY ID

If SomeDate = '29-08-2021'

That query should return the following result:

ID Name MyDate
1 Car 29-08-2021
2 Plane 29-08-2021
3 Ship 29-08-2021
4 Bicicle NULL
5 Skate NULL

If SomeDate = '30-08-2021', It should show the following

ID Name MyDate
1 Car 30-08-2021
2 Plane NULL
3 Ship NULL
4 Bicicle NULL
5 Skate 30-08-2021

So, What I need is to give two dates, and print Everything in that range of dates, all the records of TableA with their corresponding records of TableB

So if I give the Dates 28-08-2021 to 30-08-2021, the result should be like the following

ID Name MyDate
1 Car NULL
2 Plane NULL
3 Ship NULL
4 Bicicle NULL
5 Skate NULL
1 Car 29-08-2021
2 Plane 29-08-2021
3 Ship 29-08-2021
4 Bicicle NULL
5 Skate NULL
1 Car 30-08-2021
2 Plane NULL
3 Ship NULL
4 Bicicle NULL
5 Skate 30-08-2021

I need to make a query that returns this result set.

The first 5 records, are the records corresponding to 28-09-2021, because there are no recoreds in TableB with that date, it returns null.

Because there are three dates, it should repeat all the records of table A three times.

I tried to do this

SELECT ID, NAME, MyDATE
FROM TableA
LEFT JOIN TableB ON FID = ID AND MyDate BETWEEN StartDate AND EndDate
ORDER BY ID

But doing that only returns 5 records (the first 3 with the date 29-08-2021, and the second 2 with the date 30-08-201).

I need to know how to do a query that returns result set I provided.

Upvotes: 0

Views: 821

Answers (1)

Scratte
Scratte

Reputation: 3166

What you seem to want to do is get the cartesian product of TableA with all the dates in the date range. And then add information on each row as to if there is a corresponding row in your TableB.

You can start with a simple recursive SQL, that gets your dates:

WITH cte_dates(theDates) 
AS (
    SELECT @startdate
   UNION ALL
    SELECT DATEADD(day, 1, thedates)
      FROM cte_dates
     WHERE thedates < @enddate
)
SELECT *
 FROM cte_dates;

This will result in:

thedates
2021-08-28
2021-08-29
2021-08-30

The variable declarations are:

DECLARE @startdate DATE;
DECLARE @enddate DATE;

SET @startdate = '2021-08-28';
SET @enddate = '2021-08-30';

Then join that with your TableA using WHERE 1 = 1 to get the cartesian product:

WITH cte_dates(thedates) 
AS (
    SELECT @startdate
   UNION ALL
    SELECT DATEADD(day, 1, thedates)
      FROM cte_dates
     WHERE thedates < @enddate
)
SELECT *
 FROM TableA
 JOIN cte_dates ON (1 = 1)
;

This will give you 15 rows where each row in TableA has an extra column with the date. Each original row in TableA will result in 3 rows in this result set, since there are 3 dates.

Now you can use a outer left join to get the corresponding TableB values:

WITH cte_dates(thedates) 
AS (
    SELECT @startdate
   UNION ALL
    SELECT DATEADD(day, 1, thedates)
      FROM cte_dates
     WHERE thedates < @enddate
)
SELECT ta.id, ta.name, tb.mydate
 FROM TableA      AS ta
 JOIN cte_dates   AS dat ON (1 = 1)
 LEFT JOIN TableB AS tb  ON tb.fid = ta.id
                        AND tb.mydate = dat.thedates
;

dbfiddle

Upvotes: 1

Related Questions