Ocean Ocean - 1 month ago 8
SQL Question

How to replace a value from the joined tables if it is present in sql

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.

TableA:

CustomerID Date StartTime
100 10/14/2016 11:00 AM
100 10/16/2016 10:00 AM
101 10/18/2016 11:30 AM


TableB:

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


TableMain:

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


Expected output:

CustomerID Day_of_the_week StartTime FinishTime
100 Saturday 11:00 AM 10:00 PM


The sql I have so far:

select
t.StartTime, t.FinishTime, t.CustomerID
from
(select
tm.CustomerID as CustomerID,
case
when tb.FinishTime is not null
then tb.FinishTime
else tm.FinishTime
as FinishTime,
case
when ta.StartTime is not null
then ta.StartTime
else tm.StartTime
as StartTime,
from
TableMain tm
left join
TableB tb on tm.CustomerID = tb.CustomerID
left join
TableA ta on tm.CustomerID = ta.CustomerID
where
tm.day_of_week = 'SATURDAY'
and tb.Date = '2016-10-14'
and ta.Date = '2016-10-14') t

Answer

You have missed to add END to both the CASE statements

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'