Solved

sql discount amout

Posted on 2014-10-16
24
77 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
 
LVL 46

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 46

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 46

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 46

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 46

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 46

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 46

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 46

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 46

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 46

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 46

Accepted Solution

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 80
Near realtime alert if SQL Server services stop. 20 53
SQL Help - SELECT Statement 6 37
Running Total in Access 4 0
In this article I will describe the Backup & Restore 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.
In this article I will describe the Copy Database Wizard 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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

16 Experts available now in Live!

Get 1:1 Help Now