TSQL Last Transaction Date

moses004 56 Reputation points
2020-10-20T09:25:32.527+00:00

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,

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-10-21T06:49:00.587+00:00

    Hi @moses004

    Welcome to the Q&A sql server transact sql forum! Your question is posted in the correct forum. If you have any questions about TSQL, welcome discussion in the forum, and experts will provide you with solutions and suggestions.

    The question you posted is not very clear, I tried to provide a solution. The following code returns the relevant data of the last transactiondate:

    ;with cte   
    as (select max(transactiondate) transactiondate1  
    from mytable)  
      
    select distinct transactiondate,NLNominalAccountID,NLNominalTranTypeID,AccountNumber  
    from mytable m  
    join cte c   
    on m.transactiondate=c.transactiondate1  
    

    34011-image.png

    If this doesn't solve your problem,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result(it can be in image or excel format, etc.)? So that we’ll get a right direction and make some test.

    If you are confused about which forum of sql server the question should be posted on, you can also post on this forum, we will also help you.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

0 additional answers

Sort by: Most helpful