Post reply

Unable to get the last date from the query

  • October 20, 2020 at 12:15 pm

    #3799215

    Hi,

    Sorry if I couldn't find the correct forum 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,

     

    Attachments:
    You must be logged in to view attached files.

  • October 20, 2020 at 2:27 pm

    #3799277

    Duplicate post

    https://www.sqlservercentral.com/forums/topic/unable-to-get-the-last-date-from-the-query-2

    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/

Viewing 2 posts - 1 through 1 (of 1 total)

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