Unable to get the last date from the query

Question

Unable to get the last date from the query

gazy007

Default port

Points: 1491
More actions
October 20, 2020 at 12:16 pm

#3799217

Hi,
Sorry if I couldn't find the correct section to post my question. I am trying to extract just the last date 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,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The tables in your query don't bear any relation to your sample data. And please use a universal format for dates, since I got a character conversion error when I tried to run the inserts. Finally, please show us your expected results, since it's not clear from your description what you're looking for.

Would something like this work?

SELECT
    MAX(TransactionDate)
,   NLNominalAccountID
,   NLNominalTranTypeID
,   AccountNumber
FROM mytable
GROUP BY
,   NLNominalAccountID
,   NLNominalTranTypeID
,   AccountNumber;

John

gazy007 Default port Points: 1491 More actions · Answer 2

Hi John,

I have tried the same solution as yours but it brings every date of transaction against the account.

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

gazy007 wrote:

Hi John,
I have tried the same solution as yours but it brings every date of transaction against the account.

Do you want the last(most recent?) date by account number? Or by some other combination of the columns?

If it is only by account number, then the group by in John's query needs to change.

In your original query, you are joining on the account number column. You would need to join on BOTH the account number and the date.

Are you really still using SQL 2008?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27038 More actions · Answer 4

Also, and I realize that this is test data, but a date column may not make a good primary key. I'm guess that there can be two transactions on the same day.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

gazy007 Default port Points: 1491 More actions · Answer 5

Thanks, John and Michael. It worked after Grouping the account number.