February 14, 2013 at 4:09 am
Hi,
Trying this for a long time but no results 🙁 need some help from experts here.
I have the following dataset. I have to update the last column 'bResult' as 0 if,
1.for a RankID (here 3) , if I have override type as 1 for all of its rows and if Sum(PctAllocation) for all rows with RankID 3 is not equal to 100 then set bResult = 0 for all rows of RankID 3
2.for RankID(here 4), if I have mixed values of overridetype (here 1 and 3) and if Sum(PctAllocation) for all rows with RankID 4 is greater then 100 then set bResult = 0 for all rows of RankID 4
RankID WorkingID RegionID OverrideType PctAllocation bResult
3 -4 1 1 50 NULL
3 -4 2 1 20 NULL
3 -4 3 1 25 NULL
4 -3 1 1 90 NULL
4 -3 2 3 15 NULL
4 -3 3 3 5 NULL
February 14, 2013 at 4:42 am
This is not all that tricky, so rather than spoon feed you the answer I'm going to try and help you to arrive there on your own.
Firstly, it's always a good idea to produce DDL and sample data so that anyone wanting to help you doesn't have to do any extra work setting up a test environment.
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT RankID, WorkingID, RegionID, OverrideType, PctAllocation, bResult
INTO #testEnvironment
FROM (SELECT 3, -4, 1, 1, 50, NULL UNION ALL
SELECT 3, -4, 2, 1, 20, NULL UNION ALL
SELECT 3, -4, 3, 1, 25, NULL UNION ALL
SELECT 4, -3, 1, 1, 90, NULL UNION ALL
SELECT 4, -3, 2, 3, 15, NULL UNION ALL
SELECT 4, -3, 3, 3, 5, NULL
) a(RankID, WorkingID, RegionID, OverrideType, PctAllocation, bResult);
Which produces: -
RankID WorkingID RegionID OverrideType PctAllocation bResult
----------- ----------- ----------- ------------ ------------- -----------
3 -4 1 1 50 NULL
3 -4 2 1 20 NULL
3 -4 3 1 25 NULL
4 -3 1 1 90 NULL
4 -3 2 3 15 NULL
4 -3 3 3 5 NULL
Now, let's start with RankID 3. I'm going to assume that you got this far on your own: -
SELECT RankID,
CASE WHEN RankID = 3 AND SUM(PctAllocation) <> 100 THEN 0 END
FROM #testEnvironment
GROUP BY RankID;
So what the case statement is saying is, "if the rankid is 3 and the sum of the pctallocation is not 100 then the answer is 0, otherwise ignore (which returns NULL)". The third part of your case statement needs to say "if all of the overridetypeid columns are 1". This is easier done than it appears, we can just add a nested case statement: - "CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END", then grab the SUM. If the sum is equal to 0, then they were all 1, otherwise they weren't. So let's add that in to the other case statement: -
SELECT RankID,
CASE WHEN RankID = 3 AND SUM(CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END) = 0 AND SUM(PctAllocation) <> 100 THEN 0 END
FROM #testEnvironment
GROUP BY RankID;
OK, so that deals with rankid 3.
For rankid 4, I'm going to assume that you got this far: -
SELECT RankID,
CASE WHEN RankID = 3 AND SUM(CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END) = 0 AND SUM(PctAllocation) <> 100 THEN 0
WHEN RankID = 4 AND SUM(PctAllocation) > 100 THEN 0 END
FROM #testEnvironment
GROUP BY RankID;
So, the third part of your case statement this time needs to say "if the overridetype doesn't contain identical data". Again, this is easier done than you think. If we compare the MAX and the MIN, if they are different then we know that the data is mixed.
SELECT RankID,
CASE WHEN RankID = 3 AND SUM(CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END) = 0 AND SUM(PctAllocation) <> 100 THEN 0
WHEN RankID = 4 AND MAX(OverrideType) > MIN(OverrideType) AND SUM(PctAllocation) > 100 THEN 0 END
FROM #testEnvironment
GROUP BY RankID;
So now all you've got left to do is convert that in to an UPDATE statement. Remember that an aggregate can't appear in the WHERE clause. If you get stuck, post back with what you've tried and we'll take a look.
February 14, 2013 at 10:43 pm
Hi Cadavre,
Your response definitely shows a path to move ahead, thanks a lot for that.
But on the other side, it was very rude. It will discourage people from putting any questions to the forum.
To underline, this was not my tution assignment or homework which I wanted to get done from the forum (read spoonfeeding).
To create an example, I suggested RankID as 3 and 4 but it is not limited, RankID's can be in thousands, so I will not be writing thousand CASE staements...
What sounds like a cakewalk to you might be a bit 'tricky' for others, as all fingers are not same!
But in the end, I would like to appreciate your effort spent in posting a detailed reply.
Thanks and Regards.
February 15, 2013 at 12:16 am
Hi All,
For the sake of all visitors to this post, here is the solution that I figured out, with combination of my own effort, plus some other online help.
Thanks.
;WITH CTE1 AS
(
SELECT*, SUM(PctAllocation) OVER (PARTITION BY RankID) AS TotalAlloc
FROM#mytest
),
CTE2 AS
(
SELECT RankID, MIN(nIndex_CapacityOverrideType) AS MinType, MAX(nIndex_CapacityOverrideType) AS MaxType, TotalAlloc FROM CTE1 GROUP BY RankID, TotalAlloc
)
UPDATE #mytest SET bResult =
(
CASEWHEN B.MinType = 1 AND B.MaxType = 1 AND B.TotalAlloc <> 100 THEN 0
WHEN B.MinType = 1 AND B.MaxType = 2 AND B.TotalAlloc > 100 THEN 0
WHEN B.MinType = 2 AND B.MaxType = 2 AND B.TotalAlloc > 100 THEN 0
WHEN B.MinType = 1 AND B.MaxType = 3 AND B.TotalAlloc <> 100 THEN 0
WHEN B.MinType = 2 AND B.MaxType = 3 AND B.TotalAlloc > 100 THEN 0
WHEN B.MinType = 3 AND B.MaxType = 3 AND B.TotalAlloc <> 100 THEN 0
ELSE 1
END
)
FROM#mytestAS A
INNER JOINCTE2AS B
ON A.RankID = B.RankID
February 15, 2013 at 1:09 am
Sachin Vaidya (2/14/2013)
Hi Cadavre,Your response definitely shows a path to move ahead, thanks a lot for that.
But on the other side, it was very rude. It will discourage people from putting any questions to the forum.
To underline, this was not my tution assignment or homework which I wanted to get done from the forum (read spoonfeeding).
To create an example, I suggested RankID as 3 and 4 but it is not limited, RankID's can be in thousands, so I will not be writing thousand CASE staements...
What sounds like a cakewalk to you might be a bit 'tricky' for others, as all fingers are not same!
But in the end, I would like to appreciate your effort spent in posting a detailed reply.
Thanks and Regards.
Gosh, this is unexpected.
Cadavre could easily have posted his solution without any explanation. Many of us do. That's spoonfeeding. Instead he has taken the time (his own time, for free) to break the solution down into parts and explain to you in some detail how each works so you can understand it. Next time you face a similar problem you will be equipped with the necessary knowledge and skill to tackle it yourself, without seeking assistance from others. "Give a man a fish and you feed him for a day - teach him how to fish and you feed him for the rest of his life."
Cadavre's solution is a good example of the exceptionally high standard of assistance he offers to posters here. It's certainly not rude, and neither will it discourage others from seeking help on ssc.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 15, 2013 at 2:09 am
ChrisM@Work (2/15/2013)
Sachin Vaidya (2/14/2013)
Hi Cadavre,Your response definitely shows a path to move ahead, thanks a lot for that.
But on the other side, it was very rude. It will discourage people from putting any questions to the forum.
To underline, this was not my tution assignment or homework which I wanted to get done from the forum (read spoonfeeding).
To create an example, I suggested RankID as 3 and 4 but it is not limited, RankID's can be in thousands, so I will not be writing thousand CASE staements...
What sounds like a cakewalk to you might be a bit 'tricky' for others, as all fingers are not same!
But in the end, I would like to appreciate your effort spent in posting a detailed reply.
Thanks and Regards.
Gosh, this is unexpected.
Cadavre could easily have posted his solution without any explanation. Many of us do. That's spoonfeeding. Instead he has taken the time (his own time, for free) to break the solution down into parts and explain to you in some detail how each works so you can understand it. Next time you face a similar problem you will be equipped with the necessary knowledge and skill to tackle it yourself, without seeking assistance from others. "Give a man a fish and you feed him for a day - teach him how to fish and you feed him for the rest of his life."
Cadavre's solution is a good example of the exceptionally high standard of assistance he offers to posters here. It's certainly not rude, and neither will it discourage others from seeking help on ssc.
Apologies to forum,
1. I appreciate Cadavre's effort. In one of my previous post also I appreciated him but I just tried to explain that a thing can be easy for someone and tricky for others as it depends on the personal intelligence level, so that should be respected. I just wanted to highlight this, nothing personal against Cadavre.
2. I tried and finally posted my solution as well in my last post.
I hope this will rest here. Cheers.
Regards.
February 15, 2013 at 3:11 am
Sachin Vaidya (2/14/2013)
Hi Cadavre,Your response definitely shows a path to move ahead, thanks a lot for that.
But on the other side, it was very rude. It will discourage people from putting any questions to the forum.
To underline, this was not my tution assignment or homework which I wanted to get done from the forum (read spoonfeeding).
To create an example, I suggested RankID as 3 and 4 but it is not limited, RankID's can be in thousands, so I will not be writing thousand CASE staements...
What sounds like a cakewalk to you might be a bit 'tricky' for others, as all fingers are not same!
But in the end, I would like to appreciate your effort spent in posting a detailed reply.
Thanks and Regards.
I know that you have subsequently apologised, but this message is out of order. If you need someone to do your work, hire a consultant. We are unpaid volunteers who help out when we can.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 15, 2013 at 3:41 am
Phil Parkin (2/15/2013)
Sachin Vaidya (2/14/2013)
Hi Cadavre,Your response definitely shows a path to move ahead, thanks a lot for that.
But on the other side, it was very rude. It will discourage people from putting any questions to the forum.
To underline, this was not my tution assignment or homework which I wanted to get done from the forum (read spoonfeeding).
To create an example, I suggested RankID as 3 and 4 but it is not limited, RankID's can be in thousands, so I will not be writing thousand CASE staements...
What sounds like a cakewalk to you might be a bit 'tricky' for others, as all fingers are not same!
But in the end, I would like to appreciate your effort spent in posting a detailed reply.
Thanks and Regards.
I know that you have subsequently apologised, but this message is out of order. If you need someone to do your work, hire a consultant. We are unpaid volunteers who help out when we can.
I apologize once again if that will help.
I would have removed this post if it was in my hands, but please give me a chance to explain that I was not demanding a ready made answer. Who am I to ask all these technical experts to do my work? I am a humble SQL learner and nothing else.
I simply want to say that when one finds a problem tricky and someone else starts with a reply saying 'this is not all that tricky' on a public forum , it doesn't feel nice. It can discourage people who are not experts.
I have high regards for this forum which has helped me to grow as a SQL developer over the years, so I will not put any post that will harm the reputation of this forum.
From my side, this is my last reply to this particular post.
Thanks
February 15, 2013 at 4:18 am
I apologize once again if that will help.
I would have removed this post if it was in my hands, but please give me a chance to explain that I was not demanding a ready made answer. Who am I to ask all these technical experts to do my work? I am a humble SQL learner and nothing else.
I simply want to say that when one finds a problem tricky and someone else starts with a reply saying 'this is not all that tricky' on a public forum , it doesn't feel nice. It can discourage people who are not experts.
I have high regards for this forum which has helped me to grow as a SQL developer over the years, so I will not put any post that will harm the reputation of this forum.
From my side, this is my last reply to this particular post.
Thanks
OK, I understand where you are coming from. This is just a case of misinterpretation, I believe. I am certain that Cadavre was not trying to suggest anything negative about you, merely that the problem itself was not too difficult.
Most people here understand that experienced SQL developers will be more adept at solving such problems than newcomers & that that does not mean that they are smarter. They've just been there before.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 15, 2013 at 4:30 am
Sorry you feel that my reply was rude, it certainly wasn't intended to be. Terminology is often difficult on an international forum, something I try to take account of when I post. Even re-reading my reply and looking at it from your point of view, I see nothing wrong with it but if you do, then be aware that it wasn't my intention.
With the SQL, if each RankId needs to be treated differently then I don't understand how you think that your code is less to maintain. If we re-write your code using what I was attempting to explain, we come up with this: -
UPDATE a
SET bResult = newValue
FROM #mytest a
INNER JOIN (SELECT RankID,
CASE WHEN a.MinType = 1 AND a.MaxType = 1 AND a.TotalAlloc <> 100 THEN 0
WHEN a.MinType = 1 AND a.MaxType = 2 AND a.TotalAlloc > 100 THEN 0
WHEN a.MinType = 2 AND a.MaxType = 2 AND a.TotalAlloc > 100 THEN 0
WHEN a.MinType = 1 AND a.MaxType = 3 AND a.TotalAlloc <> 100 THEN 0
WHEN a.MinType = 2 AND a.MaxType = 3 AND a.TotalAlloc > 100 THEN 0
WHEN a.MinType = 3 AND a.MaxType = 3 AND a.TotalAlloc <> 100 THEN 0
ELSE 1 END AS newValue
FROM (SELECT RankID, MAX(nIndex_CapacityOverrideType), MIN(nIndex_CapacityOverrideType), SUM(PctAllocation)
FROM #mytest
GROUP BY RankID) a(RankID, MaxType, MinType, TotalAlloc)
) b ON a.RankID = b.RankID;
This does exactly what your code does, only it requires less in the way of "work" for SQL Server. Bear in mind that neither this or the solution that you posted fulfils what you originally described as your problem. However, this could have simply been lost in the natural language that you wrote, which is why we normally request DDL, sample data and expected results based on the sample data to go with any descriptions of a problem.
February 15, 2013 at 4:40 am
Group hug? 😀
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 15, 2013 at 3:51 pm
Abu Dina (2/15/2013)
Group hug? 😀
Ewwww! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2013 at 9:00 pm
ohh my my..after i read the conversation i really shocked and this is totaly kind of misunderstanding..
I respect all the people in here and more to those give a fast and accurate solution to problem encounter.
🙂 Just appreciate all the help's and be thankfull for there time.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply