I am working on sqlserver. I have three tables, TableA, TableB and TableMain, with Date, StartTime and FinishTime columns. These three tables can be joined by the common CustomerID. Unfortunately the TableMain has day_of_week while the other two have date. I have to query by the date, day_of_week and the customerid. In the sql given below I am using the date 2016-10-14, which corresponds to Saturday. The SQL I have used is given below. However he SQL doesn't compile. I am getting "Incorrect syntax near the keyword 'as'" error.
CustomerID Date StartTime
100 10/14/2016 11:00 AM
100 10/16/2016 10:00 AM
101 10/18/2016 11:30 AM
CustomerID Date FinishTime
100 10/15/2016 3:00 PM
101 10/16/2016 4:00 PM
102 10/17/2016 6:30 PM
102 10/18/2016 5:00 PM
CustomerID Day_of_week StartTime FinishTime
100 Monday 8:00 AM 10:00 PM
100 Tuesday 8:00 AM 10:00 PM
100 Wednesday 8:00 AM 10:00 PM
100 Thursday 8:00 AM 10:00 PM
100 Friday 8:00 AM 10:00 PM
100 Saturday 8:00 AM 10:00 PM
CustomerID Day_of_the_week StartTime FinishTime
100 Saturday 11:00 AM 10:00 PM
t.StartTime, t.FinishTime, t.CustomerID
tm.CustomerID as CustomerID,
when tb.FinishTime is not null
when ta.StartTime is not null
TableB tb on tm.CustomerID = tb.CustomerID
TableA ta on tm.CustomerID = ta.CustomerID
tm.day_of_week = 'SATURDAY'
and tb.Date = '2016-10-14'
and ta.Date = '2016-10-14') t
You have missed to add
END to both the
Select ......... CASE WHEN tb.FinishTime IS NOT NULL THEN tb.FinishTime ELSE tm.FinishTime END AS FinishTime, --Here CASE WHEN ta.StartTime IS NOT NULL THEN ta.StartTime ELSE tm.StartTime END AS StartTime --Here & remove the comma .........
But it can be simplified using
COALESCE, it will return the first
NOT NULL value from the column list.
COALESCE(tb.FinishTime, tm.FinishTime) AS FinishTime, COALESCE(ta.StartTime, tm.StartTime) AS StartTime
Another logical mistake was you are filtering the
Left table column in
Where clause which implicitly converts the
LEFT JOIN to
INNER JOIN. Here is the correct query
SELECT tm.CustomerID AS CustomerID, COALESCE(tb.FinishTime, tm.FinishTime) AS FinishTime, COALESCE(ta.StartTime, tm.StartTime) AS StartTime FROM TableMain tm LEFT JOIN TableB tb ON tm.CustomerID = tb.CustomerID AND tb.Date = '2016-10-14' LEFT JOIN TableA ta ON tm.CustomerID = ta.CustomerID AND ta.Date = '2016-10-14' WHERE tm.day_of_week = 'SATURDAY'