TSQL Last Transaction Date

Question

Hi,

Sorry if I couldn't find the correct forum to post my question and now adding my question in Q and A section.

I am trying to extract just the last day of the account but I do not get the last transactiondate. I get all the transaction dates in the query. This query I have been working on

SELECT t.TransactionDate
,t.NLNominalAccountID
,NLNominalAccount.AccountNumber
FROM NLPostAndHistNominalTranView AS t
INNER JOIN NLNominalAccount ON t.NLNominalAccountID = NLNominalAccount.NLNominalAccountID
JOIN (
SELECT Max(TransactionDate) dt
,NLNominalAccountID
FROM NLPostAndHistNominalTranView
GROUP BY NLNominalAccountID
) x ON x.NLNominalAccountID = t.NLNominalAccountID
WHERE NLNominalAccount.AccountNumber = 37311

CREATE TABLE mytable(
TransactionDate DATE NOT NULL PRIMARY KEY
,NLNominalAccountID INTEGER NOT NULL
,NLNominalTranTypeID BIT NOT NULL
,AccountNumber INTEGER NOT NULL
);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('20/03/2014',13896,0,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('24/01/2014',13896,0,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/01/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/01/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/01/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/03/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/03/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/03/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/03/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('31/03/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('30/04/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('30/05/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('30/05/2014',13896,1,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('20/05/2016',13896,0,37311);
INSERT INTO mytable(TransactionDate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber) VALUES ('23/11/2016',13896,0,37311);

Can someone please guide me where I am going wrong?

Thanks,

Answer 1

For getting the last row of action, use ROW_NUMBER() to enumerate it accordingly and filter by it. E.g.

DECLARE @mytable TABLE (  
    TransactionDate DATE NOT NULL ,  
    NLNominalAccountID INTEGER NOT NULL ,  
    NLNominalTranTypeID BIT NOT NULL ,  
    AccountNumber INTEGER NOT NULL  
);  
  
INSERT INTO @mytable ( TransactionDate ,  
                       NLNominalAccountID ,  
                       NLNominalTranTypeID ,  
                       AccountNumber )  
VALUES ( '20/03/2014', 13896, 0, 37311 ) ,  
       ( '24/01/2014', 13896, 0, 37311 ) ,  
       ( '31/01/2014', 13896, 1, 37311 ) ,  
       ( '31/01/2014', 13896, 1, 37311 ) ,  
       ( '31/01/2014', 13896, 1, 37311 ) ,  
       ( '31/03/2014', 13896, 1, 37311 ) ,  
       ( '31/03/2014', 13896, 1, 37311 ) ,  
       ( '31/03/2014', 13896, 1, 37311 ) ,  
       ( '31/03/2014', 13896, 1, 37311 ) ,  
       ( '31/03/2014', 13896, 1, 37311 ) ,  
       ( '30/04/2014', 13896, 1, 37311 ) ,  
       ( '30/05/2014', 13896, 1, 37311 ) ,  
       ( '30/05/2014', 13896, 1, 37311 ) ,  
       ( '20/05/2016', 13896, 0, 37311 ) ,  
       ( '23/11/2016', 13896, 0, 37311 );  
  
WITH Ordered  
AS ( SELECT * ,  
            ROW_NUMBER() OVER ( PARTITION BY M.NLNominalAccountID  
                                ORDER BY M.TransactionDate DESC ) AS RN  
     FROM   @mytable M )  
SELECT O.TransactionDate ,  
       O.NLNominalAccountID ,  
       O.NLNominalTranTypeID ,  
       O.AccountNumber  
FROM   Ordered O  
WHERE  O.RN = 1;  

Answer 2

Remove the transaction table from the outer query, it's not required for the result

SELECT x.dt
      ,NA.NLNominalAccountID
      ,NLNominalAccount.AccountNumber
FROM NLNominalAccount AS NA    
     INNER JOIN 
     (SELECT Max(TransactionDate) dt, NLNominalAccountID
      FROM NLPostAndHistNominalTranView
      GROUP BY NLNominalAccountID
      ) 
      x ON x.NLNominalAccountID = NA.NLNominalAccountID
WHERE NLNominalAccount.AccountNumber = 37311