Here is my code :
drop table if exists #Table_A
drop table if exists #Table_B
drop table if exists #Table_C
create table #Table_A
(
[DATE] Date,
TYPE INT,
QTY INT
);
create table #Table_B
(
[DATE] Date,
TYPE INT,
QTY INT
);
create table #Table_C
(
[DATE] Date,
TYPE INT,
QTY INT
);
Insert into #Table_A VALUES ('2018-01-01', 1, 10)
Insert into #Table_B VALUES ('2018-01-01', 1, 5)
Insert into #Table_B VALUES ('2018-01-01', 2, 10)
Insert into #Table_B VALUES ('2018-01-01', 3, 10)
Insert into #Table_C VALUES ('2018-01-01', 1, 5)
Insert into #Table_C VALUES ('2018-01-01', 2, 10)
Insert into #Table_C VALUES ('2018-01-01', 3, 10)
Query :
select
A.[DATE],
SUM(CASE WHEN A.[TYPE] = 1 THEN A.QTY END) A_QTY,
SUM(CASE WHEN B.[TYPE] = 1 THEN B.QTY END) B_QTY,
SUM(CASE WHEN C.[TYPE] = 1 THEN C.QTY END) C_QTY,
SUM(CASE WHEN A.[TYPE] = 2 THEN A.QTY END) A_QTY,
SUM(CASE WHEN B.[TYPE] = 2 THEN B.QTY END) B_QTY,
SUM(CASE WHEN C.[TYPE] = 2 THEN C.QTY END) C_QTY,
SUM(CASE WHEN A.[TYPE] = 3 THEN A.QTY END) A_QTY,
SUM(CASE WHEN B.[TYPE] = 3 THEN B.QTY END) B_QTY,
SUM(CASE WHEN C.[TYPE] = 3 THEN C.QTY END) C_QTY
FROM
#Table_A A
INNER JOIN #Table_B B ON A.DATE = B.DATE AND A.TYPE = B.TYPE
INNER JOIN #Table_C C ON A.DATE = C.DATE AND A.TYPE = C.TYPE
GROUP BY A.[DATE]
Current Result:
DATE | A_QTY | B_QTY | C_QTY A_QTY B_QTY C_QTY A_QTY B_QTY C_QTY
2018-01-01 | 10 | 5 | 5 NULL NULL NULL NULL NULL NULL
Expected Result:
DATE | A_QTY | B_QTY | C_QTY A_QTY B_QTY C_QTY A_QTY B_QTY C_QTY
2018-01-01 | 10 | 5 | 5 NULL 10 10 NULL 10 10
Please let me know how I can get expected result. (I know currently its not working because of inner join
).
The problem with your query is that, since it uses INNER JOIN
s, only records/date tuples that exists in all 3 tables are counted in.
If there is a table that can will always have all (TYPE
, DATE
) tuples available (in your sample data, that would be Table_C
), then you can use it a starting point and use LEFT JOIN
s :
FROM #Table_C C
LEFT JOIN #Table_A A ON A.DATE = C.DATE AND A.TYPE = C.TYPE
LEFT JOIN #Table_B B ON B.DATE = C.DATE AND B.TYPE = C.TYPE
Else, you probably want to change your INNER JOIN
s to FULL OUTER JOIN
s :
FROM #Table_A A
FULL OUTER JOIN #Table_B B ON A.DATE = B.DATE AND A.TYPE = B.TYPE
FULL OUTER JOIN #Table_C C ON A.DATE = C.DATE AND A.TYPE = C.TYPE
The FULL OUTER JOIN
solution requires to adapt the SELECT
and GROUP BY
clauses, since the date could come from any of the 3 tables.
Query :
SELECT
COALESCE(A.[DATE], B.[DATE], C.[DATE]) DATE,
SUM(CASE WHEN A.[TYPE] = 1 THEN A.QTY END) A_QTY,
SUM(CASE WHEN B.[TYPE] = 1 THEN B.QTY END) B_QTY,
SUM(CASE WHEN C.[TYPE] = 1 THEN C.QTY END) C_QTY,
SUM(CASE WHEN A.[TYPE] = 2 THEN A.QTY END) A_QTY,
SUM(CASE WHEN B.[TYPE] = 2 THEN B.QTY END) B_QTY,
SUM(CASE WHEN C.[TYPE] = 2 THEN C.QTY END) C_QTY,
SUM(CASE WHEN A.[TYPE] = 3 THEN A.QTY END) A_QTY,
SUM(CASE WHEN B.[TYPE] = 3 THEN B.QTY END) B_QTY,
SUM(CASE WHEN C.[TYPE] = 3 THEN C.QTY END) C_QTY
FROM
#Table_A A
FULL OUTER JOIN #Table_B B ON A.DATE = B.DATE AND A.TYPE = B.TYPE
FULL OUTER JOIN #Table_C C ON A.DATE = C.DATE AND A.TYPE = C.TYPE
GROUP BY COALESCE(A.[DATE], B.[DATE], C.[DATE])
This demo on DB Fiddle returns :
DATE | A_QTY | B_QTY | C_QTY | A_QTY | B_QTY | C_QTY | A_QTY | B_QTY | C_QTY
:------------------ | ----: | ----: | ----: | ----: | ----: | ----: | ----: | ----: | ----:
01/01/2018 00:00:00 | 10 | 5 | 5 | | 10 | 10 | | 10 | 10