CODEWITHSUNDEEP
sqlsql-servert-sql
MehmanBashirov
MehmanBashirov

Reputation: 661

How can I write correct query?

   WITH Encashment AS (
        SELECT  T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured
        FROM (VALUES 
              (1, 101, '2017-10-20 09:36:40.057')
             ,(1, 203, '2017-10-14 12:36:30.081')
             ,(1, 400, '2017-10-11 04:17:38.023')
             ) AS T(MachineId, Amount, Occured)
    ), MoneyAccepted  AS (
        SELECT  T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured
        FROM (VALUES 
              (1, 1, '2017-10-15 09:36:40.057')
             ,(1, 100, '2017-10-16 12:36:30.081')
             ,(1, 100, '2017-10-12 16:17:38.023')
             ,(1, 1, '2017-10-13 09:37:47.057')
             ,(1, 1, '2017-10-13 09:37:47.057')
             ,(1, 1, '2017-10-12 15:37:47.057')
             ,(1, 100, '2017-09-15 12:37:31.081')
             ,(1, 100, '2017-09-15 16:37:31.081')
             ,(1, 100, '2017-09-16 13:37:31.081')
             ,(1, 100, '2017-09-17 13:37:31.081')
             ) AS T(MachineId, Amount, Occured)
    )

I can get Amount among two encashment.(Select Amount from Encashment).
But, I want to get amount from MoneyAccepted for every Encashment.
For example: Encashment happened in 20-10-2017,till this dateTime accepted
101(100(2017-10-16 12:36:30.081)+1(2017-10-15 09:36:40.057)) money.
How can I get that?

Thanks in advance!

Upvotes: 0

Views: 104

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15130

I think what you are looking for is:

DECLARE @Encashment AS TABLE (MachineID INT, Amount INT, Occured DATETIME2)
DECLARE @MoneyAccepted AS TABLE (MachineID INT, Amount INT, Occured DATETIME2)

INSERT @Encashment (MachineID, Amount, Occured)
VALUES  (1, 101, '20171020 09:36:40.057')
,       (1, 203, '20171014 12:36:30.081')
,       (1, 400, '20171011 04:17:38.023')

INSERT @MoneyAccepted (MachineID, Amount, Occured)
VALUES  (1, 1, '20171015 09:36:40.057')
,       (1, 100, '20171016 12:36:30.081')
,       (1, 100, '20171012 16:17:38.023') 
,       (1, 100, '20171014 09:17:38.023')
,       (1, 1, '20171013 09:37:47.057')
,       (1, 1, '20171013 09:37:47.057')
,       (1, 1, '20171012 15:37:31.081') 

SELECT      E.Occured AS Encashment_Occured
,           SUM(MA.Amount) AS SUM_Amount
FROM        @MoneyAccepted AS MA
INNER JOIN (
                SELECT  MachineID
                ,       Amount
                ,       Occured
                ,       LAG(Occured) OVER(PARTITION BY MachineID ORDER BY Occured) AS Previous_Occured 
                FROM    @Encashment
            ) AS E
        ON  E.MachineID = MA.MachineID
        AND E.Occured > MA.Occured
        AND E.Previous_Occured <= MA.Occured
GROUP BY    E.Occured

Result:

+-----------------------------+------------+
|     Encashment_Occured      | SUM_Amount |
+-----------------------------+------------+
| 2017-10-14 12:36:30.0810000 |        203 |
| 2017-10-20 09:36:40.0570000 |        101 |
+-----------------------------+------------+

This uses LAG, which was introduced in sql server 2012, in order to get the range of applicable dates in a single row.

Upvotes: 2

Ezin82
Ezin82

Reputation: 374

Please edit your question, remove html and use plain text for sample data.

I think you could use CROSS APPLY. Try this:

WITH Encashment AS (
    SELECT  T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured
    FROM (VALUES 
          (1, 101, '2017-10-20 09:36:40.057')
         ,(1, 203, '2017-10-14 12:36:30.081')
         ,(1, 400, '2017-10-11 04:17:38.023')
         ) AS T(MachineId, Amount, Occured)
), MoneyAccepted  AS (
    SELECT  T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured
    FROM (VALUES 
          (1, 1, '2017-10-15 09:36:40.057')
         ,(1, 100, '2017-10-16 12:36:30.081')
         ,(1, 100, '2017-10-12 16:17:38.023')
         ,(1, 1, '2017-10-13 09:37:47.057')
         ,(1, 1, '2017-10-13 09:37:47.057')
         ,(1, 1, '2017-10-12 15:37:47.057')
         ,(1, 100, '2017-09-15 12:37:31.081')
         ,(1, 100, '2017-09-15 16:37:31.081')
         ,(1, 100, '2017-09-16 13:37:31.081')
         ,(1, 100, '2017-09-17 13:37:31.081')
         ) AS T(MachineId, Amount, Occured)
)
SELECT M.*, EN.* 
FROM MoneyAccepted AS M
CROSS APPLY (
    SELECT TOP (1) E.* FROM Encashment AS E
    WHERE E.MachineId = M.MachineId AND E.Occured > M.Occured
    ORDER BY E.Occured ASC
) AS EN

Upvotes: 1

Related Questions