Solved

sql eliminate duplicates

Posted on 2014-10-21
5
81 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

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 34

Accepted Solution

by:
ste5an earned 500 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 34

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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