Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery max date

Author  Topic 

Damian39
Starting Member

33 Posts

Posted - 2014-09-12 : 16:13:21
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_EQP
from vantage.dbo.MON_TRAN_BASE T
inner join
vantage.dbo.ohi_hist_item h
on t.sub_acct_no_mon = h.sub_acct_no_ohi
left outer JOIN
vantage.dbo.EQP_BASE EQ
ON T.SUB_ACCT_NO_MON = EQ.EQP_LOCAL_EQP
where 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_EQP

I would appreciate any help with this. Thank you!

Damian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-12 : 22:34:20
remove disco_dte_ohi from the GROUP BY clause


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -