question

isin1776 avatar image
isin1776 asked

Max Account - Group by vs SubQuery

Current Challenge: SQL 08 1: Pull the MAX ActyDt under CACSFuncCd+CACSStateNr = A51,A50,B17 2: But if CACSFuncCd+CACSFuncCd = A12,A99 and has the Most Current ActyDt then DO NOT pull into report. fyi: Unique Field CodeHistorySeq is backwards so (MAX ActyDt = the lowest CodeHistorySeq. AcctNr LtrCd ActyDt CACSFuncCd CACSStateNr CodeHistorySeq 876699 BLTRLM 4/4/2013 A 12 99723 876699 STATTO 4/3/2013 A 12 99724 876699 CC1PAY 4/1/2013 B 11 99725 876699 ADVREV 3/5/2013 A 51 99726 876699 ADVREV 2/7/2013 A 51 99727 876699 ADVREV 2/7/2013 A 50 99728 This is what I got so far: SELECT AcctNr, CACSFuncCd+CACSStateNr, MIN (CodeHistorySeq) AS CodeHistorySeq, Max (ActyDt) AS ActyDt FROM Table1 WHERE ((CACSFuncCd='A' and CACSStateNr in ('50','51')) or CACSFuncCd='B' and CACSStateNr in ('51')) Order by AcctNr Desc Results I like to see for this example would be NONE as A12 is the most Recent Date on Table1...... But again if A12 was not present then my Results would have been: B11
sql-server-2008group-bysub-query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.