Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need urgent help in a tricky t-SQL Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 4:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:09 AM
Points: 75, Visits: 226
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
Post #1419946
Posted Thursday, February 14, 2013 4:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,372, Visits: 7,560
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1419977
Posted Thursday, February 14, 2013 10:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:09 AM
Points: 75, Visits: 226
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.
Post #1420386
Posted Friday, February 15, 2013 12:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:09 AM
Points: 75, Visits: 226
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

Post #1420403
Posted Friday, February 15, 2013 1:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Post #1420416
Posted Friday, February 15, 2013 2:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:09 AM
Points: 75, Visits: 226
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.
Post #1420435
Posted Friday, February 15, 2013 3:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,078, Visits: 11,857
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1420457
Posted Friday, February 15, 2013 3:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:09 AM
Points: 75, Visits: 226
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
Post #1420466
Posted Friday, February 15, 2013 4:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,078, Visits: 11,857
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1420472
Posted Friday, February 15, 2013 4:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,372, Visits: 7,560
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1420475
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse