sql discount amout

I have developed a referral promo code system. And now i need to show the discount amount to a person who will issue the discount. I have 2 tables: PromoCodeTracker and PromoCodeUSe. they linked in by ID.

The first table have a list of promo codes that been generated for each user . The second table is the use of those promo code.

The logic is : if you refer someone who make a purchase  you get $50 (only if you did not use the code to purchase)
you get $25 if you already use someone code (which already applyed to a purchase) and someone else is used your code

Please help
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Haris DulicIT ArchitectCommented:
Can you post some sample data , so that the columns available will be shown and can you make more clear what is that you need?
0
erikTsomikSystem Architect, CF programmer Author Commented:
PromoCodeTracker

ID CodeID      createdBy      createdDate      transID      NewCode
2      1      41618      2014-10-13 14:13:06.273      46305      FR10041618
3      1      42356      2014-12-01 00:00:00.000      23333      FR2345667
5      1      41448      2014-10-14 12:09:28.250      46309      FR10041448

8      2      41618      2014-10-15 16:17:06.370      46325      FR10141618
9      1      41622      2014-10-15 16:27:35.350      46326      FR10041622

PromoCodeUSe
UsageID      ID   UsedBy                        UsedWhen
1             2            41448                 2014-10-14 12:20:03.950
2            2            41622                 2014-10-15 16:27:35.363
3             3              44444                2014-10-15 16:37:35.363
0
erikTsomikSystem Architect, CF programmer Author Commented:
is there any updates on this
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Which fields relates PromoCodeUSe to PromoCodeTracker?
0
erikTsomikSystem Architect, CF programmer Author Commented:
ID is a related field
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only ID? How do I relate a user with a promotion?
In your example there are different createdBy and UsedBy for the same ID.
0
erikTsomikSystem Architect, CF programmer Author Commented:
Why do you need a user. IN PromoCodeTracker created by is id of the user where the code is been created. in the table PromoCodeUSe you have a ID that will link back to a table PromoCodeTracker  which will tell who this code belongs to and the usedBy of the person who used it.

Also the code itself contains the portion of the user who is created. For axample FR10141618 is created by 41618
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just want to understand the logic.
So the UsedBy is the referral and the CreatedBy is the person that will win the $50, right?
I just couldn't understand the $25.

Based on your example what are you expecting to be returned?
0
erikTsomikSystem Architect, CF programmer Author Commented:
OK. if you refer somebody and the person used the promo you get $50. if you refer somebody and the person used the promo and you used the promocode from somebody else you get $25, because you already apply $25 from the promocode that you used upon making a purchase
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This query gives all promo codes used, right?
SELECT * 
FROM PromoCodeTracker t
INNER JOIN PromoCodeUSe u ON (t.ID = u.ID)

Open in new window

Can you tell which one should receive $50 an which one should receive $25?
0
erikTsomikSystem Architect, CF programmer Author Commented:
41618      should get 50. and 42356      should get 25. Because 41618      refer 41448 and this guy made a purchase so 41618      should get 50.

41448 should get 25 because he refer 44444 but 41448 used the discount from 41618      so this guy should get 25 back

Well I need to modify the record set to make more obvious

PromoCodeTracker

ID CodeID      createdBy      createdDate      transID      NewCode
2      1      41618      2014-10-13 14:13:06.273      46305      FR10041618
3      1      42356      2014-12-01 00:00:00.000      23333      FR2345667
5      1      41448      2014-10-14 12:09:28.250      46309      FR10041448

8      2      41618      2014-10-15 16:17:06.370      46325      FR10141618
9      1      41622      2014-10-15 16:27:35.350      46326      FR10041622

PromoCodeUSe
UsageID      ID   UsedBy                        UsedWhen
1             2            41448                 2014-10-14 12:20:03.950
2            2            41622                 2014-10-15 16:27:35.363
3             5              44444                2014-10-15 16:37:35.363
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you check if the below SQL statement works as you expects?
WITH Promo50_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE NOT EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
),
Promo25_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
)

SELECT c.createdBy, COUNT(c.createdBy) * 50 AS prize
FROM PromoCodeTracker t
	INNER JOIN Promo50_CTE c ON (c.ID=t.ID) 
GROUP BY c.createdBy
UNION ALL
SELECT c.CreatedBy, COUNT(c.createdBy) * 25 AS prize
FROM PromoCodeUSe s
	INNER JOIN Promo25_CTE c ON (c.ID=s.ID)
GROUP BY c.createdBy

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
no it does not I get $100. One of the rule is you can only use discount once so the prson should not get more than $50
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Then there's no need for the COUNT. The prize value will be fixed:
WITH Promo50_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE NOT EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
),
Promo25_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
)

SELECT DISTINCT c.createdBy, 50 AS prize
FROM PromoCodeTracker t
	INNER JOIN Promo50_CTE c ON (c.ID=t.ID) 
UNION ALL
SELECT DISTINCT c.CreatedBy, 25 AS prize
FROM PromoCodeUSe s
	INNER JOIN Promo25_CTE c ON (c.ID=s.ID)

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
I have the code written How would I integrate what you suggesting into my code. So it would be just 1 column let say priseAmmount

Select distinct PCT.CodeID,PCT.createdBy,PCT.createdDate,PCT.NewCOde,PCT.transID,U.firstname,U.lastName,T.validated,t.authcode,T.amount,T.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
            , LEFT(ProductList, LEN(ProductList) - 1) AS ProductList
           
            from promoCodeTracker PCT
            inner join users U WITH (NOLOCK) on U.userKey = PCT.createdBy
            inner join trans T WITH (NOLOCK) on t.transKey=PCT.transID
             LEFT OUTER JOIN (
                Orders O WITH (NOLOCK)
                INNER JOIN Users UO WITH (NOLOCK) ON O.userKey = UO.userKey
            ) ON T.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)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
But this code works with another tables. Need to know the database model first.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I am just testing the code and it is not right. in Promo50_CTE  I am getting 2 people and those are the people who should get $25 credit not 50
WITH Promo50_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE NOT EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
),
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That part of code isn't for running separately. The main SELECT with DISTINCT keyword will take care of the duplicates.
0
erikTsomikSystem Architect, CF programmer Author Commented:
The problem that I have is the following I need a little bit more that just a person id and the prize amount, I need to show the orderID,. WHEn I use the distinct it will show all the orders and I only want to issue the discount once
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want the OrderID you just need to add that column in the SELECT DISTINC statement.
WITH Promo50_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE NOT EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
),
Promo25_CTE (ID, CodeID, CreatedBy, UsedBy) AS
(
SELECT t.ID, t.CodeID, t.createdBy,u.UsedBy
FROM PromoCodeTracker t
INNER JOIN PromoCodeUse u ON (t.ID = u.ID)
WHERE EXISTS (SELECT 1 FROM PromoCodeUSe u2 WHERE t.createdBy=u2.UsedBy)
)

SELECT DISTINCT c.ID, c.createdBy, 50 AS prize 
FROM PromoCodeTracker t
	INNER JOIN Promo50_CTE c ON (c.ID=t.ID) 
UNION ALL
SELECT DISTINCT c.ID, c.CreatedBy, 25 AS prize
FROM PromoCodeUSe s
	INNER JOIN Promo25_CTE c ON (c.ID=s.ID)

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
Will this code also works
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);
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
May work but I don't have enough information about all the tables and respective data.
0
erikTsomikSystem Architect, CF programmer Author Commented:
the rest of the table is just to get more data out
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I adapted it to work with the same tables that I have:
WITH Promo (PromoCodeTrackerID, CodeID, CreatedBy, CreatedDate, transID, Promo)
 AS (SELECT t.ID, t.CodeID, t.createdBy, t.createdDate, t.transID,
	    CASE WHEN 
			EXISTS (SELECT 1 FROM PromoCodeUse u2 WHERE t.createdBy = u2.UsedBy) THEN 25
           ELSE 50
		END AS Promo                 
FROM PromoCodeTracker AS t
	INNER JOIN PromoCodeUse AS u ON (t.ID = u.ID))
SELECT DISTINCT c.createdBy,
       c.promoCodeTrackerID,
       c.createdDate,
       C.Promo AS prize
FROM PromoCodeTracker AS t
  INNER JOIN Promo AS c ON (c.PromoCodeTrackerID = t.ID)

Open in new window

It returns the same records as mine solution.
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
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.