Flattening one to many query

Question

I have a table called Event and this joins to one or more Event Spaces within each Event Space is a booking start and end time.

These multiple event space lines are defined by an event type column which specifies whether the event space booking is the Check in, check out or the actual event.

Table structure looks like this

Event:
Event ID, Booking Start Date, Booking End Date

Event Space:
Event ID, SpaceCode, Event Type, Booking Start Time, Booking End Time

I want to bring those booking start times, and end times together on one row in my results. It's important to note that not all events will have multiple event spaces - some only have one.

Desired output:

Event ID, Bump in Start Time, Bump in End Time, Event Start Time, Event End Time, Bump out start time, bump out end time

When there is no result for one of the above columns it can either be left null or if someone has a better solution please let me know.

I have written a common table expression to pull this data together so far, but the resulting dataset is not flattened yet. I am having trouble coming up with a design to do so and would appreciate any ideas.

Thank you

QUERY:

    /*
    Bump in, bump out, event time
    grouped by DAY


*/
WITH Revenue as
(
SELECT  EV200_EVENT_MASTER.EV200_EVT_ID as [Event], 
        Revenue = 
        SUM(Case
        When Orddtl.ER101_PHASE = '1' and ORDDTL.ER101_COMPL_STS = 'N'
        then ORDDTL.ER101_EXT_CHRG 
        When ORDDTL.ER101_PHASE = '5' and ORDDTL.ER101_COMPL_STS = 'N'
        then ORDDTL.ER101_EXT_CHRG 
        Else 0
        End),
        SM.EV800_SPACE_DESC,
        SM.EV800_SPACE_CODE

FROM    ungerboeck.dbo.EV200_EVENT_MASTER WITH (NOLOCK) 
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER PrimCoord WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = PrimCoord.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_1 = PrimCoord.EV870_ACCT_CODE -- Event manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER FloorMgr WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = FloorMgr.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_2 = FloorMgr.EV870_ACCT_CODE --  Floor manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER EventAccount WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EventAccount.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_CUST_NBR = EventAccount.EV870_ACCT_CODE -- The person who made the booking 
        LEFT OUTER JOIN ungerboeck.dbo.EV215_EVT_TYPE WITH (NOLOCK) -- get the event type
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EV215_EVT_TYPE.EV215_ORG_CODE
        AND EV200_EVENT_MASTER.EV200_EVT_TYPE = EV215_EVT_TYPE.EV215_EVT_TYPE 
        LEFT OUTER JOIN ungerboeck.dbo.EV130_STATUS_MASTER WITH (NOLOCK) -- this might not be necessary. Only if we want to show status = completed, etc
        ON EV200_EVENT_MASTER.EV200_EVT_STATUS = EV130_STATUS_MASTER.EV130_STATUS_CODE
        LEFT OUTER JOIN ungerboeck.dbo.EV802_SPACE_BKD SPBK 
        ON SPBK.EV802_ORG_CODE = EV200_ORG_CODE AND SPBK.EV802_EVT_ID = EV200_EVT_ID
        LEFT OUTER JOIN ungerboeck.dbo.ER101_ACCT_ORDER_DTL ORDDTL
        ON ORDDTL.ER101_ORG_CODE = EV200_EVENT_MASTER.EV200_ORG_CODE 
        AND ORDDTL.ER101_EVT_ID = EV200_EVENT_MASTER.EV200_EVT_ID
        LEFT OUTER JOIN ungerboeck.dbo.EV800_SPACE_MASTER SM
        ON Sm.EV800_ORG_CODE = EV200_EVENT_MASTER.EV200_ORG_CODE
        AND SM.EV800_SPACE_CODE = SPBK.EV802_BKD_SPACE

WHERE   EV200_EVENT_MASTER.EV200_ORG_CODE = '10' 
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS >= 30 /* only confirmed bookings */
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS <= 52 
        AND Not(EV200_EVENT_MASTER.EV200_EVT_TYPE = 'GB') -- exclude group bookings

GROUP BY EV200_EVENT_MASTER.EV200_EVT_ID,SM.EV800_SPACE_DESC,EV800_SPACE_CODE

), eventdetails as
(
SELECT  EV200_EVENT_MASTER.EV200_EVT_ID as [Event], 
        EV215_EVT_TYP_DESC as [Event Type],
        EV200_event_master.EV200_EVT_DESC,
        EV200_EVENT_MASTER.EV200_PLN_ATTEND,
        --EventAccount.EV870_NAME AS [Account], 
        PrimCoord.EV870_FIRST_NAME + ' ' + PrimCoord.EV870_LAST_NAME AS [Event Manager]
        --FloorMgr.EV870_FIRST_NAME + ' ' + FloorMgr.EV870_LAST_NAME AS [Floor Manager]

FROM    ungerboeck.dbo.EV200_EVENT_MASTER WITH (NOLOCK) 
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER PrimCoord WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = PrimCoord.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_1 = PrimCoord.EV870_ACCT_CODE -- Event manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER FloorMgr WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = FloorMgr.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_COORD_2 = FloorMgr.EV870_ACCT_CODE --  Floor manager
        LEFT OUTER JOIN ungerboeck.dbo.EV870_ACCT_MASTER EventAccount WITH (NOLOCK) 
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EventAccount.EV870_ORG_CODE 
        AND EV200_EVENT_MASTER.EV200_CUST_NBR = EventAccount.EV870_ACCT_CODE -- The person who made the booking 
        LEFT OUTER JOIN ungerboeck.dbo.EV215_EVT_TYPE WITH (NOLOCK) -- get the event type
        ON EV200_EVENT_MASTER.EV200_ORG_CODE = EV215_EVT_TYPE.EV215_ORG_CODE
        AND EV200_EVENT_MASTER.EV200_EVT_TYPE = EV215_EVT_TYPE.EV215_EVT_TYPE 
        LEFT OUTER JOIN ungerboeck.dbo.EV130_STATUS_MASTER WITH (NOLOCK) -- this might not be necessary. Only if we want to show status = completed, etc
        ON EV200_EVENT_MASTER.EV200_EVT_STATUS = EV130_STATUS_MASTER.EV130_STATUS_CODE

WHERE   EV200_EVENT_MASTER.EV200_ORG_CODE = '10' 
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS >= 30 /* only confirmed bookings */
        AND EV200_EVENT_MASTER.EV200_EVT_STATUS <= 52 
        AND Not(EV200_EVENT_MASTER.EV200_EVT_TYPE = 'GB') -- exclude group bookings


), spacedetails as
(
SELECT distinct 
SPDTL.EV803_BKD_SPACE,
SPDTL.EV803_EVT_ID,
SM.EV800_SPACE_DESC,
CONVERT(DATE,SPDTL.EV803_BKG_DATE) as bkg_date,
spdtl.EV803_START_TIME,
spdtl.EV803_END_TIME,
SPDTL.EV803_BKG_START_TIME,
SPDTL.EV803_BKG_END_TIME,
SPDTL.EV803_USAGE,
EV800_NOTE_1 AS [VENUE]
FROM ungerboeck.dbo.EV803_SPACE_BKD_DTL SPDTL
INNER JOIN ungerboeck.dbo.EV800_SPACE_MASTER SM ON SPDTL.EV803_BKD_SPACE = SM.EV800_SPACE_CODE

)

select eventdetails.Event,
[Event Type],
eventdetails.EV200_EVT_DESC as 'Description',
eventdetails.EV200_PLN_ATTEND as 'PAX',
eventdetails.[Event Manager],
spacedetails.EV800_SPACE_DESC as 'Space',
spacedetails.bkg_date as 'Booking Date',
spacedetails.VENUE,
/*
(CASE 
    WHEN spacedetails.EV803_USAGE = 'IN' THEN 'BUMP IN'
    WHEN spacedetails.EV803_USAGE = 'ET' THEN 'EVENT'
    WHEN spacedetails.EV803_USAGE = 'OUT' THEN 'BUMP OUT'
END) as 'Booking Type',
*/
(SELECT spacedetails.
CAST(spacedetails.EV803_BKG_START_TIME as time(0)) as 'Start Time',
CAST(spacedetails.EV803_BKG_END_TIME as time(0)) as 'End Time',
revenue.revenue


from eventdetails 
inner join spacedetails on eventdetails.Event = spacedetails.EV803_EVT_ID 
inner join Revenue on spacedetails.EV803_EVT_ID = revenue.Event and spacedetails.EV803_BKD_SPACE = Revenue.EV800_SPACE_CODE

Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — pcdev, Mar 19 '18 at 00:38
@pcdev No, I am not trying to concatenate strings.These are separate columns — Asher, Mar 19 '18 at 00:40
Your query doesn't match your purported table design. Please either include the real table design, or a query that matches what you provided. — STLDev, Mar 19 '18 at 01:45
@STLDeveloper my table structure is a simplification of the underlying table. The real table has over 200 columns. Listing all of them does not seem practical. — Asher, Mar 19 '18 at 02:25

score 0 · Accepted Answer · answered Mar 19 '18 at 02:25

I was able to answer my own question.

I used a subquery

 (
    SELECT TOP(1) s.EV803_START_TIME
    FROM spacedetails s
    WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
    AND s.EV803_USAGE = 'IN'
    AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
    AND s.bkg_date = ss.bkg_date
    ) AS 'Bump-in Start Time',
    (
    SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
    FROM spacedetails s
    WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
    AND s.EV803_USAGE = 'IN'
    AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
    AND s.bkg_date = ss.bkg_date
    ) AS 'Bump-in End Time',


(
SELECT TOP(1) s.EV803_START_TIME
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'ET'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Event Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'ET'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Event End Time',

(
SELECT TOP(1) s.EV803_START_TIME
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'OUT'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-out Start Time',
(
SELECT TOP(1) CAST(s.EV803_BKG_END_TIME AS time(0))
FROM spacedetails s
WHERE s.EV803_EVT_ID = ss.EV803_EVT_ID
AND s.EV803_USAGE = 'OUT'
AND s.EV803_BKD_SPACE = ss.EV803_BKD_SPACE
AND s.bkg_date = ss.bkg_date
) AS 'Bump-out End Time',

You might want to add some `order by` clauses in there, or you may get unexpected output, depending on what your data looks like. Alternatively you could use `max` or `min` with `group by`. — pcdev, Mar 19 '18 at 06:04

Flattening one to many query

1 Answers1