Comparing two dates in different tables SQL with criteria

Question

Click image to see the table structure and problm http://i.stack.imgur.com/odWKL.png

SELECT B.ENTITYID, 
       B.BALANCEDATE,
       B.BALANCE, 
       MIN(DATEDIFF(DAY,B.BALANCEDATE,C.STATUSDATE)) RECENT
FROM   BALANCES B JOIN STATUS C ON B.ENTITYID = C.ENTITYID
GROUP  BY B.ENTITYID, B.BALANCEDATE,B.BALANCE
HAVING B.ENTITYID =1

I have tried the following but can't go further than that as more nested selects have problems with accessing similar attributes:

Answer 1

See @ljh's Answer if you are working on SQL Server. The solution below works on MySQL. It's a little bit messy since MySQL doesn't support CTE and Window Function.

MySQL

SET @entity_name = 'ABCD';

SELECT  b.*, d.Status
FROM    Entity a
        INNER JOIN Balances b
            ON a.EntityID = b.EntityID
        LEFT JOIN
        (
            SELECT  a.EntityID, 
                    a.StatusDate StartDate, 
                    b.StatusDate + Interval -1 DAY EndDate,
                    a.Status
            FROM
                    (
                        SELECT  b.*, @r1 := @r1 + 1 AS Row_number
                        FROM    `Entity` a
                                INNER JOIN Status b
                                    ON a.EntityID = b.EntityID
                                CROSS JOIN (SELECT @r1 := 0) rowCount
                        WHERE   a.EntityName = @entity_name
                        ORDER   BY b.Status ASC
                    ) a
                    LEFT JOIN
                    (
                        SELECT  b.*, @r2 := @r2 + 1 AS Row_number
                        FROM    `Entity` a
                                INNER JOIN Status b
                                    ON a.EntityID = b.EntityID
                                CROSS JOIN (SELECT @r2 := 1) rowCount
                        WHERE   a.EntityName = @entity_name
                        ORDER   BY b.Status ASC
                    ) b ON  a.Row_number = b.Row_number
        ) d 
        ON b.BalanceDate BETWEEN d.StartDate AND d.EndDate
WHERE   a.EntityName = @entity_name

• SQLFiddle Demo

---

The Brief Break Down

Since, MySQL do not support Windowing Function such as ROW_NUMBER(), the query below uses User Variable to provide row number similar to ROW_NUMBER() for every record which will then be used to join on the other subquery.

SELECT  b.*, @r1 := @r1 + 1 AS Row_number
FROM    `Entity` a
        INNER JOIN Status b
            ON a.EntityID = b.EntityID
        CROSS JOIN (SELECT @r1 := 0) rowCount
WHERE   a.EntityName = @entity_name
ORDER   BY b.Status ASC

• SQLFiddle Demo

OUTPUT

╔══════════╦═════════════════════════════════╦════════╦════════════╗
║ ENTITYID ║           STATUSDATE            ║ STATUS ║ ROW_NUMBER ║
╠══════════╬═════════════════════════════════╬════════╬════════════╣
║        1 ║ May, 29 2010 00:00:00+0000      ║ A      ║          1 ║
║        1 ║ April, 16 2010 00:00:00+0000    ║ B      ║          2 ║
║        1 ║ April, 02 2010 00:00:00+0000    ║ C      ║          3 ║
║        1 ║ February, 26 2010 00:00:00+0000 ║ D      ║          4 ║
╚══════════╩═════════════════════════════════╩════════╩════════════╝

The main purpose of the provision of row number for the records is it will be used to join on another subquery so we can get the StartDate and EndDate for every Status. This is easy on SQL Server 2012 because it has a Windowing Function called LAG()

╔══════════╦═════════════════════════════════╦══════════════════════════════╦════════╗
║ ENTITYID ║            STARTDATE            ║           ENDDATE            ║ STATUS ║
╠══════════╬═════════════════════════════════╬══════════════════════════════╬════════╣
║        1 ║ May, 29 2010 00:00:00+0000      ║ (null)                       ║ A      ║
║        1 ║ April, 16 2010 00:00:00+0000    ║ May, 28 2010 00:00:00+0000   ║ B      ║
║        1 ║ April, 02 2010 00:00:00+0000    ║ April, 15 2010 00:00:00+0000 ║ C      ║
║        1 ║ February, 26 2010 00:00:00+0000 ║ April, 01 2010 00:00:00+0000 ║ D      ║
╚══════════╩═════════════════════════════════╩══════════════════════════════╩════════╝

Once the Status Range has been organized. It is now the basis as the LookUp status for every Balances.

The final Result

╔══════════╦═════════════════════════════════╦═════════╦════════╗
║ ENTITYID ║           BALANCEDATE           ║ BALANCE ║ STATUS ║
╠══════════╬═════════════════════════════════╬═════════╬════════╣
║        1 ║ May, 01 2010 00:00:00+0000      ║     100 ║ B      ║
║        1 ║ April, 01 2010 00:00:00+0000    ║      50 ║ D      ║
║        1 ║ March, 01 2010 00:00:00+0000    ║      75 ║ D      ║
║        1 ║ February, 01 2010 00:00:00+0000 ║      85 ║ (null) ║
╚══════════╩═════════════════════════════════╩═════════╩════════╝

---

SQL Server 2012

The query above demonstrated in MySQL can be easily converted in TSQL by using Common Table Expression and a Window Function which uses LAG() (introduce in SQL Server 2012 only)

WITH lookupTable
AS
(
    SELECT  EntityID, 
            StatusDate StartDate, 
            DATEADD(DAY, -1, LAG(StatusDate) OVER(PARTITION BY EntityID ORDER BY Status)) EndDate,
            Status
    FROM    Status
)
SELECT  b.*, d.Status
FROM    Entity a
        INNER JOIN Balances b
            ON a.EntityID = b.EntityID
        LEFT JOIN lookupTable d
            ON b.BalanceDate BETWEEN d.StartDate AND d.EndDate AND
               d.EntityID = a.EntityID
WHERE   a.EntityName = 'ABCD'

• SQLFiddle Demo

OUTPUT

╔══════════╦═════════════════════════════════╦═════════╦════════╗
║ ENTITYID ║           BALANCEDATE           ║ BALANCE ║ STATUS ║
╠══════════╬═════════════════════════════════╬═════════╬════════╣
║        1 ║ May, 01 2010 00:00:00+0000      ║     100 ║ B      ║
║        1 ║ April, 01 2010 00:00:00+0000    ║      50 ║ D      ║
║        1 ║ March, 01 2010 00:00:00+0000    ║      75 ║ D      ║
║        1 ║ February, 01 2010 00:00:00+0000 ║      85 ║ (null) ║
╚══════════╩═════════════════════════════════╩═════════╩════════╝