CODEWITHSUNDEEP
sqljoinunion
Sanguinus
Sanguinus

Reputation: 21

SQL Join/Merge/Union multiple tables based on Date

I have a database that has a Date in each table, which I want to combine. The problem I have is when one date is present in one table it does not show measures if that date is not present in other tables.

I am trying to connect/merge two SQL queries together based on their Date and PersonID.

I have Table A and B and want to create C.

Can anyone help?

Upvotes: 2

Views: 792

Answers (1)

VDK
VDK

Reputation: 583

You might be able to do something like this, using a CTE and UNION ALL:

WITH tableC
AS
(


SELECT personID,
   person,
   datekey,
   date,
   SUM(sales) AS sales,
   NULL AS costs
FROM tableA
GROUP BY personID, person, datekey, date

UNION ALL

SELECT personID,
   person,
   datekey,
   date,
   NULL,
   SUM(costs) AS costs
FROM tableB
GROUP BY personID, person, datekey, date

)


SELECT personID,
   person,
   datekey,
   date,
   ISNULL(SUM(sales), 0) AS sales,
   ISNULL(SUM(costs),0) AS costs
FROM tableC
GROUP BY personID, person, datekey, date
ORDER BY datekey

Upvotes: 1

Related Questions