erikTsomik
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
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
ASKER
I am trying to work out who need to get 50 and who need to get 25
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Just use
Open in new window
to calculate the promo value.