June 24, 2016 at 3:55 am
Dear All,
Hope all are good. I am Sql developer but i was in support ,now in developement, i am able to undestand complex query but not able to develop Sp or function according to the business requirement.
now in new company i got one requirement to develop job SP which has to dynamically update values.
Requirement :
5000 sellers(selling our product, they are like agent ) each of them has earned credit and they will send it will be sent n excel,we need to Identify in which level or title (lead seller,Medium Seller,small seller) the seller is suitable using their credit .
for example
DROP TABLE Seller_Table;
CREATE TABLE Seller_Table(
SellerID INTEGER NOT NULL
,SellerName VARCHAR(100) NOT NULL
,Title VARCHAR(100) NULL
,TotalcreditEarned INT,
Is_sole_seller int,
party int
);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (1,'Agent1',NULL,60000,null,1);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (2,'Agent2',NULL,30000,1,1);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (3,'Agent3',NULL,700000,null,1);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (4,'Agent4',NULL,10000,1,1);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (5,'Agent5',NULL,0,1,1);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (6,'Agent6',NULL,null,null,1);
INSERT INTO Seller_Table(SellerID,SellerName,Title,TotalcreditEarned,Is_sole_seller,party) VALUES (7,'Agent7',NULL,null,1,1);
--Seller_Table.Is_sole_seller is flag, if it is 1 = solo seller, NULL = company
--Is_sole_seller= 1 for solo seller - he is not company but single man, so if agent is sole then his title should come only with in ('lead seller' or 'Medium Seller or 'small seller' or '1* Sole seller' o '2* Sole seller' or 'Dont mind seller')
--Is_sole_seller= NULL for company - he is company not single man, company should attain title only with in ('lead seller' or 'Medium Seller or 'small seller' or 'Grand seller' or 'seller' or 'Dont mind seller')
DROP TABLE Credit_Mater
CREATE TABLE Credit_Mater(
Party int,
Title VARCHAR(100) not NULL
,Mincredit INTEGER NULL
,Tolerance INTEGER NULL
,Cashaward INTEGER NULL
,ForParty VARCHAR(100) NULL
);
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'lead seller',60000,25,10000,'4 member Guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'Medium Seller',40000,50,8000,'2 member Guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'small seller',35000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'1* Sole seller',30000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'2* Sole seller',50000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'Grand seller',100000,100,9000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'seller',100,100,200,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (1,'Dont mind seller',null,null,null,null);
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'lead seller',140000,25,10000,'4 member Guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'Medium Seller',440000,50,8000,'2 member Guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'small seller',820000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'1* Sole seller',320000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'2* Sole seller',50000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'Grand seller',10000,100,5000,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'seller',100,100,200,'No guest+1');
INSERT INTO Credit_Mater(Party,Title,Mincredit,Tolerance,Cashaward,ForParty) VALUES (2,'Dont mind seller',null,null,null,null);
--Seller_Table.Is_sole_seller is flag, if it is 1 = solo seller, NULL = company
--Is_sole_seller= 1 for solo seller - he is not company but single man, so if agent is sole then his title should come only with in ('lead seller' or 'Medium Seller or 'small seller' or '1* Sole seller' o '2* Sole seller' or 'Dont mind seller')
--Is_sole_seller= NULL for company - he is company not single man, company should attain title only with in ('lead seller' or 'Medium Seller or 'small seller' or 'Grand seller' or 'seller' or 'Dont mind seller')
Sp or function that i am writing should find the 'title' or level of seller and update in the above 'Seller_Table'
so, i have to check each seller total credit and campare with different mincredit and should find which title he is suitable and then need to update 'Seller_Table' with siutable 'title' for all seller.
Expected output:
+----------+------------+------------------+-------------------+----------------+-------+
| SellerID | SellerName | Title | TotalcreditEarned | Is_sole_seller | party |
+----------+------------+------------------+-------------------+----------------+-------+
| 1 | Agent1 | lead seller | 60000 | NULL | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
| 2 | Agent2 | 1* Sole seller | 30000 | 1 | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
| 3 | Agent3 | Grand seller | 700000 | NULL | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
| 4 | Agent4 | Not eligible | 10000 | 1 | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
| 5 | Agent5 | Not eligible | 0 | 1 | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
| 6 | Agent6 | Dont mind seller | NULL | NULL | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
| 7 | Agent7 | Dont mind seller | NULL | 1 | 1 |
+----------+------------+------------------+-------------------+----------------+-------+
so how to do it in most fast and effective way ?
should i use SP or function ?
please give me some algorithm to incorporate this logic
Thanks in advance , sorry if this is noob question. And please give me book suggestion to improve logical thinking in SQL . Sorry for my English
June 24, 2016 at 7:44 am
1) You should always look for set-based solutions and avoid "looping" of any kind if you can avoid it.
2) Avoid scalar and multi-statement table valued functions at almost any cost. They are HORRIBLY bad!
3) Please give us create table statements, inserts and expected data from the test data you provide us so we can make sure we have a query that meets your objectives.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 24, 2016 at 9:37 am
Thank you very much TheSQLGuru. Please help me. As your name says you are SQL GURU 🙂
June 24, 2016 at 10:28 am
JoNTSQLSrv (6/24/2016)
Thank you very much TheSQLGuru. Please help me. As your name says you are SQL GURU 🙂
suggest you read this article https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
and then provide what Kevin asked for
3) Please give us create table statements, inserts and expected data from the test data you provide us so we can make sure we have a query that meets your objectives.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 24, 2016 at 7:09 pm
J Livingston SQL (6/24/2016)
JoNTSQLSrv (6/24/2016)
Thank you very much TheSQLGuru. Please help me. As your name says you are SQL GURU 🙂suggest you read this article https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
and then provide what Kevin asked for
3) Please give us create table statements, inserts and expected data from the test data you provide us so we can make sure we have a query that meets your objectives.
Thanks Livingston, I hope i edited as you have tolf. please check and give me effective way got checking and best algorithm ..thanks
June 25, 2016 at 9:52 am
Hi all ,
Any help from any one ... 1 day left for me to do this
June 25, 2016 at 10:04 am
JoNTSQLSrv (6/25/2016)
Hi all ,Any help from any one ... 1 day left for me to do this
based on your edited first post...with the code (thanks) please post what you expect the results to be.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 25, 2016 at 10:47 am
maybe.....???
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c ON s.TotalcreditEarned >= c.Mincredit
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
c.Title
FROM cte
INNER JOIN Credit_Mater c ON cte.mc = c.Mincredit;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 25, 2016 at 11:02 am
J Livingston SQL (6/25/2016)
maybe.....???
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c ON s.TotalcreditEarned >= c.Mincredit
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
c.Title
FROM cte
INNER JOIN Credit_Mater c ON cte.mc = c.Mincredit;
Thanks 'J Livingston SQL' but is this correct ? wil this work in all the situation scenario? how to bring "Not eligible" along with this result?
but reallly i appreciate your thinking capacity.
I was actually trying to use while loop : passing all the totalcredit and check in whic title they will come .. but its really hard Guru
June 25, 2016 at 11:05 am
JoNTSQLSrv (6/25/2016)
J Livingston SQL (6/25/2016)
maybe.....???
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c ON s.TotalcreditEarned >= c.Mincredit
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
c.Title
FROM cte
INNER JOIN Credit_Mater c ON cte.mc = c.Mincredit;
Thanks 'J Livingston SQL' but is this correct ? wil this work in all the situation scenario? how to bring "Not eligible" along with this result?
but reallly i appreciate your thinking capacity.
I was actually trying to use while loop : passing all the totalcredit and check in whic title they will come .. but its really hard Guru
SELECT cte.SellerID,
ISNULL(c.Title,'Non Eligible')
FROM cte
LEFT JOIN Credit_Mater c ON cte.mc = c.Mincredit;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 25, 2016 at 12:04 pm
J Livingston SQL (6/25/2016)
JoNTSQLSrv (6/25/2016)
J Livingston SQL (6/25/2016)
maybe.....???
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c ON s.TotalcreditEarned >= c.Mincredit
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
c.Title
FROM cte
INNER JOIN Credit_Mater c ON cte.mc = c.Mincredit;
Thanks 'J Livingston SQL' but is this correct ? wil this work in all the situation scenario? how to bring "Not eligible" along with this result?
but reallly i appreciate your thinking capacity.
I was actually trying to use while loop : passing all the totalcredit and check in whic title they will come .. but its really hard Guru
SELECT cte.SellerID,ISNULL(c.Title,'Non Eligible')
FROM cte
LEFT JOIN Credit_Mater c ON cte.mc = c.Mincredit;
wooooow you have very good application knowledge, even i know left join and isnull, but i could not think in this way.
Sorry i missed out telling tolerance column login in credit_master; actually we need to consider not only Mincredit but (Mincredit&Tolerance) together to with this logic ( c.Mincredit * c.Tolerance / 100)
some thing like below sql code
s.TotalcreditEarned >= CASE WHEN ISNULL(c.Mincredit,0) = 0 THEN c.Mincredit ELSE (c.Mincredit * c.Tolerance / 100) END
so i changed your query like below
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c
ON s.TotalcreditEarned >= CASE WHEN ISNULL(c.Mincredit,0) = 0 THEN c.Mincredit ELSE (c.Mincredit * c.Tolerance / 100) END
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
ISNULL(c.Title,'Non Eligible')
FROM cte
LEFT JOIN Credit_Mater c ON cte.mc = c.Mincredit;
is this correct ?
June 25, 2016 at 12:09 pm
J Livingston SQL (6/25/2016)
JoNTSQLSrv (6/25/2016)
J Livingston SQL (6/25/2016)
maybe.....???
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c ON s.TotalcreditEarned >= c.Mincredit
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
c.Title
FROM cte
INNER JOIN Credit_Mater c ON cte.mc = c.Mincredit;
Thanks 'J Livingston SQL' but is this correct ? wil this work in all the situation scenario? how to bring "Not eligible" along with this result?
but reallly i appreciate your thinking capacity.
I was actually trying to use while loop : passing all the totalcredit and check in whic title they will come .. but its really hard Guru
SELECT cte.SellerID,ISNULL(c.Title,'Non Eligible')
FROM cte
LEFT JOIN Credit_Mater c ON cte.mc = c.Mincredit;
wooooow you have very good application knowledge(GURU), even i know left join and isnull, but i could not think this way.
Sorry i missed out telling tolerance column login in credit_master; actually we need to consider not only Mincredit but (Mincredit&Tolerance) together in this logic ( c.Mincredit * c.Tolerance / 100)
some thing like below sql code
s.TotalcreditEarned >= CASE WHEN ISNULL(c.Mincredit,0) = 0 THEN c.Mincredit ELSE (c.Mincredit * c.Tolerance / 100) END
so i changed your query like below
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
LEFT OUTER JOIN Credit_Mater AS c
ON s.TotalcreditEarned >= CASE WHEN ISNULL(c.Mincredit,0) = 0 THEN c.Mincredit ELSE (c.Mincredit * c.Tolerance / 100) END
GROUP BY s.SellerID
)
-- if these are the results you require then alter following code to UPDATE statement
SELECT cte.SellerID,
ISNULL(c.Title,'Non Eligible')
FROM cte
LEFT JOIN Credit_Mater c ON cte.mc = c.Mincredit;
is this correct ?
June 25, 2016 at 12:28 pm
I am sorry...but I cannot tell you if its correct......you have to tell us.
your sample dataset is small ...perhaps if you expand this for more than one sellerid and also include what effect the column "tolerance" has...then I ma sure you will get an answer.
Please expnad your dataset and provide your expected results.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 25, 2016 at 1:21 pm
J Livingston SQL (6/25/2016)
I am sorry...but I cannot tell you if its correct......you have to tell us.your sample dataset is small ...perhaps if you expand this for more than one sellerid and also include what effect the column "tolerance" has...then I ma sure you will get an answer.
Please expnad your dataset and provide your expected results.
Any how even i am confused with tolerance J Livingston SQL GURU, so we will leave that tolerance GURU, let take only total credit.
Actually , i need to incorparate some more logic like if Agent is single then for him different title if Agent company then different title.
so lastly , i kindly request you to convert that cte query that you gave to while loop query, which uses table variable , goto statement, break continue statments. I mean to say using while loop approach by taking agent total credit and comparing one by one in credit_master table and then assigning title , so if i get this structure then i can include some of the logic's in it ...
kind of same result in while loop instead cte
sorry if i am wrongly requesting
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply