Merge two queries and two results into one...?

1

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

3

This might point you in the right direction:

with tallyno10(n)
  as (select 0 from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tallyno10(n))
    ,tallyno1000(n)
  as (select row_number() over(order by (select null))-1
        from tallyno10 as a
             cross apply tallyno10 as b
             cross apply tallyno10 as c
     )
select a_tid
      ,a_availdate
      ,a_colour
  from #temp_availability
 where a_availdate>=cast('2016-02-18' as date)
union all
select b.b_tid
      ,dateadd(day,a.n,b.b_startdate)
      ,'Yellow'
  from tallyno1000 as a
       cross apply #temp_bookings as b
 where dateadd(day,a.n,b.b_startdate)>=cast('2016-02-18' as date)
   and dateadd(day,a.n,b.b_startdate)<=b.b_enddate
4

@bitsmed, my suggestion was union all but then I did not know how to implement it. I also look into cross apply as a suggestion but was doing it all wrong when I had to get back to work. I never would have come up with it. I hope @QuattroDave can use your code as it looks to do the trick.

5

The tally "thingy" I used might not be that @QuattroDave is looking for, and he did mention having 2 queries he wanted to join, but failed to show us the second query. Maybe he's second query has a better approach.

But you were right in regards to union all :+1:

Lets wait and read he's response.

6

Evening guys,

@Bitsmed, wow awesome, thank you for the code. I've been studying it for a good few mins now and I must admit its a bit over my head. I need to do some more research 'Tally', 'Cross Apply' & 'Union All'. I've run it on some data and it looks to be working perfectly :smiley:

The second query I had wasn't much different to first, I didn't bother including it as I didn't want to confuse the matter and in all honesty it probably wouldn't make much sense as it was doing most of the formatting at application level. I much prefer this SQL solution, its faster and far more elegant...!

I'm about to give up for the night but will do some more testing and reading up tomorrow.

Once again, thank you, I wasn't expecting a complete coded solution, i just need to undertand it now!

Thanks

Dave