Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql discount amout

Posted on 2014-10-16
24
Medium Priority
?
91 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
ID: 40385128
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
ID: 40385147
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
ID: 40385454
is there any updates on this
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

Expert Comment

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

Author Comment

by:erikTsomik
ID: 40386599
ID is a related field
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40386617
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
ID: 40386637
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 52

Expert Comment

by:Vitor Montalvão
ID: 40386665
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
ID: 40386674
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 52

Expert Comment

by:Vitor Montalvão
ID: 40386683
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
ID: 40386709
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 52

Expert Comment

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

Author Comment

by:erikTsomik
ID: 40386837
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 52

Expert Comment

by:Vitor Montalvão
ID: 40386853
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
ID: 40386888
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 52

Expert Comment

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

Author Comment

by:erikTsomik
ID: 40392848
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 52

Expert Comment

by:Vitor Montalvão
ID: 40393780
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
ID: 40394890
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 52

Expert Comment

by:Vitor Montalvão
ID: 40396395
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
ID: 40396849
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 52

Expert Comment

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

Author Comment

by:erikTsomik
ID: 40396862
the rest of the table is just to get more data out
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40396924
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

972 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