Afternoon all,
I've been looking at this problem for a few days, I'm trying to merge 2 queries and 2 sets of results into 1 query with 1 set of results. I'm not even sure if its possible. I'll try and explain...
Test table & data - Availability Table:
CREATE TABLE #temp_Availability
(
A_TID INT,
A_AvailDate DATE,
A_Colour VARCHAR(15)
)
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1000, '2016-02-02', 'Red')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1000, '2016-02-03', 'Blue')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1000, '2016-02-04', 'Yellow')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1000, '2016-02-18', 'Green')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1000, '2016-02-19', 'Green')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1000, '2016-02-23', 'Green')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1001, '2016-02-25', 'Blue')
INSERT INTO #temp_Availability(A_TID, A_AvailDate, A_Colour)
VALUES(1001, '2016-02-26', 'Green')
Booking Table:
CREATE TABLE #temp_Bookings
(
B_TID INT,
B_StartDate DATE,
B_EndDate DATE
)
INSERT INTO #temp_Bookings(B_TID, B_StartDate, B_EndDate)
VALUES(1002, '2016-02-16', '2016-02-20')
INSERT INTO #temp_Bookings(B_TID, B_StartDate, B_EndDate)
VALUES(1002, '2016-02-25', '2016-02-28')
INSERT INTO #temp_Bookings(B_TID, B_StartDate, B_EndDate)
VALUES(1003, '2016-02-19', '2016-02-19')
INSERT INTO #temp_Bookings(B_TID, B_StartDate, B_EndDate)
VALUES(1003, '2016-02-16', '2016-02-16')
INSERT INTO #temp_Bookings(B_TID, B_StartDate, B_EndDate)
VALUES(1004, '2016-02-20', '2016-02-22')
If I query the Availability table with something like:
SELECT A_TID, A_AvailDate, A_Colour
FROM #temp_Availability
WHERE A_AvailDate >= '2016-02-18' --GETDATE()
Then I get the data I want from the Availability Table. However, the Booking Table also contains information I want to include in the results but in a slightly different layout.
The results just from the Availability Table would be:
A_TID A_Date A_Colour
1000 2016-02-19 Green
1000 2016-02-23 Green
1001 2016-02-25 Blue
1001 2016-02-26 Green
All data coming from the Bookings Table would have the colour 'Yellow'. So the combined results I would like to see would be something like:
A_TID A_Date A_Colour
1000 2016-02-19 Green
1000 2016-02-23 Green
1001 2016-02-25 Blue
1001 2016-02-26 Green
1002 2016-02-18 Yellow
1002 2016-02-19 Yellow
1002 2016-02-20 Yellow
1002 2016-02-25 Yellow
1002 2016-02-26 Yellow
1002 2016-02-27 Yellow
1002 2016-02-28 Yellow
1003 2016-02-19 Yellow
1004 2016-02-20 Yellow
1004 2016-02-21 Yellow
1004 2016-02-22 Yellow
I hope i've explained that ok... Any idea how I might achieve this, originally I looked at 'UNION' but it doesn't like duplicates...?
Thanks
Dave