June 8, 2010 at 9:32 am
What if it's 11/09 and 2/10 )month and year). In reality, 11 would be the earliest and 2 would be later but you only store month???? Your design leaves some potential issues with respect to obtaining accurate data and potentially reporting to someone you may be trying to get a renewal from (meaning "money"). If you couldn't get my renewal date correct, I'd be tossing your solicitation into the trash without another thought.
-- You can't be late until you show up.
June 8, 2010 at 9:40 am
Thanks for your feedback.
The 'renewal' month represents the anniversary month and is collected from multiple sources and this routine (MaxVariance and EarliestMonth) is part of a larger process used to score the accuracy of the supplied data across sources.
June 8, 2010 at 9:53 am
This is not meant as a put-down because I've inherited some pretty ugly stuff before but I still believe the design is lacking and will lead to issues somewhere along the way. Good luck.
-- You can't be late until you show up.
June 8, 2010 at 2:48 pm
joefreeman (6/8/2010)
Hi,Thanks for your support and help so far, do you think there could be a solution ?
Please don't message me about specific problems, I get a notification for threads I've posted in so if I decided not to reply there was a reason.
I answer questions here (and ask them!) in my spare time, when I feel like it. There came a point in attempting to answer this where it just became more trouble than it was worth. After all we've gone through, I just don't understand the logic behind this problem and so rather than give you a sub-par answer that will probably not work for your problem I decided to bow out. That is not to say that if the logic was thought through and posted in a more understandable fashion that I wouldn't attempt it, just that for me this whole thing makes no sense.
I'm not trying to be horrible when I say this, but I'd look at redesigning the logic because it seems fairly plain to me that either you don't understand it or that the logic is bad.
June 8, 2010 at 2:49 pm
To follow tosscrosby's concern:
What would be your expected output for the following sample data? And Why?
INSERT @Sample (PersonID, Month) VALUES (11,'06');
INSERT @Sample (PersonID, Month) VALUES (11,'08');
INSERT @Sample (PersonID, Month) VALUES (11,'07');
Will your max variance be 2 or 13 or something else?
June 8, 2010 at 3:56 pm
Please see if this does what you need. If so, might be able to clean it up a little:
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
Scott Pletcher, SQL Server MVP 2008-2010
June 8, 2010 at 4:06 pm
This query shows *all* results, including the NULLs, just for confirmation.
You will need to replace MONTH(GETDATE()) with renewal month -- wasn't sure what the "magic month" 🙂 would end up being, so I used that as a quick-and-dirty work-around :-).
SELECT
PersonId,
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 [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,
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
Scott Pletcher, SQL Server MVP 2008-2010
June 8, 2010 at 4:07 pm
Yikes, looks like crap w/o the proper spacing.
Why are leading spaces stripped when dealing with CODE posts???? :angry:
Scott Pletcher, SQL Server MVP 2008-2010
June 8, 2010 at 5:18 pm
June 8, 2010 at 6:11 pm
joefreeman (6/8/2010)
I'm sorry Paul and really do appreciate your help. When you're so close to a problem sometimes it's hard to verbalise all the intricacies. If you can help further I would be very grateful.
No offense, but I don't think the problem here is that you're so close. You haven't really at any point clarified objectively what makes any one particular month the first. I'm just taking a guess based on your non-logically defined examples.
This code isn't pretty, but given the lack of definition I didn't want to spend more than the minimum amount of time to return what I think it is that you want.
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
As other people have mentioned, I think there is a major design flaw with your database, which will have the tendency to make queries ugly and inefficient. At the same time, I'm sure one of the smarties around here would be able to make this much better if it is in fact what you want.
June 8, 2010 at 6:19 pm
Unless I'm missing something, this problem is impossible to solve because there's simply nothing to guarantee the order of the data. There has to be a date entered column or and IDENTITY column that represents the order the data is meant to be. Otherwise there's a chance of the "months" being presented/calculated in an incorrect order.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2010 at 6:26 pm
Jeff Moden (6/8/2010)
Unless I'm missing something, this problem is impossible to solve because there's simply nothing to guarantee the order of the data. There has to be a date entered column or and IDENTITY column that represents the order the data is meant to be. Otherwise there's a chance of the "months" being presented/calculated in an incorrect order.
Looking at the samples, I'm guessing that the months will never fall that far apart ... that the last month won't be more than 4 or 5 months after the first month. (In the example it's no more than 3.) That's my premise in my 'solution'. However, given the lack of clarity in the OP's questions and comments I wouldn't be surprised if you're correct and there is no objective way to determine which month is first.
June 9, 2010 at 8:36 pm
skcadavre (6/8/2010)
Please don't message me about specific problems, I get a notification for threads I've posted in so if I decided not to reply there was a reason.I answer questions here (and ask them!) in my spare time, when I feel like it. There came a point in attempting to answer this where it just became more trouble than it was worth. After all we've gone through, I just don't understand the logic behind this problem and so rather than give you a sub-par answer that will probably not work for your problem I decided to bow out. That is not to say that if the logic was thought through and posted in a more understandable fashion that I wouldn't attempt it, just that for me this whole thing makes no sense.
I'm not trying to be horrible when I say this, but I'd look at redesigning the logic because it seems fairly plain to me that either you don't understand it or that the logic is bad.
Well put, and I came here intending to write exactly the same.
I'm more than happy to contribute again, but we do need a precise definition of the rules.
I won't post back until that happens.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 9, 2010 at 11:14 pm
bteraberry (6/8/2010)
Jeff Moden (6/8/2010)
Unless I'm missing something, this problem is impossible to solve because there's simply nothing to guarantee the order of the data. There has to be a date entered column or and IDENTITY column that represents the order the data is meant to be. Otherwise there's a chance of the "months" being presented/calculated in an incorrect order.Looking at the samples, I'm guessing that the months will never fall that far apart ... that the last month won't be more than 4 or 5 months after the first month. (In the example it's no more than 3.) That's my premise in my 'solution'. However, given the lack of clarity in the OP's questions and comments I wouldn't be surprised if you're correct and there is no objective way to determine which month is first.
Understood but even if the months are only 1 apart, there's nothing to guarantee the order of the months as 11,12,1 (for example). A heap table will make it look so but there's no guarantee.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 10, 2010 at 7:43 am
Did anyone even look at the code I posted that, to me, seems to get the answers desired??
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 15 posts - 16 through 30 (of 68 total)
You must be logged in to reply to this topic. Login to reply