Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need urgent help in a tricky t-SQL


Need urgent help in a tricky t-SQL

Author
Message
Sachin Vaidya
Sachin Vaidya
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 238
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
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Sachin Vaidya
Sachin Vaidya
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 238
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.
Sachin Vaidya
Sachin Vaidya
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 238
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 =
(
CASE WHEN 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 #mytest AS A
INNER JOIN CTE2 AS B
ON A.RankID = B.RankID


ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8981 Visits: 19028
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Sachin Vaidya
Sachin Vaidya
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 238
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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sachin Vaidya
Sachin Vaidya
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 238
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8375 Visits: 19500
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search