Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

sql eliminate duplicates

I ma trying to eliminate duplicate from my query. This is the promo code system when I need award a person with a discount

but i do get a different orders and cannot use a distinct
WITH Promo50_CTE (PromoCodeTrackerID, CodeID, CreatedBy,CreatedDate,transID) AS
							(
							SELECT t.PromoCodeTrackerID, t.CodeID, t.createdBy,t.createdDate,t.transID
							FROM PromoCodeTracker t
							INNER JOIN PromoCodeTrackerUse u ON (t.PromoCodeTrackerID = u.PromoCodeTrackerID)
							WHERE NOT EXISTS (SELECT 1 FROM PromoCodeTrackerUse u2 WHERE t.createdBy=u2.UsedBy)
							and (t.isDiscountIssued != 'Y'  OR t.isDiscountIssued is NULL) 
							),
							Promo25_CTE (PromoCodeTrackerID, CodeID, CreatedBy,CreatedDate,transID) AS
							(
							SELECT t.PromoCodeTrackerID, t.CodeID, t.createdBy,u.transID,t.createdDate
							FROM PromoCodeTracker t
							INNER JOIN PromoCodeTrackerUse u ON (t.PromoCodeTrackerID = u.PromoCodeTrackerID)
							WHERE EXISTS (SELECT 1 FROM PromoCodeTrackerUse u2 WHERE t.createdBy=u2.UsedBy)
							and (t.isDiscountIssued != 'Y'  OR t.isDiscountIssued is NULL) 
							)
							
							
							
							SELECT DISTINCT c.createdBy,c.promoCodeTrackerID,c.createdDate, 50 AS prize,U.firstname,u.lastname,TR.validated, LEFT(ProductList, LEN(ProductList) - 1) AS ProductList,O.orderKey,
							(
				                SELECT TOP 1 LAS.availStateCode
				                FROM lineitems LI WITH (NOLOCK)
				                    INNER JOIN lkup_availState LAS WITH (NOLOCK) ON LI.officeStateKey = LAS.availStateKey
				                WHERE O.orderKey = LI.orderKey
				            ) AS availStateCode
            
							FROM PromoCodeTracker t
							INNER JOIN Promo50_CTE c ON (c.PromoCodeTrackerID=t.PromoCodeTrackerID) 
							inner join users U WITH (NOLOCK) on U.userKey = c.CreatedBy
							inner join trans TR WITH (NOLOCK) on tr.transKey=c.transID
							LEFT OUTER JOIN (
				                Orders O WITH (NOLOCK) 
				                INNER JOIN Users UO WITH (NOLOCK) ON O.userKey = UO.userKey
				            ) ON TR.orderKey = O.orderKey
            
							CROSS APPLY (
				                SELECT LI2.description + '|'
				                FROM lineitems LI2 WITH (NOLOCK)
				                WHERE O.orderKey = LI2.orderKey
				                ORDER BY LI2.description
				                FOR XML PATH('')
				            ) AS Cross1(ProductList)    
							GROUP BY c.createdBy,c.promoCodeTrackerID,c.createdDate ,U.firstname,u.lastname,TR.validated,ProductList,tr.authcode ,TR.amount,O.orderKey
							
							UNION ALL
							
							SELECT DISTINCT c.CreatedBy,c.promoCodeTrackerID,c.createdDate,  25 AS prize,U.firstname,u.lastname,TR.validated, LEFT(ProductList, LEN(ProductList) - 1) AS ProductList,O.orderKey,
							(
				                SELECT TOP 1 LAS.availStateCode
				                FROM lineitems LI WITH (NOLOCK)
				                    INNER JOIN lkup_availState LAS WITH (NOLOCK) ON LI.officeStateKey = LAS.availStateKey
				                WHERE O.orderKey = LI.orderKey
				            ) AS availStateCode
							FROM PromoCodeTrackerUse s
								INNER JOIN Promo25_CTE c ON (c.PromoCodeTrackerID=s.PromoCodeTrackerID)
								inner join users U WITH (NOLOCK) on U.userKey = c.createdBy
							inner join trans TR WITH (NOLOCK) on tr.transKey=s.transID
							LEFT OUTER JOIN (
				                Orders O WITH (NOLOCK) 
				                INNER JOIN Users UO WITH (NOLOCK) ON O.userKey = UO.userKey
				            ) ON TR.orderKey = O.orderKey
							CROSS APPLY (
				                SELECT LI2.description + '|'
				                FROM lineitems LI2 WITH (NOLOCK)
				                WHERE O.orderKey = LI2.orderKey
				                ORDER BY LI2.description
				                FOR XML PATH('')
				            ) AS Cross1(ProductList) 
							GROUP BY c.createdBy,c.promoCodeTrackerID,c.createdDate ,U.firstname,u.lastname,TR.validated,ProductList,tr.authcode,TR.amount,O.orderKey

Open in new window

Avatar of ste5an
ste5an
Flag of Germany image

It's a little bit complex. But at the first glance: I don't think that you need two promo CTE's and an UNION.

Just use

SELECT	t.PromoCodeTrackerID ,
		t.CodeID ,
		t.createdBy ,
		t.createdDate ,
		t.transID,
		CASE WHEN EXISTS ( 
			SELECT 1
			FROM   PromoCodeTrackerUse u2
			WHERE  t.createdBy = u2.UsedBy
			)
			THEN 25
			ELSE 50
		END AS Promo
FROM	PromoCodeTracker t
	INNER JOIN PromoCodeTrackerUse u ON ( t.PromoCodeTrackerID = u.PromoCodeTrackerID )
WHERE	t.isDiscountIssued != 'Y'
	OR t.isDiscountIssued IS NULL

Open in new window


to calculate the promo value.
Avatar of erikTsomik

ASKER

I am trying to work out who need to get 50 and who need to get 25
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
it seems like it works. ALso I want to change prize amount. instead of hard coding a price Can i look it up in the table called promoCode it linked to PromoCodeTracker  table by codeid  and the field is called award amount.
Why not. Create price table (Promo, Price) with (25, 100.00) and (50, 50.00) and join it with Promo column.