-1

So i have 2 tables, one keeps the record of the cash on tjhe cash register at the end of the day and how much money is left for the next day, the other keeps a record of basicly the date of the record (tables cannot be joined) All that looks like this:

    RegistersTable
------------------
Register_ID| DATE
5          | 02/02/2012
8          | 04/02/2012
1          | 10/02/2012

CashTable
----------------
Register_ID|CashEOD|CashFND
8          |3235   |325
5          |6843   |435
1          |1236   |1953

So what im trying to get is a select statement that should return this

RegisterID|      DATE|CashEOD|PrevCashFND
1         |10/02/2012|1236   |325
8         |04/02/2012|3235   |435
5         |02/02/2012|6843   |0/Null

Start with a RegisterID on the CashTable, find the previous RegisterID by the DATE in RegistersTable, get the previous CashFND so the final goal is to know how much was selled on that day. Cash End of Day minus the Cash left on the register from the previous day should tell me that. Thanks in advance.

Vyacheslav Shcherbakov
  • 55
  • 6
  • Is there a reason the solutions proposed here: http://stackoverflow.com/q/879111/3915817 would not work for you? – KHeaney Jan 08 '15 at 22:32

1 Answers1

0

Please try the following - I hope it helps:

SELECT
    D.RegisterID,
    D.[DATE],
    CashTable.CashEOD,
    CashTable_Prev.CashFND AS PrevCashFND
FROM
    (
        SELECT 
            A.RegisterID,
            A.[DATE],
            B.RegisterID AS PrevID
        FROM
            RegistersTable A
            INNER JOIN RegistersTable B ON A.RegisterID <> B.RegisterID AND A.[DATE] > B.[DATE]
            INNER JOIN
            (
                SELECT 
                    _innA.RegisterID,
                    _innB.RegisterID As PrevID,
                    MIN(_innA.[DATE] - _innB.[DATE]) AS MinDateDiff
                FROM
                    RegistersTable _innA
                    INNER JOIN RegistersTable _innB ON _innA.RegisterID <> _innB.RegisterID AND _innA.[DATE] > _innB.[DATE]
                GROUP BY
                    _innA.RegisterID,
                    _innB.RegisterID
            ) C
                ON A.RegisterID = C.RegisterID AND B.RegisterID = C.PrevID AND (A.[DATE] - B.[DATE]) = C.MinDateDiff
    ) AS D
    INNER JOIN CashTable 
        ON CashTable.Register_ID = D.RegisterID
    INNER JOIN CashTable AS CashTable_Prev
        ON CashTable_Prev.Register_ID = D.PrevID