June 10, 2010 at 9:33 am
scott.pletcher (6/10/2010)
Did anyone even look at the code I posted that, to me, seems to get the answers desired??
Does it work if the data is out "out of order" from what the correct logical order should be?
--Jeff Moden
June 10, 2010 at 9:54 am
I think so.
It was fairly clear when he said month 11 was "before" month 1 that some other month was controlling the sequence.
For convenience, I used MONTH(GETDATE()), since it was easy and worked for the sample data :-).
But, as I stated in an earlier post, he should just be able to replace MONTH(GETDATE()) with "expiration"/"renewal" month and the logic should still work.
Run the code as I posted with sample data and you should see it matches his desired output from his initial post.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 11:20 am
scott.pletcher (6/10/2010)
Did anyone even look at the code I posted that, to me, seems to get the answers desired??
Yes it works with the sample data provided, but without specific rule criteria, there is no way to know how it would hold up with additional possible data. For instance, add:
INSERT @Sample (PersonID, Month) VALUES (10,'09');
INSERT @Sample (PersonID, Month) VALUES (10,'11');
INSERT @Sample (PersonID, Month) VALUES (10,'01');
Because you make the assumption that a series of months won't 'wrap' from before October, it thinks the first month is January with a resulting variance of 10.
If you're correct and it is impossible to wrap from before a start of Oct then it's pretty solid. If your assumption is incorrect then it doesn't really work.
June 10, 2010 at 11:26 am
I used month 10 as the cutoff because if month 9 and month 1 are present, the max of 2 will be exceeded, so the actual max variance is irrelevant for selection .... except that, as you're noted, from a reporting standpoint, the max variance would be wrong. I think I can fix that, but it will be some time before I can get to it.
As for properly selecting var <= 2, and finding the start month, I think the code will work for all valid input, but I admit I haven't tested it on the larger data samples shown.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 9:35 pm
scott.pletcher (6/10/2010)
I think so.It was fairly clear when he said month 11 was "before" month 1 that some other month was controlling the sequence.
For convenience, I used MONTH(GETDATE()), since it was easy and worked for the sample data :-).
But, as I stated in an earlier post, he should just be able to replace MONTH(GETDATE()) with "expiration"/"renewal" month and the logic should still work.
Run the code as I posted with sample data and you should see it matches his desired output from his initial post.
Understood... but my point is that everyone is just getting lucky. There isn't anything in the data to guarantee that the month order of (for example) 11, 12, 1 won't change to 1, 11, 12 behind the scenes. It could change at any time.
--Jeff Moden
June 10, 2010 at 9:37 pm
bteraberry (6/10/2010)
scott.pletcher (6/10/2010)
Did anyone even look at the code I posted that, to me, seems to get the answers desired??Yes it works with the sample data provided, but without specific rule criteria, there is no way to know how it would hold up with additional possible data. For instance, add:
INSERT @Sample (PersonID, Month) VALUES (10,'09');INSERT @Sample (PersonID, Month) VALUES (10,'11');
INSERT @Sample (PersonID, Month) VALUES (10,'01');
Because you make the assumption that a series of months won't 'wrap' from before October, it thinks the first month is January with a resulting variance of 10.
If you're correct and it is impossible to wrap from before a start of Oct then it's pretty solid. If your assumption is incorrect then it doesn't really work.
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.
--Jeff Moden
June 11, 2010 at 9:48 am
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!?
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 11:00 am
Jeff Moden (6/10/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.
Jeff, I believe you are completely correct. There is a huge difference between a reasonable assumption and reliable knowledge and no solution can be judged to be correct without the foundation of the latter.
*If* it is true that the last month would never trail the first month by 5, *then* the solution could be mathematically defined. However, since that has never been stated it is merely a guess (whether reasonable or not), any solution built on this hypothesis is unreliable as is any other solution built on any other theory.
Damn I miss handling project management and getting specs for clueless clients ...
June 11, 2010 at 11:34 am
lol, you two are funny. I post code that actually works and you keep talking to each other about how "it can't be done". ROFLOL.
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 11:39 am
If you couldn't glean enough info to work on this from the requestor's initial posts, it's not (only) the clients that are clueless!
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 11:58 am
If you only have 3 months in your db per person at a time, then the following should work
SELECT Person,
CASE MonthTot
WHEN 6 THEN 1
WHEN 9 THEN 2
WHEN 12 THEN 3
WHEN 15 THEN 4
WHEN 18 THEN 5
WHEN 21 THEN 6
WHEN 24 THEN 7
WHEN 117 THEN 8
WHEN 219 THEN 9
WHEN 330 THEN 10
WHEN 231 THEN 11
WHEN 123 THEN 12
END
FROM
(SELECT Person, SUM(CASE WHEN BadID> 9 THEN badid * 10 ELSE badid END) as MonthTot FROM Tryit
GROUP BY Person) Q
This gives you a unique total for each month combination in the subquery and parses it back out to the lowest possible month in the parent query.
That said, You should redesign your table so that not only do you have year information, making this all far easier, but you'll be able to store a larger amount of data per person.
In addition, your original sample won't work as you have the Month declared as a "char(01)" and it has 2 character values. You should make it a date or int and change the name from "Month" to something more descriptive that isn't a reserved word.
--------------------------------------
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:03 pm
scott.pletcher (6/11/2010)
lol, you two are funny. I post code that actually works and you keep talking to each other about how "it can't be done". ROFLOL.
I posted a very plausible case that broke your code. Rather than accept the limitation of your logic and recognizing the shortcomings of making assumptions you've chose to be an *** ... again. By the way, the code I posted returns exactly the results one would guess that the guy wants and this remains the case when plausible results are added. This being the case, I recognize that my 'solution' is completely unreliable because Jeff is correct, my assumptions about the logic are merely assumptions.
I'm not sure if you thought that adding 'MVP' to your signature would buy you credibility, but when you post junior level code (non-SARG-able queries anyone?) and attack Jeff you just come across like a total pretender with a chip on his shoulder, which is sad because there weren't any of those here before you came.
June 11, 2010 at 12:07 pm
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.
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 12:08 pm
scott.pletcher (6/11/2010)
If you couldn't glean enough info to work on this from the requestor's initial posts, it's not (only) the clients that are clueless!
Your solution seems to work for the sample data provided by the OP and for the additional requirement you defined all by yourself.
It seems like you made up a value to compare against (MONTH(GETDATE())). Where did you get the information from that there will be any such value to compare against? Or did you make it up simply because you needed it for your solution?
From my point of view there is a major difference between "glean information" vs. "fabricate information"...
As long as the OP doesn't provide any more details, we're only guessing. Therefore, I admit being clueless in this case.
June 11, 2010 at 12:09 pm
>> 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 him for it.
You're just upset that I out-did you several times. Geez, get over it.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 15 posts - 31 through 45 (of 68 total)
You must be logged in to reply to this topic. Login to reply