Unable to get the last date from the query

• gazy007

  Default port

  Points: 1491

  More actions

  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.

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  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/