Hello everyone,
I've written the following code, but I'm receiving the following error in the subject line when I attempt to run. Is there something I'm missing, or do I have something within the wrong place. I've pasted the code below. Any and all help is greatly appreciated:
Select distinct
case
when a.SYS_SBB = 8495 THEN CAST(CONCAT(a.SYS_SBB, a.PRIN_SBB) AS BIGINT)
else a.SYS_SBB
end as 'SYS_PRIN'
,a.[RES_NAME_SBB]
,a.[SUB_ACCT_NO_SBB]
,a.[CUST_ACCT_NO_SBB]
,a.[HSE_KEY_SBB]
,case when a.[EXT_STAT_SBB] = ' ' then 'Active'
when a.[EXT_STAT_SBB] = 'C' then 'Voluntary Disconnect'
when a.[EXT_STAT_SBB] = 'E' then 'Non-Pay Disconnect'
when a.[EXT_STAT_SBB] = 'Z' then 'Charged Off'
else ' ' end as 'Account Status'
,a.[ORIG_CONN_DTE_SBB]
,a.[CONNECT_DTE_SBB]
from ExternalUser.[Vantage].[SBB_BASE] as A (nolock) inner join ExternalUser.[Vantage].[HSE_BASE] as B (nolock)
on a.[SUB_ACCT_NO_SBB] = b.[SUB_ACCT_NO_HSE]
and a.[HSE_KEY_SBB] = b.[HSE_KEY_HSE]
where b.[DWELL_TYP_HSE] = 'HA'
and b.[HSE_KEY_HSE] in (select distinct b.[HSE_KEY_HSE] from ExternalUser.[Vantage].[HSE_BASE]
group by a.SYS_SBB, a.PRIN_SBB, a.[RES_NAME_SBB], a.[SUB_ACCT_NO_SBB], a.[CUST_ACCT_NO_SBB], b.[HSE_KEY_HSE], a.[EXT_STAT_SBB], a.[ORIG_CONN_DTE_SBB], a.[CONNECT_DTE_SBB]
having count (b.[HSE_KEY_HSE]) > 1)
Hi Bitsmed,
Thank you for your quick reply. The tables are already created, so I don't have to use any create statements. I'm not sure what you're requesting when you ask for sample data in the form of insert statements. Lastly, I'm expecting to find multiple active accounts within the same physical location. This is why I'm trying to do a count of HSE_KEY_HSE > 1. HSE_KEY_HSE refers to the locations house key. If there were more than one account within the same physical location, I'm trying to bring these accounts back with the code I've written. Also, can you please elaborate on using "exists" I've actually never used this approach before.
I requested table description and sample data in order for me NOT to spend time to setup a scenario that I can work with, in order to help solve your problem.
exists goes something like this:
where b.[DWELL_TYP_HSE] = 'HA'
and exists (select 1
from ExternalUser.[Vantage].[HSE_BASE] as c
where c.[HSE_KEY_HSE]=b.[HSE_KEY_HSE]
group by c.[HSE_KEY_HSE]
having count(*)>1
)
But this is just me guessing, as I have no idea what your data looks like and can't test syntax.