CREATE TABLE TableA (
    col1 int,
    col2 varchar(10),
    col3 date
    )
INSERT INTO TableA VALUES(100, 'OPEN',  '01-JAN-2014')
INSERT INTO TableA VALUES(101, 'CLOSE', '12-JAN-2014')
INSERT INTO TableA VALUES(102, 'LOCK',  '10-JAN-2014')
INSERT INTO TableA VALUES(103, 'CLOSE', '12-JAN-2014')
CREATE TABLE TableB (
    col1 int,
    col2 varchar(10),
    col3 date
    )
INSERT INTO TableB VALUES(100, 'OPEN', '01-JAN-2014')
INSERT INTO TableB VALUES(101, 'OPEN', '04-JAN-2014')
INSERT INTO TableB VALUES(101, 'LOCK', '05-JAN-2014')
INSERT INTO TableB VALUES(101, 'CLOSE','12-JAN-2014')
INSERT INTO TableB VALUES(102, 'OPEN', '08-JAN-2014')
INSERT INTO TableB VALUES(102, 'LOCK', '10-JAN-2014')
INSERT INTO TableB VALUES(103, 'OPEN', '11-JAN-2014')
INSERT INTO TableB VALUES(103, 'CLOSE', '12-JAN-2014')
GO


SELECT COL1, CASE WHEN Lock_Date IS NOT NULL THEN 'LOCK' WHEN Close_Date IS NOT NULL THEN 'CLOSE' ELSE 'OPEN' END AS COL2,
     COALESCE(Lock_Date, Close_Date, Other_Date) AS COL3
FROM (
    SELECT COALESCE(a.COL1, b.COL1) AS COL1,
        MAX(CASE WHEN a.Lock_Date > b.Lock_Date THEN a.Lock_Date ELSE b.Lock_Date END) AS Lock_Date,
        MAX(CASE WHEN a.Close_Date > b.Close_Date THEN a.Close_Date ELSE b.Close_Date END) AS Close_Date,
        MAX(CASE WHEN a.Other_Date > b.Other_Date THEN a.Other_Date ELSE b.Other_Date END) AS Other_Date
    FROM (
        SELECT
            COL1,
            MAX(CASE col2 WHEN 'LOCK' THEN COL3 END) AS Lock_Date,
            MAX(CASE col2 WHEN 'CLOSE' THEN COL3 END) AS Close_Date,
            MAX(COL3) AS Other_Date    
        FROM TableA
        GROUP BY COL1
    ) AS a
    FULL OUTER JOIN (
        SELECT
            COL1,
            MAX(CASE col2 WHEN 'LOCK' THEN COL3 END) AS Lock_Date,
            MAX(CASE col2 WHEN 'CLOSE' THEN COL3 END) AS Close_Date,
            MAX(COL3) AS Other_Date    
        FROM TableB
        GROUP BY COL1
    ) AS b ON
        b.COl1 = a.COL1
    GROUP BY
        COALESCE(a.COL1, b.COL1)
) AS derived
ORDER BY COL1

quote:

---

Originally posted by James0816

In a word.....HUH? Wow. There was no overthinking that. Would have never came up with that query.

In either case, I copied and pasted it, changed to the real values and it didn't like the statement:

SQL command not properly ended Line 9 Col 1.

---