Multiple case statements return 1 column with multiple rows

Question

I need to evaluate two sets of time stamps and create 1 column that could have multiple rows. To further complicate things, I need to use functions to collect those time stamps. What I would like:

SELECT DISTINCT

mpd.ccn AS CCN,
mpd.date AS Date,
[functionAStartTime] AS A_Start,
[functionAStopTime] AS A_Stop,
[functionBStartTime] AS B_Start,
[functionBStopTime] AS B_Stop,
mpd.fac AS Facility,
bmpi.mod AS Module,
CASE WHEN [functionAStartTime] <= '05:59' AND [functionAStopTime] >= '00:00' THEN 'A' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '05:59' AND [functionBStopTime] >= '00:00' THEN 'A' END AS TimeGroup,
CASE WHEN [functionAStartTime] <= '11:59' AND [functionAStopTime] >= '06:00' THEN 'B' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '11:59' AND [functionBStopTime] >= '06:00' THEN 'B' END AS TimeGroup,
CASE WHEN [functionAStartTime] <= '17:59' AND [functionAStopTime] >= '12:00' THEN 'C' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '17:59' AND [functionBStopTime] >= '12:00' THEN 'C' END AS TimeGroup,
CASE WHEN [functionAStartTime] <= '23:59' AND [functionAStopTime] >= '18:00' THEN 'D' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '23:59' AND [functionBStopTime] >= '18:00' THEN 'D' END AS TimeGroup

FROM

bmpi,
mpd

WHERE

mpd.pid = bmpi.pid
AND mpd.cec = bmpi.cec

Each CCN could have either or both sets of time stamps, and either set could span more than one group. This obviously will create multiple columns (Time_Group, Time_Group_1, Time_Group_3, etc.) I need one "Time Group" column with a separate row for each result. Such as:

CCN   |  Date    | A_Start | A_Stop | B_Start | B_Stop | Facility | Module | Time Group
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0012   | B
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0012   | C
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0021   | C
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0021   | D
4321  | 02/02/02 | 02:00   | 03:00  | 13:00   | 14:00  | ABCD     | 0012   | A
4321  | 02/02/02 | 02:00   | 03:00  | 13:00   | 14:00  | ABCD     | 0021   | C
0001  | 03/03/03 | 06:00   | 08:00  | NULL    | NULL   | WPDH     | 0012   | B

I've been teaching myself searching for what I need when I need it, so an example and explanation would be greatly appreciated.

Answer 1

So a CASE statement is a way of doing "if - then - else" logic in SQL, not a way of getting multiple results.

I think you need a subquery that has each of your Time Group values as separate columns, then the main query will UNPIVOT those columns. Something like this might work:

SELECT 
    CCN,
    [Date],
    A_Start,
    A_Stop,
    B_Start,
    B_Stop,
    Facility,
    Module,
    TimeGroup,
    TimeGroupValues
FROM
    (
    SELECT DISTINCT
        mpd.ccn AS CCN,
        mpd.date AS [Date],
        [functionAStartTime] AS A_Start,
        [functionAStopTime] AS A_Stop,
        [functionBStartTime] AS B_Start,
        [functionBStopTime] AS B_Stop,
        mpd.fac AS Facility,
        bmpi.mod AS Module,
        CASE 
            WHEN [functionAStartTime] <= '05:59' AND [functionAStopTime] >= '00:00' 
            THEN 'A' 
            WHEN [functionBStartTime] <= '05:59' AND [functionBStopTime] >= '00:00' 
            THEN 'A' 
            END AS TimeGroupA,
        CASE 
            WHEN [functionAStartTime] <= '11:59' AND [functionAStopTime] >= '06:00' 
            THEN 'B' 
            WHEN [functionBStartTime] <= '11:59' AND [functionBStopTime] >= '06:00' 
            THEN 'B' 
            END AS TimeGroupB,
        CASE 
            WHEN [functionAStartTime] <= '17:59' AND [functionAStopTime] >= '12:00' 
            THEN 'C' 
            WHEN [functionBStartTime] <= '17:59' AND [functionBStopTime] >= '12:00' 
            THEN 'C' 
            END AS TimeGroupC,
        CASE 
            WHEN [functionAStartTime] <= '23:59' AND [functionAStopTime] >= '18:00' 
            THEN 'D'
            WHEN [functionBStartTime] <= '23:59' AND [functionBStopTime] >= '18:00' 
            THEN 'D' 
            END AS TimeGroupD
    FROM
        bmpi
    JOIN
        mpd
    ON
        mpd.pid = bmpi.pid
    AND 
        mpd.cec = bmpi.cec
    ) AS u
    UNPIVOT
        (TimeGroup FOR TimeGroupValues IN
            (
                TimeGroupA, 
                TimeGroupB, 
                TimeGroupC, 
                TimeGroupD
            )
        ) AS p