Solved

sql eliminate duplicates

Posted on 2014-10-21
5
59 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 32

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 32

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 32

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now