Solved

sql discount amout

Posted on 2014-10-16
24
73 Views
Last Modified: 2014-10-24
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
0
Comment
Question by:erikTsomik
  • 12
  • 11
24 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
is there any updates on this
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Which fields relates PromoCodeUSe to PromoCodeTracker?
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
ID is a related field
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
But this code works with another tables. Need to know the database model first.
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
That part of code isn't for running separately. The main SELECT with DISTINCT keyword will take care of the duplicates.
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
May work but I don't have enough information about all the tables and respective data.
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
the rest of the table is just to get more data out
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now