Hello everyone,
My objective is to bring back 1 record for an account that has moved/transferred to a new location. For the specific account I'm looking at, I continue to bring back two records, because there are two order numbers that are unique but were created and closed on the same dates. So the sub-queries that I'm using to bring back the max value doesn't work in this scenario. I've now tried to had min and max functions to the To order number, and the From order number which is now bringing me back the error message within the subject line, The code has been pasted below:
IF OBJECT_ID('TEMPDB..#tmp1o') IS NOT NULL DROP TABLE #tmp1o
IF OBJECT_ID('TEMPDB..#tmp2o') IS NOT NULL DROP TABLE #tmp2o
IF OBJECT_ID('TEMPDB..#tmp3o') IS NOT NULL DROP TABLE #tmp3o
select distinct
t.SYS_SBT, t.LS_CHG_DTE_SBT,
ts.RES_NAME_SBB ToSubName,
fs.RES_NAME_SBB FromSubName,
ts.CUST_ACCT_NO_SBB ToCust,
fs.CUST_ACCT_NO_SBB FromCust,
t.SUB_ACCT_NO_SBT To_Acct_No,
t.XREF_ACCT_NO_SBT From_Acct_No,
ts.HSE_KEY_SBB ToHouseKey,
fs.HSE_KEY_SBB FromHouseKey,
th.addr1 ToAdd1,
fh.addr1 FromAdd1,
th.HouseActivity ToHouseActivity,
fh.HouseActivity FromHouseActivity,
td.DwellingCatName ToDwellingCatName,
fd.DwellingCatName FromDwellingCatName,
td.GeographyKey ToGeographyKey,
fd.GeographyKey FromGeographyKey,
case
when ts.ext_stat_sbb = ' ' then 'Normal'
when ts.ext_stat_sbb = 'C' then 'Voluntary Disconnect'
when ts.ext_stat_sbb = 'E' then 'Non-Pay Disconnect'
when ts.ext_stat_sbb = 'Z' then 'Charged Off'
else ' ' end as 'ToSubStatus',
case
when fs.ext_stat_sbb = ' ' then 'Normal'
when fs.ext_stat_sbb = 'C' then 'Voluntary Disconnect'
when fs.ext_stat_sbb = 'E' then 'Non-Pay Disconnect'
when fs.ext_stat_sbb = 'Z' then 'Charged Off'
else ' ' end as 'FromSubStatus'
into #tmp1o
from externaluser.vantage.SBT_ACCT_TRANS as t (nolock) inner join externaluser.vantage.SBB_BASE as ts (nolock)
on t.SUB_ACCT_NO_SBT = ts.SUB_ACCT_NO_SBB
inner join WISDM.dim.House as th (nolock)
on ts.HSE_KEY_SBB = th.CSGHouseKey
join wisdm.dim.Dwellingflat as td (nolock)
on th.DwellingKey = td.DwellingKey
and th.GeographyKey = td.GeographyKey
inner join externaluser.vantage.SBB_BASE as fs (nolock)
on fs.sub_acct_no_sbb = t.xref_acct_no_sbt
inner join WISDM.dim.House as fh (nolock)
on fs.HSE_KEY_SBB = fh.CSGHouseKey
inner join wisdm.dim.Dwellingflat as fd (nolock)
on th.DwellingKey = fd.DwellingKey
and th.GeographyKey = fd.GeographyKey
where t.ACCT_TRANS_FLG_SBT = 'F'
and t.SYS_SBT = '8155'
and t.SUB_ACCT_NO_SBT = '8155200090956386'
--select * from #tmp1o
select *
into #tmp2o
from #tmp1o
where (left(FromAdd1,10) = left(ToAdd1,10)
and right(FromAdd1,5) <> right(ToAdd1,5) ----change this to = to find same address
)
and (FromDwellingCatName = 'COMMERCIAL' OR ToDwellingCatName = 'Commercial')
--select * from #tmp2o
select distinct b.*, min(oc.[ORDER_NO_OCR]) as ToOrderNumber, max(oc2.[ORDER_NO_OCR]) as FromOrderNumber, oc.CREATE_DTE_OCR ToOrderCreateDate, oc.[COMPL_DTE_OCR] ToOrderCompleteDate,
oc2.CREATE_DTE_OCR FromOrderCreateDate, oc2.[COMPL_DTE_OCR] FromOrderCompleteDate, wt.[PERNR] as ToPernr, wt.[NTLOGIN] as ToNTLogin, wt.[ROLE] AS Torole, sp2.name as Toname
into #tmp3o
from #tmp2o b inner join ExternalUser.[iCoMP].[t_WSTCBSTransactionData] wt (nolock)
on convert(varchar(255),b.To_Acct_No) = wt.[ACCOUNT_NUMBER]
inner join ExternalUser.[EMS].[SAPCurrent] sp2 (nolock)
on wt.[PERNR] = sp2.[PERNR]
inner join [Vantage].[OCR_ORDER_COMP] oc (nolock)
on b.ToCust = oc.cust_acct_no_ocr
and b.ToHouseKey = oc.hse_key_ocr
inner join [Vantage].[OCR_ORDER_COMP] oc2 (nolock)
on b.FromCust = oc2.cust_acct_no_ocr
and b.FromHouseKey = oc2.hse_key_ocr
--where oc2.[COMPL_DTE_OCR] = (select max([COMPL_DTE_OCR]) as [COMPL_DTE_OCR] from [Vantage].[OCR_ORDER_COMP] oc3 (nolock)
-- where oc2.cust_acct_no_ocr = oc3.cust_acct_no_ocr
-- and oc2.hse_key_ocr = oc3.hse_key_ocr)
--and oc.[COMPL_DTE_OCR] = (select max([COMPL_DTE_OCR]) as [COMPL_DTE_OCR] from [Vantage].[OCR_ORDER_COMP] oc4 (nolock)
-- where oc.cust_acct_no_ocr = oc4.cust_acct_no_ocr
-- and oc.hse_key_ocr = oc4.hse_key_ocr)
where oc2.[COMPL_DTE_OCR] between '2017-09-01' and '2017-09-30'
and oc.[ORDER_NO_OCR] = 'S'
and oc2.[ORDER_NO_OCR] = 'S'
group by b.SYS_SBT, b.LS_CHG_DTE_SBT,
b.ToSubName,
b.FromSubName,
b.ToCust,
b.FromCust,
b.To_Acct_No,
b.From_Acct_No,
b.ToHouseKey,
b.FromHouseKey,
b.ToAdd1,
b.FromAdd1,
b.ToHouseActivity,
b.FromHouseActivity,
b.ToDwellingCatName,
b.FromDwellingCatName,
b.ToGeographyKey,
b.FromGeographyKey,
b.ToSubStatus,
b.FromSubStatus,
oc.[ORDER_NO_OCR], oc2.[ORDER_NO_OCR], oc.CREATE_DTE_OCR, oc.[COMPL_DTE_OCR], oc2.CREATE_DTE_OCR, oc2.[COMPL_DTE_OCR], wt.[PERNR], wt.[NTLOGIN], wt.[ROLE], sp2.name
select * from #tmp3o
Any and all help is greatly appreciated. Thank you.
D.