CODEWITHSUNDEEP
sql-serversql-server-2012
sky_limit
sky_limit

Reputation: 133

Select earliest date based on different events and join other tables

Current data:

Status table
CustomerID    Event      StartDateTime
1001          check-in   2015-04-13 10:15
1001          Transfer   2015-04-13 10:30
1001          check-in   2015-04-13 10:45
1001          check-out  2015-04-13 12:00
2001          Transfer   2015-04-15 12:00
2001          check-out  2015-04-15 12:30
3001          WaitRoom   2015-05-16 10:00
3001          check-in   2015-05-16 10:15

Location table
CustomerID    Location
1001          River
2001          Mountain
3001          Sun Rise

Facilty table
Customer ID     Description
1001            Gym
2001            Pool
3001            Breakfast

Required result:
CustomerID  Event     StartDateTime     Location     Description
1001        check-in  2015-04-13 10:15  River        Gym
2001        Transfer  2015-04-15 12:00  Mountain     Pool
3001        check-in  2015-05-16 10:15  Sun Rise     Breakfast

The earliest StartDateTime is picked based on the first check-in or Transfer Event. Get StartDateTime from first check-in. If check-in is not present, then get first Transfer StartDateTime. Thank a lot. Update: Below is what I'm trying but it's still not generating the right result. http://sqlfiddle.com/#!6/18d45/18

SELECT a.CustomerID, coalesce(b.Event,c.Event), coalesce(b.StartDateTime,c.StartDateTime),d.Location,e.Description
FROM (SELECT DISTINCT CustomerID FROM Status) a
LEFT JOIN Status b ON a.CustomerID = b.CustomerID AND b.Event='check-in'
LEFT JOIN Status c ON a.CustomerID = c.CustomerID AND c.Event='Transfer'
INNER JOIN Location d on a.CustomerID=d.CustomerID 
INNER JOIN Facility e on e.CustomerID=d.CustomerID

Upvotes: 2

Views: 158

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this using ROW_NUMBER:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY
                                CASE
                                    WHEN Event = 'check-in' THEN 1
                                    ELSE 2
                                END,
                                StartDateTime
                            )
    FROM Status
    WHERE Event IN('check-in', 'Transfer')
)
SELECT
    c.CustomerID, c.Event, c.StartDateTime, l.Location, f.Description
FROM Cte c
INNER JOIN Location l
    ON l.CustomerID = c.CustomerID
INNER JOIN Facility f
    ON f.CustomerID = c.CustomerID
WHERE RN = 1

Without using ROW_NUMBER:

SQL Fiddle

SELECT
    s.CustomerID,
    CASE WHEN MaxCheckIn IS NOT NULL THEN 'check-in' ELSE 'Transfer' END AS Event,
    CASE WHEN MaxCheckIn IS NOT NULL THEN MaxCheckIn ELSE MaxTransfer END AS StartDateTime,
    l.Location,
    f.Description
FROM (
    SELECT
        CustomerID,
        MAX(CASE WHEN Event = 'check-in' THEN StartDateTime END) AS MaxCheckIn,
        MAX(CASE WHEN Event = 'Transfer' THEN StartDatetime END) AS MaxTransfer
    FROM Status
    WHERE Event IN ('check-in', 'Transfer')
    GROUP BY CustomerID
)s
INNER JOIN Location l
    ON l.CustomerID = s.CustomerID
INNER JOIN Facility f
    ON f.CustomerID = s.CustomerID

Upvotes: 1

FutbolFan
FutbolFan

Reputation: 13713

This can be handled by assigning a dummy rank (change it to match according to your logic) and then a row_number to your ranks. And then, just filter out in the outer query where row_number is equal to 1 to get your desired output:

;WITH stat AS (
SELECT CustomerID
, Event
, StartDateTime
, CASE 
    WHEN event = 'check-in' then 1
    WHEN event = 'Transfer' then 2
  ELSE 3
  END  as Ranking
FROM Status
),
q2 as
(
SELECT 
    s.*,
    ROW_NUMBER () over (partition by customerid order by s.ranking) as rn
FROM stat AS s
)
select q2.customerid,
       q2.[event],
       q2.startdatetime,
       l.location,
       f.[description]
 from q2
JOIN Location AS l ON q2.CustomerID = l.CustomerID
JOIN Facility AS f ON q2.CustomerID = f.CustomerID
WHERE q2.rn = 1

SQL Fiddle Demo

Upvotes: 1

M. Grue
M. Grue

Reputation: 331

WITH stat AS (
SELECT CustomerID
, Event
, StartDateTime
, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY StartDateTime) as RowN
FROM Status
)
SELECT s.CustomerID, s.Event, s.StartDateTime, l.Location, f.Description
FROM stat AS s
JOIN Location AS l ON s.CustomerID = l.CustomerID
JOIN Facilty AS f ON s.CustomerID = f.CustomerID
WHERE s.RowN = 1

Hope it helps :)

Upvotes: 1

Related Questions