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 |

+----------+------------+------------------+-------------------+----------------+-------+