Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql eliminate duplicates

Posted on 2014-10-21
5
Medium Priority
?
86 Views
Last Modified: 2014-10-23
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

0
Comment
Question by:erikTsomik
  • 3
  • 2
5 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 40395019
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40395040
I am trying to work out who need to get 50 and who need to get 25
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40395436
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40395518
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40395775
Why not. Create price table (Promo, Price) with (25, 100.00) and (50, 50.00) and join it with Promo column.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question