SQL Server Query Help - Comparing one column against two columns

Question

So, I have this account that has services with specific tags on them that allow them to be provisioned to a particular box.

I only want to populate an error message in the last column if I don't have any equipment that fulfill the tag on any particular service; however, I am unclear on how to do my 'counting' because I need to compare the services against the cat_component column first and then compare any remaining services and equipment against the eqp_cat column. Any services remaining at that point should get the error.

In the example above, the BA001 service would attach to the A/CC/01 equipment, the HR608 service would attach to the B/03 equipment, the first EM100 service would attach to the A/DG/05 equipment and the second EM100 service would attach to the C/DG/05 equipment. The EZ026 service would be the remaining service without equipment to attach to and should get the error message.

I have tried looking on the internet and trying to do things with RowNum and Count to no avail. Thus, I am reaching out for any help that anyone might have. Thank you, in advance for your help.

Here is my query as it is written today. You can see I am just returning an error message on every service whether or not it is in error.

--ISP05E SERVICES NEED MORE EQUIPMENT
--A basic service or additional outlet service has an outlet specified to split to but the outlet does not have any equipment or placeholder associated to it.
--A basic service or additional outlet service has a category/component requirement that does not match any of the equipment’s components.

-----This cte pulls back accounts on the item split report based on the ISPxxE errors. These accounts are then fed into the main queries below.-----
with
cte0 as
(
SELECT
row_number() over(order by [acct_no] asc) as "RowNumber"
,[isp_return_code]
,[description]
,[acct_no]
FROM [report].[item_split_results] a
join [trn].[sub_base] b on a.[acct_no] = b.[account_number] and b.[vip_flag] in ('')
WHERE [isp_return_code] like 'ISP05E'
),
cte1 as
(
SELECT *
FROM cte0
WHERE RowNumber <= 50000
)

-----This query pulls back the NOS01/03 service information for the account. It excludes Eqp_Req 'Z' services since they are not evaluated for item split.-----
SELECT
x.RowNumber
,x.[isp_return_code] "isp_return_code"
,x.[description] "description"
,a.[acct_sub] "acct_sub/location"
,cast(a.[sid] as char(5)) "sid/outlet"
,a.[service_code] "service_code/eqp_typ"
,b.[cat_component] "cat_component/eqp_cmp(cat)"
,'' "eqp_cat"
-----ISP05E - SERVICES NEED MORE EQUIPMENT-----
,case when b.[cat_component] = '01' then 'Confirm there is Eqp/Ph with cmp/cat of 01 on account.'
when b.[cat_component] = '03' then 'Confirm there is Eqp/Ph with cmp/cat of 03 on account.'
when b.[cat_component] = '04' then 'Confirm there is Eqp/Ph with cmp/cat of 04 on account.'
when b.[cat_component] = '05' then 'Confirm there is Eqp/Ph with cmp/cat of 05 on account.'
when b.[cat_component] = 'AP' then 'Confirm there is Eqp/Ph with cmp/cat of AP on account.'
when b.[cat_component] = 'CD' then 'Confirm there is Eqp/Ph with cmp/cat of CD on account.'
when b.[cat_component] = 'CV' then 'Confirm there is Eqp/Ph with cmp/cat of CV on account.'
when b.[cat_component] = 'DG' then 'Confirm there is Eqp/Ph with cmp/cat of DG on account.'
when b.[cat_component] = 'MI' then 'Confirm there is Eqp/Ph with cmp/cat of MI on account.'
when b.[cat_component] = 'MR' then 'Confirm there is Eqp/Ph with cmp/cat of MR on account.'
when b.[cat_component] = 'MW' then 'Confirm there is Eqp/Ph with cmp/cat of MW on account.'
when b.[cat_component] = 'PR' then 'Confirm there is Eqp/Ph with cmp/cat of PR on account.'
when b.[cat_component] = 'SP' then 'Confirm there is Eqp/Ph with cmp/cat of SP on account.'
when b.[cat_component] = 'VA' then 'Confirm there is Eqp/Ph with cmp/cat of VA on account.'
when b.[cat_component] = 'VT' then 'Confirm there is Eqp/Ph with cmp/cat of VT on account.' else '' end "ISP05E_remedy"

FROM [trn].[item] a
left join [ccsr].[services] b on concat(a.[system],a.[prin],a.[agent]) = b.[spaId] and a.[service_code] = b.[service_code]
join cte1 x on a.[acct_sub] = x.[acct_no]

WHERE b.[conv_rqd_flag] <> 'Z'
and b.[nature_of_service_flag] in ('01','03')

UNION ALL

-----These two queries pull back the equipment and placeholders on the account.-----
-----This query pulls back the equipment on the account.-----
SELECT
x.RowNumber
,x.[isp_return_code] "isp_return_code"
,x.[description] "description"
,a.[location] "acct_sub/location"
,a.[outlet] "sid/outlet"
,a.[type] "service_code/eqp_typ"
,a.[rec_type] "cat_component/eqp_cmp(cat)"
,isnull(b.[equipment_cat],'') "eqp_cat"
,'' "ISP05E_remedy"

FROM [trn].[equip_base] a
left join [ccsr].[etl_equipment_type_comp] b on concat(a.[sys],a.[prin],a.[agt_r]) = b.[spaid] and a.[type] = b.[equip_type] and a.[rec_type] = b.[comp_def]
join cte1 x on a.[location] = x.[acct_no]

UNION ALL

-----This query pulls back the placeholders on the account.-----
SELECT
x.RowNumber
,x.[isp_return_code] "isp_return_code"
,x.[description] "description"
,a.[location]
,a.[outlet_id] "sid/outlet"
,a.[equip_type] "service_code/eqp_typ"
,a.[component] "cat_component/eqp_cmp(cat)"
,'' "eqp_cat"
,'' "ISP05E_remedy"

FROM [trn].[leq] a
join cte1 x on a.[location] = x.[acct_no]

WHERE
a.[serial_number] = '*'

ORDER BY 1,5
;

Answer 1

Hi @Matt Hardebeck ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Please refer below example and check whether it is helpful to you.

create table reports  
(  
eqpsort int,  
rownumber int,  
sid varchar(20),  
service_code varchar(10),  
cat_component char(2),  
eqp_cmp char(2))  
  
insert into reports values  
(1,28,'A','S1','CC','01'),  
(1,28,'A','S1','DG','05'),  
(1,28,'B','MY','03',null),  
(1,28,'C','B3','CC','01'),  
(1,28,'C','B3','DG','05'),  
(2,28,'22001','BA001','01',NULL),  
(2,28,'22004','HR608','03',NULL),  
(2,28,'22008','EZ026','05',NULL),  
(2,28,'22013','EM100','DG',NULL),  
(2,28,'22014','EM100','DG',NULL)  
  
;WITH CTE AS (  
SELECT *,ROW_NUMBER() OVER (PARTITION BY eqpsort,rownumber,service_code,cat_component ORDER BY SID) RN1 FROM reports WHERE eqpsort=2)  
,CTE1 AS (  
SELECT *,ROW_NUMBER() OVER (PARTITION BY eqpsort,rownumber,cat_component,eqp_cmp ORDER BY SID) RN2 FROM reports WHERE eqpsort=1)  
,CTE2 AS (  
SELECT A.*,B.sid SID1,B.service_code service_code2,B.cat_component cat_component2,B.eqp_cmp eqp_cmp2  
FROM CTE A INNER JOIN CTE1 B  
ON A.cat_component=B.cat_component AND A.RN1=B.RN2 )  
,CTE3 AS (  
SELECT eqpsort,rownumber,SID,service_code,cat_component,eqp_cmp,SID1,service_code2,cat_component2,eqp_cmp2 FROM CTE2  
UNION   
SELECT A.eqpsort,A.rownumber,A.SID,A.service_code,A.cat_component,A.eqp_cmp,B.SID,B.service_code,B.cat_component,B.eqp_cmp  
FROM CTE A INNER JOIN CTE1 B  
ON A.cat_component=B.eqp_cmp AND A.RN1=B.RN2  
WHERE NOT EXISTS   
(SELECT 1 FROM CTE2 C WHERE B.cat_component=C.cat_component2 AND B.eqp_cmp=C.eqp_cmp2))  
 SELECT A.eqpsort,A.rownumber,A.service_code,A.cat_component,A.eqp_cmp  
 ,B.SID1,B.service_code2,B.cat_component2,B.eqp_cmp2  
 FROM CTE A  
 LEFT JOIN CTE3 B ON A.SID=B.SID  

Output:

eqpsort	rownumber	service_code	cat_component	eqp_cmp	SID1	service_code2	cat_component2	eqp_cmp2  
2	28	BA001	01	NULL	A	S1	CC	01  
2	28	HR608	03	NULL	B	MY	03	NULL  
2	28	EZ026	05	NULL	NULL	NULL	NULL	NULL  
2	28	EM100	DG	NULL	A	S1	DG	05  
2	28	EM100	DG	NULL	C	B3	DG	05  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for your response, Melissa. I will take a look and let you know if I need further clarification, but I believe I understand what you did here.