jmich738 jmich738 - 1 year ago 57
SQL Question

SQL - How to cross-join two table to repeat values

I have a 2 tables that look like this:

MonthEndDate
2016-06-30 00:00:00.000
2016-07-31 00:00:00.000
2016-08-31 00:00:00.000
2016-09-30 00:00:00.000
2016-10-31 00:00:00.000
2016-11-30 00:00:00.000
2016-12-31 00:00:00.000


AND

MonthEndDate CustomerId Flag
2016-06-30 00:00:00.000 123 1
2016-07-31 00:00:00.000 123 1
2016-08-31 00:00:00.000 123 1
2016-09-30 00:00:00.000 123 1


I would like an output that looks like this:

MonthEndDate CustomerId Flag
2016-06-30 00:00:00.000 123 1
2016-07-31 00:00:00.000 123 1
2016-08-31 00:00:00.000 123 1
2016-09-30 00:00:00.000 123 1
2016-10-31 00:00:00.000 123 0
2016-11-30 00:00:00.000 123 0
2016-12-31 00:00:00.000 123 0


Table 1 is a
DimDate
table that has month end date.

Table




2 is the
CustomerInfo
table.

Each customer has a
Flag
set to
1
whenever that customer has a value for the given Month End.

I want to get an output that will have every Month End Date (that's why I'm suing
DimDate
table) and when a customer does not have a value for the Month End I want the flag to show 0.

I'm using SQL Server 2005

Here is some sample code I used:

DECLARE @table1 TABLE
(
MonthEndDate DATETIME
)

INSERT INTO @table1
VALUES('2016-06-30 00:00:00.000')

INSERT INTO @table1
VALUES('2016-07-31 00:00:00.000')
INSERT INTO @table1
VALUES('2016-08-31 00:00:00.000')
INSERT INTO @table1
VALUES('2016-09-30 00:00:00.000')
INSERT INTO @table1
VALUES('2016-10-31 00:00:00.000')
INSERT INTO @table1
VALUES('2016-11-30 00:00:00.000')
INSERT INTO @table1
VALUES('2016-12-31 00:00:00.000')

DECLARE @table2 TABLE
(
MonthEndDate DATETIME
,CustomerId INT
,Flag INT
)

INSERT INTO @table2
VALUES('2016-06-30 00:00:00.000',123,1)

INSERT INTO @table2
VALUES('2016-07-31 00:00:00.000',123,1)
INSERT INTO @table2
VALUES('2016-08-31 00:00:00.000',123,1)
INSERT INTO @table2
VALUES('2016-09-30 00:00:00.000',123,1)

SELECt * FROM @table1


SELECt * FROM @table2

Answer Source

You need to do a CROSS JOIN on to get all combinations of MonthEndDate and CustomerId. When you have that, do a LEFT JOIN on table2 to get the Flag:

SELECT
    t1.MonthEndDate,
    c.CustomerId,
    Flag = ISNULL(t2.Flag, 0)
FROM @table1 t1
CROSS JOIN (SELECT DISTINCT CustomerId FROM @table2) c
LEFT JOIN @table2 t2
    ON t1.MonthEndDate = t2.MonthEndDate
    AND c.CustomerId = t2.CustomerId
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download