how to improvise code

I have almost 30 CTE's like this which involve changing just the category number, at the end I have to update a table and set a groupnumber to 1or 2 or 3 if a customer falls in any of the CTE's. Is there a better way to achieve this instead of having this CTE copy pasted 30 times? Pls help
CTEDealers AS(

SELECT DISTINCT
       CT.Customer_No
       
  FROM Hade.dbo.HCC M WITH (NOLOCK)
  JOIN Hade..HCC_CustomerTypes CT WITH (NOLOCK)
    ON M.Customer_No = CT.Customer_No
  JOIN HN.dbo.sales S WITH (NOLOCK)
    ON CT.Customer_no = S.Customer_no
 WHERE CT.Category IN (465,-- Fine Art
                      )  
   AND S.prices_realized IS NOT NULL
   AND S.Buy_Back =0            
UNION  
SELECT DISTINCT
       CT.Customer_No
  FROM Hade.dbo.HCC M WITH (NOLOCK)
  JOIN Hade..HCC_CustomerTypes CT WITH (NOLOCK)
    ON M.Customer_No = CT.Customer_No  
 WHERE CT.Category IN (167, --  Art Dealers
                       )
sqlcuriousAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
If you mean this line:
WHERE CT.Category IN (465,-- Fine Art
                      )  

I'm not sure what 465,--Fine Art is, but if that is all one field value, it is a poor choice as it contains commas. I'd make the Categories have IDs. Category ID for this one would be 465 if that is unique. For the other I'd make the CategoryID be 167. Then you could just do

WHERE CT.Category IN (465,167)  

And so forth, separating only be comma.
0
Scott PletcherSenior DBACommented:
I'm using the SELECT structure below with multiple separate MAX() lines in case the same customer can have multiple categories: if one cust can't have multiple categs, it can be simplified further:

SELECT
        CT.Customer_No,
        MAX(CASE WHEN CT.Category IN (465 /*,...*/) THEN 1 ELSE 0 END) AS Is_Fine_Art,
        MAX(CASE WHEN CT.Category IN (167 /*,...*/) THEN 1 ELSE 0 END) AS Is_Art_Dealer,
        ...

       
   FROM Hade.dbo.HCC M WITH (NOLOCK)
   JOIN Hade..HCC_CustomerTypes CT WITH (NOLOCK)
     ON M.Customer_No = CT.Customer_No
   LEFT OUTER JOIN HN.dbo.sales S WITH (NOLOCK)
     ON CT.Customer_no = S.Customer_no
   ...
0
awking00Information Technology SpecialistCommented:
It looks like more than the category number is changing. The first select is joining to the sales table and the second select does not. What other conditions might exist in those 30 CTEs? What are you doing with the CTE once it's been created? It may be that you could create only one generic CTE and include the necessary filters in the query of it.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

awking00Information Technology SpecialistCommented:
For example using your scenario, create a CTE as a select customer_no joining the two tables, Hade.dbo.HCC and Hade..HCC_CustomerTypes where the category is in (167,465) then query the CTE where not exists (select 1 from HN.dbo.sales  s where s.customer_no = cte.customer_no and (s.prices_realized is null or s.buy_back <> 0). It's probable that you will need to meet additional conditions but they may not be as cumbersome as 30 select statements.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
sqlcurious, do you still need help with this question?
0
sqlcuriousAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.