Evening all,
I'm getting rather confused, I have a query that produces 6 columns, but I only want 5 columns. 2 of the columns contain data that I want to merge but I'm getting really confused about how to achieve it...
Probably the best way to describe is give you code & example so here goes.....
Declare @Test_ID Int = 2970,
@StartDate Date = '2016-03-31',
@EndDate Date = '2016-03-31';
SELECT
T_TID,
T_FName,
T_LName,
A.*,
B.*,
TR_Rating
FROM Empolyees
FULL OUTER JOIN Ratings
ON TR_TID = T_TID
AND TR_SID = @Test_ID
OUTER APPLY
(SELECT TOP 1 A_Colour
FROM Availability
WHERE A_TID = T_TID
AND A_AvailDate >= @StartDate
AND A_AvailDate <= @EndDate
GROUP BY A_Colour
ORDER BY COUNT(*) Desc)A
OUTER APPLY
(SELECT TOP 1
CASE
WHEN B_BID > 0 THEN 'Yellow'
END AS BookingColour
FROM Bookings
WHERE B_TID = T_TID
AND B_StartDate >= @StartDate
AND B_EndDate <= @EndDate
GROUP BY B_BID
ORDER BY COUNT(*) Desc)B
WHERE T_Status = 'Live'
The current output is something like this:
I would like to keep 'A_Colour' and do away with 'BookingColour'. The only rule is that 'Booking Colour' over rides 'A_Colour' so in the example line 6 would be 'Yellow' not 'Red' and line 9 'Yellow' not NULL....
Originally I was trying to achieve it with the REPLACE command but couldn't get it to work properly... Any ideas how I might achieve this.....?
Thanks
Dave