June 25, 2016 at 1:33 pm
JoNTSQLSrv (6/25/2016)
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
so..I'll ask again
Actually , i need to incorparate some more logic like if Agent is single then for him different title if Agent company then different title.
If you dont provide all the necessary rules...how can you expect us to help you?
Please expand your dataset and provide your expected results.
also.....please explain while you are insisting on using a "while loop".?
why do you think is necessary?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 26, 2016 at 8:32 am
J Livingston SQL (6/25/2016)
JoNTSQLSrv (6/25/2016)
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
so..I'll ask again
Actually , i need to incorparate some more logic like if Agent is single then for him different title if Agent company then different title.
If you dont provide all the necessary rules...how can you expect us to help you?
Please expand your dataset and provide your expected results.
also.....please explain while you are insisting on using a "while loop".?
why do you think is necessary?
Dear J Livingston SQL GURU,
I have edited the main post with all the logic and i have added appropriate desire result . Please check and help me .... 1 night left for me
I asked for while loop so that if they add some extra logic i will be able to add those along with the existing main logic .. thanks a lot
June 26, 2016 at 10:11 am
sorry J Livingston SQL GURU,
previously i put wrong data in urgent, please check now in the main post .. it is perfect data .. please help me 1 night left 🙁
June 26, 2016 at 11:35 am
JoNTSQLSrv (6/26/2016)
sorry J Livingston SQL GURU,previously i put wrong data in urgent, please check now in the main post .. it is perfect data .. please help me 1 night left 🙁
so what have you tried that isnt working>?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 26, 2016 at 11:51 am
J Livingston SQL (6/26/2016)
JoNTSQLSrv (6/26/2016)
sorry J Livingston SQL GURU,previously i put wrong data in urgent, please check now in the main post .. it is perfect data .. please help me 1 night left 🙁
so what have you tried that isnt working>?
Yes, J Livingston SQL GURU , i tried your query with the new data set , but i am not able to connect s.Is_sole_seller logic into it 🙁
itred some thing like this
WITH cte as (
SELECT s.SellerID,
MAX(c.Mincredit) AS mc
FROM Seller_Table AS s
left JOIN Credit_Mater AS c
ON isnull(s.TotalcreditEarned,0) >= isnull(c.Mincredit,0)
and s.party = c.Party
where s.party = 1
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
left JOIN Credit_Mater c ON cte.mc = c.Mincredit
and c.Party = 1
left join Seller_Table s
on s.SellerID = cte.SellerID
and c.Party = 1
where s.Is_sole_seller = 1
and c.Title in ('lead seller',
'Medium Seller',
'small seller',
'1* Sole seller',
'2* Sole seller',
'Dont mind seller')
it does nt work 🙁
result
SellerIDTitle
21* Sole seller
I am geting only one value instead of 7 value
please help me bring that desire result GURU
June 26, 2016 at 11:54 am
JoNTSQLSrv (6/26/2016)
it does nt work 🙁
Be specific. What exactly is wrong? Wrong results? If so, where and what should the results be? Throws an error? If so, then what error?
The people posting are all volunteers, taking time out of their own weekend to help you for free. The easier you make it for them, the more likely you'll get help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2016 at 12:22 pm
GilaMonster (6/26/2016)
JoNTSQLSrv (6/26/2016)
it does nt work 🙁Be specific. What exactly is wrong? Wrong results? If so, where and what should the results be? Throws an error? If so, then what error?
The people posting are all volunteers, taking time out of their own weekend to help you for free. The easier you make it for them, the more likely you'll get help.
I am extremly sorry "GilaMonster" i will put the result and explain them what s wrong
June 26, 2016 at 12:30 pm
JoNTSQLSrv (6/26/2016)
GilaMonster (6/26/2016)
JoNTSQLSrv (6/26/2016)
it does nt work 🙁Be specific. What exactly is wrong? Wrong results? If so, where and what should the results be? Throws an error? If so, then what error?
The people posting are all volunteers, taking time out of their own weekend to help you for free. The easier you make it for them, the more likely you'll get help.
I am extremly sorry "GilaMonster" i will put the result and explain them what s wrong
please repost your whole sample data and expected results....if you keep editing you original it gets confusing
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 26, 2016 at 1:33 pm
J Livingston SQL (6/26/2016)
JoNTSQLSrv (6/26/2016)
GilaMonster (6/26/2016)
JoNTSQLSrv (6/26/2016)
it does nt work 🙁Be specific. What exactly is wrong? Wrong results? If so, where and what should the results be? Throws an error? If so, then what error?
The people posting are all volunteers, taking time out of their own weekend to help you for free. The easier you make it for them, the more likely you'll get help.
I am extremly sorry "GilaMonster" i will put the result and explain them what s wrong
please repost your whole sample data and expected results....if you keep editing you original it gets confusing
I am extremly sorry J Livingston SQL GURU,
please take the present insert script and present expected result output . now it is prefect .. no confusion 🙂 I hope you will bring the sample output that i am trying to get :).. thanks in advance
You want me to post in this reply ? the new dataset and sample output ? here it is
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 |
+----------+------------+------------------+-------------------+----------------+-------+
June 27, 2016 at 3:38 am
JoNTSQLSrv (6/26/2016)
J Livingston SQL (6/26/2016)
JoNTSQLSrv (6/26/2016)
GilaMonster (6/26/2016)
JoNTSQLSrv (6/26/2016)
it does nt work 🙁Be specific. What exactly is wrong? Wrong results? If so, where and what should the results be? Throws an error? If so, then what error?
The people posting are all volunteers, taking time out of their own weekend to help you for free. The easier you make it for them, the more likely you'll get help.
I am extremly sorry "GilaMonster" i will put the result and explain them what s wrong
please repost your whole sample data and expected results....if you keep editing you original it gets confusing
I am extremly sorry J Livingston SQL GURU,
please take the present insert script and present expected result output . now it is prefect .. no confusion 🙂 I hope you will bring the sample output that i am trying to get :).. thanks in advance
You want me to post in this reply ? the new dataset and sample output ? here it is
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 |
+----------+------------+------------------+-------------------+----------------+-------+
I dont follow your expected results.......for example
3 | Agent3 | Grand seller | 700000
can you please explain in words how you reach this result
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 27, 2016 at 4:42 am
J Livingston SQL (6/27/2016)
JoNTSQLSrv (6/26/2016)
J Livingston SQL (6/26/2016)
JoNTSQLSrv (6/26/2016)
GilaMonster (6/26/2016)
JoNTSQLSrv (6/26/2016)
it does nt work 🙁Be specific. What exactly is wrong? Wrong results? If so, where and what should the results be? Throws an error? If so, then what error?
The people posting are all volunteers, taking time out of their own weekend to help you for free. The easier you make it for them, the more likely you'll get help.
I am extremly sorry "GilaMonster" i will put the result and explain them what s wrong
please repost your whole sample data and expected results....if you keep editing you original it gets confusing
I am extremly sorry J Livingston SQL GURU,
please take the present insert script and present expected result output . now it is prefect .. no confusion 🙂 I hope you will bring the sample output that i am trying to get :).. thanks in advance
You want me to post in this reply ? the new dataset and sample output ? here it is
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 |
+----------+------------+------------------+-------------------+----------------+-------+
I dont follow your expected results.......for example
3 | Agent3 | Grand seller | 700000can you please explain in words how you reach this result
Sorry for my improper explaination. Now i will try my level best to explain clearly with select statement
select * From Seller_Table where Is_sole_seller is null
if 'Is_sole_seller' is null then that seller is company and he can have title only with
below query results.
select * from Credit_Mater where Party = 1 and Title in (
'lead seller',
'Medium Seller',
'small seller',
'Grand seller',
'seller',
'Dont mind seller')
since Agent3 has 700,000 credit so he fits into Grand seller which has max mincredit 100,000
Like wise Is_sole_seller =1 is solo seller (single man not company )
select * From Seller_Table where Is_sole_seller =1
so title for Is_sole_seller =1 should attain title only within bellow result.
select * from Credit_Mater where Party = 1 and Title in (
'lead seller',
'Medium Seller',
'small seller',
'1* Sole seller',
'2* Sole seller',
'Dont mind seller')
so "Agent2" attains title "1* Sole seller" which has mincredit 30000.
Hope now you should understand the logic. 🙂 Thanks
June 27, 2016 at 8:31 am
I am sorry to have to say this, but when it takes that much effort to explain what you need then it goes way beyond the amount of time virtually all of us (as Gail stated) volunteers are willing to spend to help out on a single post. I recommend you get your company to hire a consultant for a short engagement to really dig in and understand your data and data processing needs and develop a workable (and hopefully efficient) solution for you.
I will add that it seems your company may have done you a disservice by throwing you such a complex problem having just come out of a support role.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 27, 2016 at 8:49 am
I see in Credit_Mater table you have now added a second set of data for "Party" = 2......what is the significance of this please?
Actually.... is this a real world table that the business uses or is this something you have developed to assist in creating a solution?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 27, 2016 at 10:10 am
J Livingston SQL (6/27/2016)
I see in Credit_Mater table you have now added a second set of data for "Party" = 2......what is the significance of this please?Actually.... is this a real world table that the business uses or is this something you have developed to assist in creating a solution?
Dear J Livingston SQL GURU,
They conduct diferent parties in different dates, just to show you i added party 2 in credit_master, if you want just delete it , by
Delete from credit_master where party = 2
if its is confusing for you
June 27, 2016 at 10:16 am
JoNTSQLSrv (6/27/2016)
J Livingston SQL (6/27/2016)
I see in Credit_Mater table you have now added a second set of data for "Party" = 2......what is the significance of this please?Actually.... is this a real world table that the business uses or is this something you have developed to assist in creating a solution?
Dear J Livingston SQL GURU,
They conduct diferent parties in different dates, just to show you i added party 2 in credit_master, if you want just delete it , by
Delete from credit_master where party = 2if its is confusing for you
different parties in different dates.......is this likely going to be another join/filter?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply