1

Say I added a Cost Difference column to the second table from Rishal (see the below link for this previous post), how would I also calculate and display that?

Using just the 1001 Account Number and adding the following amounts of ID1=$10, ID4=$33 and ID6=$50 to the first table, how would I display in Rishal's second table a result of $23 and $17 in addition to the other 3 columns that are already there?

I've used this code (from GarethD) and would like to insert my Cost Difference column within this...Thanks in advance,

SELECT  ID,
        AccountNumber,
        Date,
        NextDate,
        DATEDIFF("D", Date, NextDate)
FROM    (   SELECT  ID, 
                    AccountNumber,
                    Date,
                    (   SELECT  MIN(Date) 
                        FROM    YourTable T2
                        WHERE   T2.Accountnumber = T1.AccountNumber
                        AND     T2.Date > T1.Date
                    ) AS NextDate
            FROM    YourTable T1
        ) AS T

Date Difference between consecutive rows

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
rememberthecoop
  • 11
  • 1

1 Answers1

0

I would recommend using JOIN to bring in the entire next record:

SELECT T.*, DATEDIFF("D", t.Date, t.NextDate) as datediff,
       TNext.Amount, (Tnext.Amount - T.Amount) as amountdiff
FROM (SELECT T1.*,
             (SELECT MIN(Date) 
              FROM YourTable T2
              WHERE T2.Accountnumber = T1.AccountNumber AND
                    T2.Date > T1.Date
            ) AS NextDate
      FROM  YourTable as T1
     ) AS T LEFT JOIN
     YourTable as Tnext
     ON t.Accountnumber = tnext.Accountnumber AND t.Date = tnext.Accountnumber;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, thanks for quick reply. What is "YourTable as Tnext"? I have two tables that are predefined, basically named T1 and T2 and that's it. I'm unclear about that line of code. And should the last bit of your code show "t.Date=tnext.Date"? Again, thanks! – rememberthecoop Sep 17 '18 at 18:16
  • @rememberthecoop . . . You have one table. The `T1`, `T2`, and `TNext` are all table aliases. – Gordon Linoff Sep 17 '18 at 23:43