June 11, 2010 at 12:12 pm
scott.pletcher (6/11/2010)
posted a very plausible case that broke your code.
Depends. I was answering the request to select only rows with a gap of 2 or less (i.e. exlude rows with max of >2). My code DOES that.
You all just fretted about:
"There's no year, we can't compare".
"How could 11 ever be before 1".
Of course there's no specific year specified -- it's EVERY year.
He wanted MaxVariance. That was part of the specification. Your code broke.
When I post code that has an issue and someone points out the issue I don't argue. I think Jeff has probably corrected me about 5 times in the last month and I actually hope he'll continue to do so because I keep learning from it.
June 11, 2010 at 12:15 pm
scott.pletcher (6/11/2010)
>> but when you post junior level code (non-SARG-able queries anyone?) <<Yes, I took the short-cut, because as *I* pointed out the code will result in full scan anyway.
When Lynn posted *exactly* the same comment about the scan *after* I did, you praised her for it.
You're just upset that I out-did you several times. Geez, get over it.
I've been reading this site a long time and only started posting fairly recently, but it is clear from your comments that you haven't been here long. People trying to post help here don't get upset about other people's solutions or help. They don't spend all their time trying to "out-do" each other. That's one of the reasons I love this site so much, people just help and take suggestions well. You spent a lot of time at "Experts Exchange", you said. That site is full of pissing contests and trying to out-do each other, and competing for points for giving the correct answer, so I can understand why you may have this attitude. You don't earn points like that here. If you're certain you're right, prove it. Show that Jeff's problem can never occur, otherwise you're just showing that all MVPs are not created equal.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 11, 2010 at 12:17 pm
From the original q:
The idea is to find the earliest month value from the list of values per person only when the max variance is <= 2 taking into consideration a 12 month wrap
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 12:18 pm
When I get a chance, I will write code to calc the variance of *any* set of numbers.
At any rate, what solution did *you* provide? NONE. All you could do was make up excuses about the q being "wrong"!
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 12:21 pm
scott.pletcher (6/11/2010)
>> but when you post junior level code (non-SARG-able queries anyone?) <<Yes, I took the short-cut, because as *I* pointed out the code will result in full scan anyway.
When Lynn posted *exactly* the same comment about the scan *after* I did, you praised her for it.
You're just upset that I out-did you several times. Geez, get over it.
The only reason I'm upset is because I think you're a tool who is misleading people who are trying to learn SQL.
For example, you proposed a solution that included a CASE in a WHERE clause in that other thread and I called you on it. It's amateur coding because it makes it non-SARG-able. Your response was that it didn't matter because the query would result in a scan no matter how it was done. I pointed out how you were wrong and gave a specific example that resulted in a seek. This is just one example.
June 11, 2010 at 12:25 pm
scott.pletcher (6/11/2010)
When I get a chance, I will write code to calc the variance of *any* set of numbers.At any rate, what solution did *you* provide? NONE. All you could do was make up excuses about the q being "wrong"!
Page 3. Post 5. It will work with any months as long as the last month isn't more than 5 months after the first month. That would be an assumption though, unreliable at best.
Good day though. Have some real work to do now here on the farm.
June 11, 2010 at 12:30 pm
How about this? We let each person view the code and decide which they like better? Fair enough?
Mine:
SELECT
PersonId,
CASE WHEN NextCalMth < 99 THEN NextCalMth ELSE MinMonth END AS NextMonth,
CASE WHEN MinMonthOct = 99 THEN CAST(MaxMonth AS tinyint) - CAST(MinMonth AS tinyint)
ELSE (12 - CAST(MinMonthOct AS tinyint)) + CAST(MaxMonthPreOct AS tinyint) END
AS [Variance]
FROM (
SELECT
PersonID,
MIN(CASE WHEN [Month] >= MONTH(GETDATE()) THEN [Month] ELSE 99 END) AS NextCalMth,
MAX([Month]) AS MaxMonth,
MIN([Month]) AS MinMonth,
MIN(CASE WHEN [Month] >= 10 THEN [Month] ELSE 99 END) AS MinMonthOct,
MAX(CASE WHEN [Month] >= 10 THEN 0 ELSE [Month] END) AS MaxMonthPreOct
FROM @Sample
GROUP BY PersonID
) AS derived
WHERE
CASE WHEN MinMonthOct = 99 THEN CAST(MaxMonth AS tinyint) - CAST(MinMonth AS tinyint)
ELSE (12 - CAST(MinMonthOct AS tinyint)) + CAST(MaxMonthPreOct AS tinyint) END
<= 2
Or yours:
with cteSample(PersonID, MonthNum)
as
(
select PersonID,
CAST([Month] as Int)
from @Sample
),
cteSampleRefine(PersonID, MonthNum, MonthDiff)
as
(
select cs1.PersonID,
cs1.MonthNum,
MonthDiff = case
when cs1.MonthNum > cs2.MonthNum then cs1.MonthNum - cs2.MonthNum
else cs1.MonthNum + 12 - cs2.MonthNum
end
from cteSample cs1
join cteSample cs2
on cs1.PersonID = cs2.PersonID
and cs1.MonthNum <> cs2.MonthNum
),
cteSampleRefine2(PersonID, MonthNum, DiffOrder)
as
(
select PersonID,
MonthNum,
ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY MonthDiff DESC)
from cteSampleRefine
),
cteFinal(PersonID, LastMonth, FirstMonth)
as
(
select csf.PersonID,
LastMonth = case
when csf.MonthNum = (select MIN(sq.MonthNum) from cteSample sq where sq.PersonID = csf.PersonID)
then (select MAX(sq.MonthNum) from cteSample sq where sq.PersonID = csf.PersonID)
else (select max(sq.MonthNum) from cteSample sq where sq.PersonID = csf.PersonID and sq.MonthNum < csf.MonthNum)
end,
csf.MonthNum as 'First Month'
from cteSampleRefine2 csf
where csf.DiffOrder = 1
),
cteSuperFinal(PersonID, MaxVariance, EarliestMonth)
as
(
select PersonID,
MaxVariance = case
when FirstMonth > LastMonth then LastMonth + 12 - FirstMonth
else LastMonth - FirstMonth
end,
EarliestMonth = FirstMonth
from cteFinal
)
select PersonID,
MaxVariance,
EarliestMonth = case
when MaxVariance > 2 then null
else EarliestMonth
end
from cteSuperFinal
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 12:32 pm
scott.pletcher (6/11/2010)
...When Lynn posted *exactly* the same comment about the scan *after* I did, you praised her for it.
Just as a side note: Lynn (Pettis) is male, not female...
June 11, 2010 at 12:42 pm
Hmm, it would be helpful if someone could re-point me to the data that my code fails on. I don't see the bug, so sample data would help me correct it.
PLEASE IGNORE -- I WENT BACK AND FOUND IT.
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 12:43 pm
>> It will work with any months as long as the last month isn't more than 5 months after the first month. That would be an assumption though, unreliable at best. <<
But only months '11', '12', and '01' will work correctly?
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 1:29 pm
If there can be gaps in the months, and it appears from your test data there can be, I think you will need to resort to a scalar function to calc the "max variance". It's just too complex to do on each row in set-based SQL.
Assuming no month repeats -- and it really shouldn't -- you can create one value that identifies every month in the input, like so:
SUM(POWER(2, CAST([Month] AS TINYINT))) AS Months,
Then pass that value into a scalar function in the outer query to determine the max variance. The scalar function will of course allow you to use loops, IFs, etc., to arrive at the shortest max variance.
For example:
SELECT
PersonId,
dbo.CalcMaxVariance(Months) AS MaxVariance,
CASE WHEN
CASE WHEN MinMonthOct = 99 THEN CAST(MaxMonth AS tinyint) - CAST(MinMonth AS tinyint)
ELSE (12 - CAST(MinMonthOct AS tinyint)) + CAST(MaxMonthPreOct AS tinyint) END
> 2 THEN NULL ELSE
CASE WHEN NextCalMth < 99 THEN CAST(NextCalMth AS tinyint) ELSE CAST(MinMonth AS tinyint) END END
AS NextMonth
FROM (
SELECT
PersonID,
SUM(POWER(2, CAST([Month] AS TINYINT))) AS Months,
MIN(CASE WHEN [Month] >= MONTH(GETDATE()) THEN [Month] ELSE 99 END) AS NextCalMth,
MAX([Month]) AS MaxMonth,
MIN([Month]) AS MinMonth,
MIN(CASE WHEN [Month] BETWEEN 7 AND 9 THEN [Month] ELSE 99 END) AS Min3rdQtrMth,
MIN(CASE WHEN [Month] >= 10 THEN [Month] ELSE 99 END) AS MinMonthOct,
MAX(CASE WHEN [Month] >= 10 THEN 0 ELSE [Month] END) AS MaxMonthPreOct
FROM @Sample
GROUP BY PersonID
) AS derived
Then "all" we have to do is write a CalcMaxVariance function that works correctly 🙂 .
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 2:39 pm
Ok, thought about it some more: I think below is a 100% reliable way of finding the next month. This code also can handle duplicate months in the original input.
To complete the request, still have to write the function to take the Months string and figure out the Max Variance though.
Will need the original requestor to spell out specific rules on that to be truly accurate, since many variations are possible.
SELECT
PersonId,
Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec AS Months,
ExpiratnMth,
--dbo.CalcMaxVariance(Months) AS MaxVariance,
REPLACE(RIGHT('0' + CAST(CHARINDEX('1', REPLICATE(
Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec
, 2), ExpiratnMth) % 12 AS VARCHAR(2)), 2), '00', '12') --or ExpiratnMth + 1 depending on your bus. rule
AS NextMonth
FROM (
SELECT
PersonID,
MAX(CASE WHEN [Month] = '01' THEN '1' ELSE '0' END) AS Jan,
MAX(CASE WHEN [Month] = '02' THEN '1' ELSE '0' END) AS Feb,
MAX(CASE WHEN [Month] = '03' THEN '1' ELSE '0' END) AS Mar,
MAX(CASE WHEN [Month] = '04' THEN '1' ELSE '0' END) AS Apr,
MAX(CASE WHEN [Month] = '05' THEN '1' ELSE '0' END) AS May,
MAX(CASE WHEN [Month] = '06' THEN '1' ELSE '0' END) AS Jun,
MAX(CASE WHEN [Month] = '07' THEN '1' ELSE '0' END) AS Jul,
MAX(CASE WHEN [Month] = '08' THEN '1' ELSE '0' END) AS Aug,
MAX(CASE WHEN [Month] = '09' THEN '1' ELSE '0' END) AS Sep,
MAX(CASE WHEN [Month] = '10' THEN '1' ELSE '0' END) AS Oct,
MAX(CASE WHEN [Month] = '11' THEN '1' ELSE '0' END) AS Nov,
MAX(CASE WHEN [Month] = '12' THEN '1' ELSE '0' END) AS Dec
FROM @Sample
GROUP BY PersonID
) AS derived
--Just to show the results for every expiration month
CROSS JOIN (
SELECT 1 AS ExpiratnMth UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12
) AS ExpiratnMths
ORDER BY PersonID, ExpiratnMth
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 3:06 pm
scott.pletcher (6/11/2010)
I'll also add what I said before. The data is being inserted in the order of 09, 11, 01 and everyone is just getting lucky with the "sorted" order. It could change without warning to be 01, 09, 11 because there's nothing in the data to force the sort order.
That's irrelevant.
The "order" is "in order" *after* the expiration date. The order of the rows is completely irrelevant. There's no "luck" involved in my solution. It does NOT require ANY order to the incoming rows. MIN() and MAX() don't care what order the input is in, right!?
That may be a good point. I'll have to check it out.
--Jeff Moden
June 11, 2010 at 3:07 pm
As a side bar, this thread has taken a bit of a turn for the worse. Everybody be nice, eh?
--Jeff Moden
June 11, 2010 at 3:34 pm
I'm trying to avoid passing thru the original input table, or parts of it, multiple times.
Hopefully the last version is understandable enough while still holding the full passes of the original table to just one.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 15 posts - 46 through 60 (of 68 total)
You must be logged in to reply to this topic. Login to reply