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

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  

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.