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
)
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
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sqlcurious, do you still need help with this question?
ASKER
thanks
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.