I need little help with Concatenation and outer Join .
I ahve two tables with different level of data sets
Table 1
Orders:
Load
OrderID,
OrderQty,
OrderDate
OrderItemsID
From ****
Table 2
Load
OrderID,
OrderQty,
OrderDate,
Order type
From ****
Table 2 contains all orders like approve, cancelled, pending while Table 1 is all approve orders.
Now I want to want to join these tables with calendar, for this I was concatenating table1 and table 2 based on dates but for one order it gives me two rows like
Order no Orderdate orderQty ordertype Year Month
1 1.1.2023 100 - 2023 Jan
- - 100 approved - -
i want result to be
Order no Orderdate orderQty ordertype Year Month
1 1.1.2023 100 approved 2023 Jan
If i do outer join on orderID i get single row but i dont want two separate calendars
