IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C

select distinct T.PRIN_MON, 
T.AGNT_MON, 
T.SUB_ACCT_NO_MON, 
s.RES_NAME_SBB,
hs.ADDR1_HSE,
hs.RES_CITY_HSE,
hs.RES_STATE_HSE,
hs.POSTAL_CDE_HSE,
T.TRAN_DTE_MON, 
--T.TRAN_CDE_MON, 
T.TRAN_AMT_MON, 
s.CUR_BAL_SBB,
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, 
h.disco_dte_ohi, 
h.item_status_ohi

INTO #TMP1C

from vantage.dbo.ohi_hist_item as h (nolock),Vantage.dbo.SBB_BASE as s (nolock),
Vantage.dbo.HSE_BASE as hs (nolock),vantage.dbo.MON_TRAN_BASE as T (nolock)

AND h.PRIN_OHI = s.PRIN_SBB
and h.SYS_OHI = s.SYS_SBB
and h.SUB_ACCT_NO_OHI = s.SUB_ACCT_NO_SBB
and s.HSE_KEY_SBB = hs.HSE_KEY_HSE
and t.PRIN_MON = h.PRIN_OHI
and t.sub_acct_no_mon = h.sub_acct_no_ohi

where H.DISCO_DTE_OHI = (select max(disco_dte_ohi) as disco_dte_ohi 
FROM Vantage.dbo.OHI_HIST_ITEM AS B WHERE B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI)

and T.TRAN_CDE_MON = '287'
and h.acct_stage_ohi in ('V','N')
and h.ITEM_STATUS_OHI NOT IN ('B','X')


group by T.PRIN_MON, 
T.AGNT_MON, 
T.SUB_ACCT_NO_MON,
s.RES_NAME_SBB,
hs.ADDR1_HSE,
hs.RES_CITY_HSE,
hs.RES_STATE_HSE,
hs.POSTAL_CDE_HSE, 
T.TRAN_DTE_MON, 
--T.TRAN_CDE_MON, 
T.TRAN_AMT_MON, 
s.CUR_BAL_SBB,
h.CUSTOMER_STATUS,
h.connect_dte_ohi, 
h.disco_dte_ohi, 
h.item_status_ohi

SELECT A.*, EQ.eqp_stat_eqp, eq.EQP_SERIAL_EQP


FROM #TMP1C AS A (NOLOCK) left outer JOIN vantage.dbo.EQP_BASE as EQ (nolock)
ON h.SUB_ACCT_NO_OHI = EQ.EQP_LOCAL_EQP

group by A.PRIN_MON, 
A.AGNT_MON, 
A.SUB_ACCT_NO_MON,
A.RES_NAME_SBB,
A.ADDR1_HSE,
A.RES_CITY_HSE,
A.RES_STATE_HSE,
A.POSTAL_CDE_HSE, 
A.TRAN_DTE_MON, 
--T.TRAN_CDE_MON, 
A.TRAN_AMT_MON, 
A.CUR_BAL_SBB,
EQ.eqp_stat_eqp,
eq.EQP_SERIAL_EQP, 
A.acct_stage_ohi,
A.connect_dte_ohi, 
A.disco_dte_ohi, 
A.item_status_ohi

FROM		vantage.dbo.ohi_hist_item as h (nolock)
INNER JOIN 	Vantage.dbo.SBB_BASE as s (nolock)	ON	h.PRIN_OHI = s.PRIN_SBB	
							AND	h.SYS_OHI = s.SYS_SBB 
							AND	h.SUB_ACCT_NO_OHI = s.SUB_ACCT_NO_SBB
INNER JOIN 	Vantage.dbo.HSE_BASE as hs (nolock)	ON	s.HSE_KEY_SBB = hs.HSE_KEY_HSE
INNER JOIN 	vantage.dbo.MON_TRAN_BASE as T (nolock)	ON	t.PRIN_MON = h.PRIN_OHI
							AND	t.sub_acct_no_mon = h.sub_acct_no_ohi

quote:

---

Originally posted by khtan

You miss the WHERE after the FROM clause. Anyway, you should use INNER JOIN

FROM		vantage.dbo.ohi_hist_item as h (nolock)
INNER JOIN 	Vantage.dbo.SBB_BASE as s (nolock)	ON	h.PRIN_OHI = s.PRIN_SBB	
							AND	h.SYS_OHI = s.SYS_SBB 
							AND	h.SUB_ACCT_NO_OHI = s.SUB_ACCT_NO_SBB
INNER JOIN 	Vantage.dbo.HSE_BASE as hs (nolock)	ON	s.HSE_KEY_SBB = hs.HSE_KEY_HSE
INNER JOIN 	vantage.dbo.MON_TRAN_BASE as T (nolock)	ON	t.PRIN_MON = h.PRIN_OHI
							AND	t.sub_acct_no_mon = h.sub_acct_no_ohi

---

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

---

from vantage.dbo.ohi_hist_item as h (nolock),Vantage.dbo.SBB_BASE as s (nolock),
Vantage.dbo.HSE_BASE as hs (nolock),vantage.dbo.MON_TRAN_BASE as T (nolock)

AND h.PRIN_OHI = s.PRIN_SBB
and h.SYS_OHI = s.SYS_SBB
and h.SUB_ACCT_NO_OHI = s.SUB_ACCT_NO_SBB
and s.HSE_KEY_SBB = hs.HSE_KEY_HSE
and t.PRIN_MON = h.PRIN_OHI
and t.sub_acct_no_mon = h.sub_acct_no_ohi

where H.DISCO_DTE_OHI = (select max(disco_dte_ohi) as disco_dte_ohi 
FROM Vantage.dbo.OHI_HIST_ITEM AS B WHERE B.CUST_ACCT_NO_OHI = H.CUST_ACCT_NO_OHI)

and T.TRAN_CDE_MON = '287'
and h.acct_stage_ohi in ('V','N')
and h.ITEM_STATUS_OHI NOT IN ('B','X')

AND h.PRIN_OHI = s.PRIN_SBB
and h.SYS_OHI = s.SYS_SBB
and h.SUB_ACCT_NO_OHI = s.SUB_ACCT_NO_SBB
and s.HSE_KEY_SBB = hs.HSE_KEY_HSE
and t.PRIN_MON = h.PRIN_OHI
and t.sub_acct_no_mon = h.sub_acct_no_ohi