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?
 
awking00Commented:
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
awking00Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
sqlcurious, do you still need help with this question?
0
 
sqlcuriousAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.