June 27, 2016 at 11:01 am
you can try this......in all honesty I would be looking to review your tables and make recommendations so that the initial cte below is not required.
that said....
WITH TT as (
SELECT * FROM(
SELECT 's' AS ttype,
Party,
Title,
Mincredit
FROM Credit_Mater
WHERE
(Title IN('lead seller', 'Medium Seller', 'small seller', '1* Sole seller', '2* Sole seller'))
UNION
SELECT 'c' AS ttype,
Party,
Title,
Mincredit
FROM Credit_Mater
WHERE
Title IN('lead seller', 'Medium Seller', 'small seller', 'Grand seller', 'seller')) x
)
,
cte as (
SELECT s.SellerID,
MAX(tt.Mincredit) AS mc,
MAX(s.TotalcreditEarned) tce
FROM Seller_Table AS s
LEFT OUTER JOIN tt ON s.TotalcreditEarned >= tt.Mincredit
AND s.party = tt.party
AND (CASE WHEN Is_sole_seller = 1 THEN 's' ELSE 'c' END) = tt.ttype
GROUP BY s.SellerID
)
SELECT cte.SellerID,
CASE WHEN cte.tce >=0 THEN ISNULL(c.Title,'Not Eligible') ELSE 'dont care' END
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 28, 2016 at 12:32 am
J Livingston SQL (6/27/2016)
you can try this......in all honesty I would be looking to review your tables and make recommendations so that the initial cte below is not required.that said....
WITH TT as (SELECT * FROM(
SELECT 's' AS ttype,
Party,
Title,
Mincredit
FROM Credit_Mater
WHERE
(Title IN('lead seller', 'Medium Seller', 'small seller', '1* Sole seller', '2* Sole seller'))
UNION
SELECT 'c' AS ttype,
Party,
Title,
Mincredit
FROM Credit_Mater
WHERE
Title IN('lead seller', 'Medium Seller', 'small seller', 'Grand seller', 'seller')) x
)
,
cte as (
SELECT s.SellerID,
MAX(tt.Mincredit) AS mc,
MAX(s.TotalcreditEarned) tce
FROM Seller_Table AS s
LEFT OUTER JOIN tt ON s.TotalcreditEarned >= tt.Mincredit
AND s.party = tt.party
AND (CASE WHEN Is_sole_seller = 1 THEN 's' ELSE 'c' END) = tt.ttype
GROUP BY s.SellerID
)
SELECT cte.SellerID,
CASE WHEN cte.tce >=0 THEN ISNULL(c.Title,'Not Eligible') ELSE 'dont care' END
FROM cte
LEFT JOIN Credit_Mater c ON cte.mc = c.Mincredit;
Dear J Livingston SQL GURU, no words.. YOU ARE THE BEST SQL GURU I HAVE SEEN. You did bring the result with few lines with faster execution plan . You exactly brought the result i expected bro :).. THANKS A MILLION. GOD BLESS YOU MORE FOR YOUR KIND AND HELPING HEART 🙂
Please help me improving my sql thinking according to any bsiness requirement .. help me and suggest some books or sites .. thanks in advance
June 28, 2016 at 3:41 am
Please help me improving my sql thinking according to any bsiness requirement .. help me and suggest some books or sites .. thanks in advance
suggest you stick around this site....read the questions and see how they are solved...try answering a few yourself.
...and i am far far from being a guru :blush: !
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 29, 2016 at 2:12 am
J Livingston SQL (6/28/2016)
Please help me improving my sql thinking according to any bsiness requirement .. help me and suggest some books or sites .. thanks in advance
suggest you stick around this site....read the questions and see how they are solved...try answering a few yourself.
...and i am far far from being a guru :blush: !
No, J Livingston SQL You are geneius is application, even thou everyone know all the sql concept only some one can apply in correct situation, you are one among them .... GLAD TO SEE SUCH GOOD SQL DEVELOPER.
For knowledge shake, is it possible to implement the same result in while loop ?
June 29, 2016 at 2:20 am
For knowledge shake, is it possible to implement the same result in while loop ?
why?
what benefits do you perceive you will gain by using a while loop......?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 29, 2016 at 10:35 am
J Livingston SQL (6/29/2016)
For knowledge shake, is it possible to implement the same result in while loop ?why?
what benefits do you perceive you will gain by using a while loop......?
Please stop thinking about processing one piece of data at a time and start thinking about what you want to do with the whole collection of data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 29, 2016 at 11:11 am
TheSQLGuru (6/29/2016)
J Livingston SQL (6/29/2016)
For knowledge shake, is it possible to implement the same result in while loop ?why?
what benefits do you perceive you will gain by using a while loop......?
Please stop thinking about processing one piece of data at a time and start thinking about what you want to do with the whole collection of data.
I agree with what everyone is saying, but the simple answer to your question is yes it is possible. But to reinforce what everyone is saying, it is counter productive, not performant or scalable, and something to be avoided when ever possible.
June 29, 2016 at 12:49 pm
Maybe he wants to see how painfully slow they are?
June 30, 2016 at 1:51 am
J Livingston SQL (6/29/2016)
For knowledge shake, is it possible to implement the same result in while loop ?why?
what benefits do you perceive you will gain by using a while loop......?
Yes ofcourse it is slower than cte ,but as a programmer we should be able to give same solution or result using cte or while loop or cursor right ? so it will increase our logical thinking right ?
I tried using while loop, but i am unable to bring the same result, i get struck when i check all value and fix the exact title when i use loop, so if you help me in that, then it will be usefull for me and i will learn it ... and sure it will help me in some situation
June 30, 2016 at 2:12 am
JoNTSQLSrv (6/30/2016)
J Livingston SQL (6/29/2016)
For knowledge shake, is it possible to implement the same result in while loop ?why?
what benefits do you perceive you will gain by using a while loop......?
Yes ofcourse it is slower than cte ,but as a programmer we should be able to give same solution or result using cte or while loop or cursor right ? so it will increase our logical thinking right ?
I tried using while loop, but i am unable to bring the same result, i get struck when i check all value and fix the exact title when i use loop, so if you help me in that, then it will be usefull for me and i will learn it ... and sure it will help me in some situation
sorry, but I am not convinced about your statement .....
we should be able to give same solution or result using cte or while loop or cursor right
because you have asked several times already for this to be in a while loop. You previously replied the reason was thus:
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
can I ask if this requirement is a "business rule" being imposed upon you?
my view is that you should use the right tools for the job......I can't even spell "curser" let alone write an efficent one.....so I will leave that to others to assist if they so feel.
In order to proceed further with this request, then can I ask you to post the "while loop code" you have that isnt working for you, along with sample DDL/ data and expected results
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 30, 2016 at 3:57 am
J Livingston SQL (6/30/2016)
JoNTSQLSrv (6/30/2016)
J Livingston SQL (6/29/2016)
For knowledge shake, is it possible to implement the same result in while loop ?why?
what benefits do you perceive you will gain by using a while loop......?
Yes ofcourse it is slower than cte ,but as a programmer we should be able to give same solution or result using cte or while loop or cursor right ? so it will increase our logical thinking right ?
I tried using while loop, but i am unable to bring the same result, i get struck when i check all value and fix the exact title when i use loop, so if you help me in that, then it will be usefull for me and i will learn it ... and sure it will help me in some situation
sorry, but I am not convinced about your statement .....
we should be able to give same solution or result using cte or while loop or cursor right
because you have asked several times already for this to be in a while loop. You previously replied the reason was thus:
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
can I ask if this requirement is a "business rule" being imposed upon you?
my view is that you should use the right tools for the job......I can't even spell "curser" let alone write an efficent one.....so I will leave that to others to assist if they so feel.
In order to proceed further with this request, then can I ask you to post the "while loop code" you have that isnt working for you, along with sample DDL/ data and expected results
my view is that you should use the right tools for the job......I can't even spell "curser" let alone write an efficent one.....so I will leave that to others to assist if they so feel.
You are 100% correct. I accept it.
can I ask if this requirement is a "business rule" being imposed upon you?
No, Dear "J Livingston SQL GURU", using while loop is not business rule, But as i said before i wanted to learn how to bring the same result in while loop and also i said before for safer side if i know how to code it in while loop i can easily add extra business rules in it.
I will put the code that i tried using while loop bro. THanks
June 30, 2016 at 9:23 am
if i know how to code it in while loop i can easily add extra business rules in it.
what business rules do you foresee that will necessitate a while loop...as ooposed to amending the current solution?
will be interested to understand....and hopefully someone will be along to help you.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 30, 2016 at 1:29 pm
JoNTSQLSrv (6/30/2016)
J Livingston SQL (6/29/2016)
For knowledge shake, is it possible to implement the same result in while loop ?why?
what benefits do you perceive you will gain by using a while loop......?
Yes ofcourse it is slower than cte ,but as a programmer we should be able to give same solution or result using cte or while loop or cursor right ? so it will increase our logical thinking right ?
I tried using while loop, but i am unable to bring the same result, i get struck when i check all value and fix the exact title when i use loop, so if you help me in that, then it will be usefull for me and i will learn it ... and sure it will help me in some situation
I'll be honest, I could help but I find it better not too in this case. I cannot think of a reason that additional business rules would invalidate a set based solution. Know how to write a RBAR solution where a set based solution is obviously better does not necessarily increase ones logical thinking. Thinking is sets is not the same as thinking row by row. The more you develop set based solutions the better you will become at developing set based solutions.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply