I have two queries which both create tables that I would like to cross join.
SELECT SITA, B, C, Rooms, Datearrived, Market_segment, StayDays as RN, AMTRoom as Rev
FROM [UKRMC].[dbo].[revenue] rev
JOIN [UKRMC].[dbo].[Contacts] contacts
ON rev.hotel_id = contacts.id
WHERE datearrived between '2017-01-01' and '2017-08-31' and C like '%GB%'
ORDER BY sita
This query gives a table which has each date from 2017-01-01 to 2017-08-31 with each SITA (there are 30 SITA) and each Market_segment (there are 18 distinct Market_segments but each SITA doesn't have all the Market_segments and this is where my problem lies).
My next query is:
SELECT SEG
FROM [UKRMC].[dbo].[Segmentation]
WHERE SEG IN ('RAC', 'BIT', 'BIQ', 'CBI', 'TOF', 'QOF', 'BOA', 'FIT', 'LYO', 'RER', 'OTH', 'NRG', 'XXX', 'CRW', 'BGR', 'BGO', 'LGR', 'LGS')
So this gives a table of the 18 Segments.
Now, what I want is a table which has each date from 2017-01-01 to 2017-08-31 with each SITA and each Market_Segment (So each date will have 30 x 18 rows) along with all the other columns from the first query too; B, C, Rooms, StayDays as RN, AMTRoom as Rev.
Of course since all dates and market_Segment/Seg combination aren't in the first table I would like to just output RN and Rev as 0 for these but B, C, Rooms is fixed for each SITA (so will i need another join for the fixed values?)
I hope it makes sense what I need. Any help is appreciated! Here is sample data for the two queries respectively.
SITA B C Rooms Datearrived Market_segment RN Rev
ABZPD PI GB 150 2017-01-01 TOF 2 45
ABZPD PI GB 150 2017-01-01 BAO 33 30.5
ABZPD PI GB 150 2017-01-01 BGR 11 50
ABZPD PI GB 150 2017-01-01 NRG 52 10
ABZPD PI GB 150 2017-01-01 CRW 20 90
ABZPD PI GB 150 2017-01-01 BIT 7 20
ABZPD PI GB 150 2017-01-01 CBI 0 40
ABZPD PI GB 150 2017-01-01 OTH 10 50
ABZPD PI GB 150 2017-01-01 BIQ 11 60
ABZPD PI GB 150 2017-01-01 RER 12 65
**SEG**
RAC
BIT
BIQ
CBI
TOF
QOF
BOA
FIT
LYO
RER
OTH
NRG
XXX
CRW
BGR
BGO
LGR
LGS
You need to cross join the distinct sita, B, C, Rooms, datearrived with your SEG list, and left join your original query. I can not tell where the columns are being sourced from in your query, so I am just treating table t as your original query.
select
d.SITA
, d.B
, d.C
, d.Rooms
, d.Datearrived
, Market_segment = s.Seg
, Rn = isnull(t.rn,0)
, Rev = isnull(t.Rev,0)
from (
select distinct sita, B, C, Rooms, datearrived
from dbo.t -- whichever table has the above columns
) d
cross join (
select SEG
from dbo.Segmentation
where SEG in ('RAC', 'BIT', 'BIQ', 'CBI', 'TOF', 'QOF', 'BOA', 'FIT', 'LYO', 'RER', 'OTH', 'NRG', 'XXX', 'CRW', 'BGR', 'BGO', 'LGR', 'LGS')
) s
left join dbo.t
on d.datearrived = t.datearrived
and d.sita = t.sita
and s.seg = t.market_segment
rextester demo: http://rextester.com/WSFUO57652
returns:
+-------+----+----+-------+-------------+----------------+----+-----+
| SITA | B | C | Rooms | Datearrived | Market_segment | Rn | Rev |
+-------+----+----+-------+-------------+----------------+----+-----+
| ABZPD | PI | GB | 150 | 2017-01-01 | RAC | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | BIT | 7 | 20 |
| ABZPD | PI | GB | 150 | 2017-01-01 | BIQ | 11 | 60 |
| ABZPD | PI | GB | 150 | 2017-01-01 | CBI | 0 | 40 |
| ABZPD | PI | GB | 150 | 2017-01-01 | TOF | 2 | 45 |
| ABZPD | PI | GB | 150 | 2017-01-01 | QOF | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | BOA | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | FIT | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | LYO | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | RER | 12 | 65 |
| ABZPD | PI | GB | 150 | 2017-01-01 | OTH | 10 | 50 |
| ABZPD | PI | GB | 150 | 2017-01-01 | NRG | 52 | 10 |
| ABZPD | PI | GB | 150 | 2017-01-01 | XXX | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | CRW | 20 | 90 |
| ABZPD | PI | GB | 150 | 2017-01-01 | BGR | 11 | 50 |
| ABZPD | PI | GB | 150 | 2017-01-01 | BGO | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | LGR | 0 | 0 |
| ABZPD | PI | GB | 150 | 2017-01-01 | LGS | 0 | 0 |
+-------+----+----+-------+-------------+----------------+----+-----+