I've written a query calling for the max disconnect date within a table, but I would also like to make sure to only retrieve records with the most recent connect date. I'm unsure how to go about adding the additional max date function to my query. Here's what I have written:select distinct T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON, EQ.eqp_stat_eqp, case when h.acct_stage_ohi = 'v' then 'VOL_DISCO' when h.ACCT_STAGE_OHI = 'n' then 'NON_PAY_DISCO'ELSE '' END AS 'CUSTOMER_STATUS',h.connect_dte_ohi, max(h.disco_dte_ohi) as disco_dte_ohi, h.item_status_ohi, eq.EQP_SERIAL_EQPfrom vantage.dbo.MON_TRAN_BASE Tinner joinvantage.dbo.ohi_hist_item hon t.sub_acct_no_mon = h.sub_acct_no_ohileft outer JOINvantage.dbo.EQP_BASE EQON T.SUB_ACCT_NO_MON = EQ.EQP_LOCAL_EQPwhere T.TRAN_CDE_MON = '287'and h.acct_stage_ohi in ('V','N')and h.ITEM_STATUS_OHI NOT IN ('B','X')and eq.EQP_STAT_EQP not in ('d','h')--and t.TRAN_DTE_MON BETWEEN '2013-01-01 00:00:00' and '2014-09-06 00:00:00'group by T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON, T.TRAN_AMT_MON, EQ.eqp_stat_eqp, h.acct_stage_ohi,h.connect_dte_ohi, h.disco_dte_ohi, h.item_status_ohi,eq.EQP_SERIAL_EQPI would appreciate any help with this. Thank you!Damian
Subquery max date
Posted - 2014-09-12 : 16:13:21
