SELECT statement with where clause on Column

I am trying to gather some data from a table with INNER JOIN with another table. What I am trying to get have 2 Types and I need to separate these 2 types in 2 different columns.

In table where I am Inner joining have a column called ‘Data’ and another Column called ‘Type’ depending on the type of data I need to separate them when I inner join this table with another one.

Here is my script that I am trying to run:

SELECT 
C.StoreName, 'Time' = (SELECT CST.Data from tbStoreScheduleData CST
INNER JOIN tbPrograms P2 on CST.StoreScheduleDataID = P2.StoreScheduleDayID
 where CST.Type = 'T'  ) from tbPrograms P 

INNER JOIN tbStore C on P.StoreID= C.StoreID
INNER JOIN tbStoreScheduleData CS on P.StoreScheduleDayID = CS.StoreScheduleDataID

But when I run this I get everything NULL for Time. How can I put a where clause on a column in statement? to only get the Type ‘T’ for Time value?

In the Table Store Schedule Data, each store has entry values entered ONE is the time and other is the DAY and its separated and pulled depending on the Type. If you want to look at the days of the week store runs you pull the TYPE D and if you want Time you pull TYPE T. But pulling this in an inner join is something I am stuck on.

EDITED: THIS is what data looks like in StoreScheduleDataID

+---------------------+------+--------------------+-----------+----------+
| StoreScheduleDataID | Type |        Data        | SortOrder | WeekDays |
+---------------------+------+--------------------+-----------+----------+
|                   1 | D    | Monday to Thursday |         1 | 2345     |
|                   2 | D    | Monday to Friday   |         2 | 23456    |
|                   3 | D    | Tuesday to Friday  |         3 | 3456     |
|                   4 | D    | Tuesdays           |        11 | 3        |
|                   5 | T    | 8:00AM to 2:15PM   |        90 | NULL     |
|                   6 | T    | 8:00AM to 1:00PM   |        83 | NULL     |
|                   7 | T    | 8:30AM to 1:30PM   |       108 | NULL     |
+---------------------+------+--------------------+-----------+----------+

Desired output:

+------------------------------------+------------------+
| StoreName           Date           |       TIME       |
+------------------------------------+------------------+
| Store1          Monday to Friday   | 8:00AM to 2:15PM |
| Store2          Monday to Thursday | 8:00AM to 2:15PM |
| Store3          Monday to Friday   | 8:00AM to 2:15PM |
+------------------------------------+------------------+

I get this with the 1st answer given:

+------------------------------------+------+
| StoreName           Date           | TIME |
+------------------------------------+------+
| Store1          Monday to Friday   | NULL |
| Store2          Monday to Thursday | NULL |
| Store3          Monday to Friday   | NULL |
+------------------------------------+------+

Answer

Maybe this:

SELECT C.StoreName
      ,MAX(CASE WHEN CST.Type = 'T' THEN  CST.Data  END) AS [Time]
      ,MAX(CASE WHEN CST.Type = 'D' THEN  CST.Data  END) AS [Date]
from tbPrograms P 
INNER JOIN tbStore C 
    on P.StoreID= C.StoreID
INNER JOIN tbStoreScheduleData CS 
    on P.StoreScheduleDayID = CS.StoreScheduleDataID
GROUP BY C.StoreName

Advertisement

Answer