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

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
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.

ste5anSenior DeveloperCommented:
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.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I am trying to work out who need to get 50 and who need to get 25
0
ste5anSenior DeveloperCommented:
And.. you haven't posted the imporant things: table DDL and sample data..

But this may work:

WITH     Promo (PromoCodeTrackerID, CodeID, CreatedBy, CreatedDate, transID)
AS       (SELECT t.PromoCodeTrackerID,
                 t.CodeID,
                 t.createdBy,
                 t.createdDate,
                 t.transID,
				 CASE WHEN EXISTS (SELECT * FROM PromoCodeTrackerUse u2 WHERE t.createdBy = u2.UsedBy)
					THEN 25
					ELSE 50
				 END AS Promo                 
          FROM   PromoCodeTracker AS t
                 INNER JOIN
                 PromoCodeTrackerUse AS u
                 ON (t.PromoCodeTrackerID = u.PromoCodeTrackerID)
          WHERE  t.isDiscountIssued != 'Y'
                 OR t.isDiscountIssued IS NULL
			)
SELECT   DISTINCT c.createdBy,
                  c.promoCodeTrackerID,
                  c.createdDate,
                  C.Promo AS prize,
                  U.firstname,
                  u.lastname,
                  TR.validated,
                  LEFT(ProductList, LEN(ProductList) - 1) AS ProductList,
                  O.orderKey,
                  (SELECT TOP 1 LAS.availStateCode
                   FROM   lineitems AS LI WITH (NOLOCK)
                          INNER JOIN
                          lkup_availState AS LAS WITH (NOLOCK)
                          ON LI.officeStateKey = LAS.availStateKey
                   WHERE  O.orderKey = LI.orderKey
				   /* HERE IS IMHO AN ORDER BY MISSING */
				   ) AS availStateCode
FROM     PromoCodeTracker AS t
         INNER JOIN Promo AS c ON (c.PromoCodeTrackerID = t.PromoCodeTrackerID)
         INNER JOIN users AS U WITH (NOLOCK) ON U.userKey = c.CreatedBy
         INNER JOIN trans AS TR WITH (NOLOCK) ON tr.transKey = c.transID
         LEFT OUTER JOIN Orders AS O WITH (NOLOCK) ON TR.orderKey = O.orderKey 
         INNER JOIN Users AS UO WITH (NOLOCK) ON O.userKey = UO.userKey
         CROSS APPLY (
			SELECT   LI2.description + '|'
            FROM     lineitems AS LI2 WITH (NOLOCK)
            WHERE    O.orderKey = LI2.orderKey
            ORDER BY LI2.description
            FOR      XML PATH ('')
			) AS Cross1(ProductList);

Open in new window

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
erikTsomikSystem Architect, CF programmer Author Commented:
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.
0
ste5anSenior DeveloperCommented:
Why not. Create price table (Promo, Price) with (25, 100.00) and (50, 50.00) and join it with Promo column.
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
Query Syntax

From novice to tech pro — start learning today.