Sql-server – Joining tables with all rows for each unique column value

join;sql server

I have 2 tables as follows:

    | A        |
    +----------+
    | ty_date  |
    | ly_date  |
    +----------+

and

    | B           |
    +-------------+
    | locationid  |
    | ty_date     |
    | value       |
    +-------------+

Let's say Table A has ty_date from 1st January till 31st December while Table B has ty_date from 1st January till, say, 1st August for each locationid.

I am trying to join the table in such a way that in Table B each locationid has all the dates from Table A with the field value having null values for all future dates.

I tried FULL OUTER JOIN ON A.ty_date = B.ty_date but I am still missing the grouping logic for each locationid

Any help on what approach I can try?

Best Answer

You can use a recursive CTE to produce the complete list of days for each locationid then join it back to table_b to get your values

Setup the tables:

CREATE TABLE dbo.table_a (
    ty_date DATE NULL,
    ly_date DATE NULL
);

CREATE TABLE dbo.table_b (
    locationid INT NULL,
    ty_date DATE NULL,
    value INT NULL
);

Add 365 entries to table_a, one for each day:

;WITH Dates AS (
    SELECT 1 AS [RowID], CAST('2019-01-01' AS DATETIME) AS ty_date
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM Dates r1
    WHERE r1.RowID <= 364
)

INSERT INTO dbo.table_a(ty_date)
SELECT ty_date
FROM Dates
OPTION (MAXRECURSION 400)

Add entries in table_b for 3 unique locationid's. One entry for each day until Aug:

;WITH Dates AS (
    SELECT 1 AS [RowID], CAST('2019-01-01' AS DATETIME) AS ty_date
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM Dates r1
    WHERE r1.RowID <= 242
)

INSERT INTO dbo.table_b(locationid, ty_date, value)
SELECT 1,
    ty_date, 
    RowID
FROM Dates
UNION
SELECT 2,
    ty_date, 
    RowID
FROM Dates
UNION
SELECT 3,
    ty_date, 
    RowID
FROM Dates
OPTION (MAXRECURSION 400)

Use the below query to return the full 365 days, one row per day per locationid. The Value column is only filled for values that exist in table_b, otherwise they are NULL

DECLARE @MinDate DATE,
    @MaxDate DATE

SELECT @MinDate = MIN(ty_date), @MaxDate = DATEADD(DAY, -1, MAX(ty_date)) FROM table_a

;WITH AllData AS (
    SELECT DISTINCT 1 AS RowID, locationid, @MinDate AS ty_date
    FROM table_b
    UNION ALL
    SELECT r1.RowID + 1 AS RowID, r1.locationid, DATEADD(DAY, 1, r1.ty_date) AS ty_date
    FROM AllData r1
    WHERE r1.ty_date BETWEEN @MinDate AND @MaxDate
)

SELECT ad.ty_date, ad.locationid, b.value
FROM AllData ad
LEFT JOIN table_b b ON b.locationid = ad.locationid AND b.ty_date = ad.ty_date
ORDER BY ad.ty_date, ad.locationid
OPTION (MAXRECURSION 1100)

Note that MAXRECURSION must be equal to or higher than 365 * the number of distinct locationid values in table_b minus 1

The results:

ty_date     locationid  value
-----------------------------
2019-01-01  1           1
2019-01-01  2           1
2019-01-01  3           1
2019-01-02  1           2
2019-01-02  2           2
2019-01-02  3           2
2019-01-03  1           3
2019-01-03  2           3
2019-01-03  3           3
2019-01-04  1           4
2019-01-04  2           4
2019-01-04  3           4
.....
2019-08-31  1           243
2019-08-31  2           243
2019-08-31  3           243
2019-09-01  1           NULL
2019-09-01  2           NULL
2019-09-01  3           NULL
2019-09-02  1           NULL
2019-09-02  2           NULL
2019-09-02  3           NULL
2019-09-03  1           NULL
2019-09-03  2           NULL
2019-09-03  3           NULL
.....
2019-12-28  1           NULL
2019-12-28  2           NULL
2019-12-28  3           NULL
2019-12-29  1           NULL
2019-12-29  2           NULL
2019-12-29  3           NULL
2019-12-30  1           NULL
2019-12-30  2           NULL
2019-12-30  3           NULL
2019-12-31  1           NULL
2019-12-31  2           NULL
2019-12-31  3           NULL

See here for a db<>fiddle demonstrating this.

Related Solutions

Mysql – How to simulate a table join on itself

You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table:

select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;

See SQL Fiddle with Demo. This will create a list of data similar to:

| PRODUCT_NUMBER |                            DATE |
|----------------|---------------------------------|
|            100 |  January, 01 2010 00:00:00+0000 |
|            200 |  January, 01 2010 00:00:00+0000 |
|            100 | February, 01 2010 00:00:00+0000 |

You will then use the above query and LEFT JOIN to your table to return the final result:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join yourtable d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Giving a final result of:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |

The LEFT JOIN returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.

This could also be written as:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
( 
  -- list of every date
  select distinct date
  from yourtable 
) d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. This may have better performance depending on your table size.

Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates. This table would be used in a similar manner to create a list of all dates/products which you would then join.

The table would be similar to:

CREATE TABLE dates
    (`date` datetime)
;

INSERT INTO dates
    (`date`)
VALUES
    ('2010-01-01 00:00:00'),
    ('2010-02-01 00:00:00'),
    ('2010-03-01 00:00:00'),
    ('2010-04-01 00:00:00'),
    ('2010-05-01 00:00:00')
;

You'd then use the following query to get the list of dates/products:

select distinct p.product_number, d.date
from yourtable p
cross join dates d

And finally, you would join that back to your table:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join dates d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Or an alternative:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
 dates
  d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. Again this may have better performance based on the table size. Using this type of solution, you'd return all dates even those not in your table:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            100 |    April, 01 2010 00:00:00+0000 | (null) |
|            100 |      May, 01 2010 00:00:00+0000 | (null) |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |    April, 01 2010 00:00:00+0000 | (null) |
|            200 |      May, 01 2010 00:00:00+0000 | (null) |

Sql-server – Effective dates, complex requirements

You can simplify the first and second query with TOP / ORDER BY:

SELECT TOP (1) color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date < '2015-01-01'
ORDER BY eff_date DESC ;

It won't however return anything for Nina. You could use another subquery and then combine them with either UNION ALL and one more TOP or with COALESCE() (or ISNULL() as in your query):

SELECT color = COALESCE(
    ( SELECT TOP (1) color
        FROM fav_colors
       WHERE name='Luis'
         AND eff_date < '2015-01-01'
      ORDER BY eff_date DESC
    ),
    ( SELECT TOP (1) color
        FROM fav_colors
       WHERE name='Luis'
         AND eff_date >= '2015-01-01'
      ORDER BY eff_date ASC
    ) ) ;

If you want to do this check for more than one (or all of the) persons on the table, you can use OUTER APPLY, as @RobFarley's explains in his answer.)

Best Answer

Related Solutions

Mysql – How to simulate a table join on itself

Sql-server – Effective dates, complex requirements

Related Question