June 8, 2010 at 3:51 am
I'm stuck, Please help.
I have a database with multiple month values per individual in a single table like so;
PersonID, Month
1,'01'
1,'11'
1,'12'
2,'06'
2,'07'
3,'11'
3,'01'
For each PersonID I need to select the earliest month value taking into consideration a 12 month wrap e.g. PersonID 1 has values '01','11','12' thus the month value selected should be '11'.
Sorry, to add to the issue.
I also need to know the max variance between the values for each PersonID i.e the max variance between '01','02','03' is 2 and given '11','12','01' it should also return 2
Unfortunately the month value is an anniversary/renewal month therefore is non year specific.
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
Therefore when given 11,01 the variance would actually be 2 and earliest month 11.
11,12,02 variance = 3
12,01,03 variance = 3
11,12,01 variance = 2 and earliest is 11
12,01,02 variance = 2 and earliest is 12
12,01 variance = 1 and earliest is 12
12,02 variance = 2 and earliest is 12
DECLARE @Sample
TABLE (
PersonID int,
Month char(01)
);
INSERT @Sample (PersonID, Month) VALUES (1,'12');
INSERT @Sample (PersonID, Month) VALUES (1,'01');
INSERT @Sample (PersonID, Month) VALUES (2,'11');
INSERT @Sample (PersonID, Month) VALUES (2,'12');
INSERT @Sample (PersonID, Month) VALUES (2,'01');
INSERT @Sample (PersonID, Month) VALUES (3,'11');
INSERT @Sample (PersonID, Month) VALUES (3,'01');
INSERT @Sample (PersonID, Month) VALUES (4,'11');
INSERT @Sample (PersonID, Month) VALUES (4,'02');
INSERT @Sample (PersonID, Month) VALUES (5,'12');
INSERT @Sample (PersonID, Month) VALUES (5,'02');
INSERT @Sample (PersonID, Month) VALUES (6,'06');
INSERT @Sample (PersonID, Month) VALUES (6,'07');
INSERT @Sample (PersonID, Month) VALUES (6,'08');
INSERT @Sample (PersonID, Month) VALUES (7,'06');
INSERT @Sample (PersonID, Month) VALUES (7,'07');
INSERT @Sample (PersonID, Month) VALUES (7,'09');
INSERT @Sample (PersonID, Month) VALUES (8,'01');
INSERT @Sample (PersonID, Month) VALUES (8,'02');
INSERT @Sample (PersonID, Month) VALUES (8,'03');
INSERT @Sample (PersonID, Month) VALUES (9,'11');
INSERT @Sample (PersonID, Month) VALUES (9,'12');
From the above sample the results would look something like;
PersonID,MaxVariance,EarliestMonth
1,1,'12'
2,2,'11'
3,2,'11'
4,3,NULL
5,2,'12'
6,2,'06'
7,3,NULL
8,2,'01'
9,1,'11'
June 8, 2010 at 3:59 am
Sorry, to add to the issue.
I also need to know the max variance between the values for each PersonID i.e the max variance between '01','02','03' is 2 and given '11','12','01' it should also return 2
June 8, 2010 at 3:59 am
lol, just answered an identical question that wanted the largest instead of the smallest from the data set.
--First things first, since you have provided this in your question,
--I build some dummy data.
DECLARE @Table1 TABLE(
[ID] INT,
[Month] INT)
INSERT INTO @Table1
VALUES (1,01)
INSERT INTO @Table1
VALUES (1,11)
INSERT INTO @Table1
VALUES (1,12)
INSERT INTO @Table1
VALUES (2,06)
INSERT INTO @Table1
VALUES (2,07)
INSERT INTO @Table1
VALUES (3,11)
INSERT INTO @Table1
VALUES (3,01)
--Now for the query
SELECT t1.*
FROM @Table1 AS t1
LEFT OUTER JOIN @Table1 AS t2
ON ( t1.[ID] = t2.[ID]
AND t1.[Month] > t2.[Month] )
WHERE t2.[Month] IS NULL;
-EDIT- You moved the goal posts after I started typing, so this is no longer what you want 😛
June 8, 2010 at 5:08 am
Assuming that the current date acts as the cut-off for deciding whether a month number belongs to last year or this year...
DECLARE @Sample
TABLE (
person_id INTEGER NOT NULL,
month_id INTEGER NOT NULL
);
INSERT @Sample (person_id, month_id) VALUES (1,01);
INSERT @Sample (person_id, month_id) VALUES (1,11);
INSERT @Sample (person_id, month_id) VALUES (1,12);
INSERT @Sample (person_id, month_id) VALUES (2,06);
INSERT @Sample (person_id, month_id) VALUES (2,07);
INSERT @Sample (person_id, month_id) VALUES (3,11);
INSERT @Sample (person_id, month_id) VALUES (3,01);
SELECT S.person_id,
earliest =
MIN(iTVF.adjusted_month) OVER (PARTITION BY S.person_id) % 12,
variance =
MAX(iTVF.adjusted_month) OVER (PARTITION BY S.person_id) -
MIN(iTVF.adjusted_month) OVER (PARTITION BY S.person_id) % 12
FROM @Sample S
CROSS
APPLY (
SELECT CASE WHEN S.month_id > MONTH(CURRENT_TIMESTAMP) THEN S.month_id ELSE S.month_id + 12 END
) iTVF (adjusted_month);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2010 at 5:15 am
Sorry, unfortunately the month value is an anniversary/renewal month therefore is non year specific. So the wrap affect only comes into play when December(12) is in the list.
June 8, 2010 at 5:41 am
joefreeman (6/8/2010)
Sorry, unfortunately the month value is an anniversary/renewal month therefore is non year specific. So the wrap affect only comes into play when December(12) is in the list.
That's two people's time you have wasted by not bothering to state the question precisely enough.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2010 at 5:46 am
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.
June 8, 2010 at 5:47 am
What is the earliest month for person 3 in your original sample data?
Is it month 1 or month 11?
Month 12 is not in the list.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2010 at 5:49 am
For PersonID 3 the earliest month value would be '01'
June 8, 2010 at 5:57 am
joefreeman (6/8/2010)
For PersonID 3 the earliest month value would be '01'
And the variance would be 10? :unsure:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2010 at 6:30 am
Right sorry Paul, don't shout at me :), I've managed to grab the person who has this business requirement and dug a little deeper.
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 between any given months.
Therefore when given 11,01 the variance would actually be 2 and earliest month 11.
11,12,02 variance = 3
12,01,03 variance = 3
11,12,01 variance = 2 and earliest is 11
12,01,02 variance = 2 and earliest is 12
12,01 variance = 1 and earliest is 12
12,02 variance = 2 and earliest is 12
So I think the wrap is only taking into consideration when the range includes November to February ?
If you are still free and willing to help it would be very very much appreciated.
June 8, 2010 at 7:32 am
I don't understand this at all. . . is the month stored as an INT? Or is it actually a DATETIME that you get the month from?
The reason I ask is because otherwise I can't see anyway you can differentiate between 05,06,07 (05 being the earliest with a variance of 2) and 05,06,07 (with 07 being the earliest and a variance of 10).
Can you supply with data and table structure please, in a format such as Paul or myself supplied: -
DECLARE @Sample
TABLE (
person_id DATATYPE,
month_id DATATYPE
);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
INSERT @Sample (person_id, month_id) VALUES ($,$$);
With an explanation detailing how the above scenario I have described would be resolved.
Or what about. . . what is the earliest and why: -
*09,10,11 ?
*10,11,09 ?
*05,06,07 ?
June 8, 2010 at 7:57 am
DECLARE @Sample
TABLE (
PersonID int,
Month char(01)
);
INSERT @Sample (PersonID, Month) VALUES (1,'12');
INSERT @Sample (PersonID, Month) VALUES (1,'01');
INSERT @Sample (PersonID, Month) VALUES (2,'11');
INSERT @Sample (PersonID, Month) VALUES (2,'12');
INSERT @Sample (PersonID, Month) VALUES (2,'01');
INSERT @Sample (PersonID, Month) VALUES (3,'11');
INSERT @Sample (PersonID, Month) VALUES (3,'01');
INSERT @Sample (PersonID, Month) VALUES (4,'11');
INSERT @Sample (PersonID, Month) VALUES (4,'02');
INSERT @Sample (PersonID, Month) VALUES (5,'12');
INSERT @Sample (PersonID, Month) VALUES (5,'02');
INSERT @Sample (PersonID, Month) VALUES (6,'06');
INSERT @Sample (PersonID, Month) VALUES (6,'07');
INSERT @Sample (PersonID, Month) VALUES (6,'08');
INSERT @Sample (PersonID, Month) VALUES (7,'06');
INSERT @Sample (PersonID, Month) VALUES (7,'07');
INSERT @Sample (PersonID, Month) VALUES (7,'09');
INSERT @Sample (PersonID, Month) VALUES (8,'01');
INSERT @Sample (PersonID, Month) VALUES (8,'02');
INSERT @Sample (PersonID, Month) VALUES (8,'03');
INSERT @Sample (PersonID, Month) VALUES (9,'11');
INSERT @Sample (PersonID, Month) VALUES (9,'12');
From the above sample the results would look something like;
PersonID,MaxVariance,EarliestMonth
1,1,'12'
2,2,'11'
3,2,'11'
4,3,NULL
5,2,'12'
6,2,'06'
7,3,NULL
8,2,'01'
9,1,'11'
It certainly is stumping to me - really appreciate you looking into it further.
June 8, 2010 at 8:24 am
joefreeman (6/8/2010)
From the above sample the results would look something like;PersonID,MaxVariance,EarliestMonth
1,1,'12'
2,2,'11'
3,2,'11'
4,3,NULL
5,2,'12'
6,2,'06'
7,3,NULL
8,2,'01'
9,1,'11'
It certainly is stumping to me - really appreciate you looking into it further.
OK, first I've fixed the data insert - (char(1) doesn't work with data that has 2 digits)
DECLARE @Sample
TABLE (
[PersonID] int,
[Month] char(2)
);
INSERT @Sample ([PersonID], [Month]) VALUES (1,'12');
INSERT @Sample ([PersonID], [Month]) VALUES (1,'01');
INSERT @Sample ([PersonID], [Month]) VALUES (2,'11');
INSERT @Sample ([PersonID], [Month]) VALUES (2,'12');
INSERT @Sample ([PersonID], [Month]) VALUES (2,'01');
INSERT @Sample ([PersonID], [Month]) VALUES (3,'11');
INSERT @Sample ([PersonID], [Month]) VALUES (3,'01');
INSERT @Sample ([PersonID], [Month]) VALUES (4,'11');
INSERT @Sample ([PersonID], [Month]) VALUES (4,'02');
INSERT @Sample ([PersonID], [Month]) VALUES (5,'12');
INSERT @Sample ([PersonID], [Month]) VALUES (5,'02');
INSERT @Sample ([PersonID], [Month]) VALUES (6,'06');
INSERT @Sample ([PersonID], [Month]) VALUES (6,'07');
INSERT @Sample ([PersonID], [Month]) VALUES (6,'08');
INSERT @Sample ([PersonID], [Month]) VALUES (7,'06');
INSERT @Sample ([PersonID], [Month]) VALUES (7,'07');
INSERT @Sample ([PersonID], [Month]) VALUES (7,'09');
INSERT @Sample ([PersonID], [Month]) VALUES (8,'01');
INSERT @Sample ([PersonID], [Month]) VALUES (8,'02');
INSERT @Sample ([PersonID], [Month]) VALUES (8,'03');
INSERT @Sample ([PersonID], [Month]) VALUES (9,'11');
INSERT @Sample ([PersonID], [Month]) VALUES (9,'12');
Second, formatted your expected output.
/*
Person ID MaxVariance Earliest Month
----------- ----------- --------------
1 1 12
2 2 11
3 2 11
4 3 NULL
5 2 12
6 2 06
7 3 NULL
8 2 01
9 1 11
*/
Why does PersonID 4 and 7 have a NULL for the earliest month?
June 8, 2010 at 8:29 am
Sorry was rushing to get the sample over to you, thank you for tidying it up.
I've left the EarliestMonth value Null for records where the MaxVariance is > 2. The requirement is to select the earliest month only when the MaxVariance is <= 2, obviously we could still calculate but it is not required.
Viewing 15 posts - 1 through 15 (of 68 total)
You must be logged in to reply to this topic. Login to reply