April 19, 2007 at 7:19 am
Hello everyone,
I must be having a mental block this morning. From the data set below, I need to determine the first 4-month window that each group achieves more than 10 units. I should know that "Alpha" gets more than 10 units first between 2007/04 and 2007/07, and that "Beta" gets more than 10 units first between 2007/01 and 2007/04.
Does anyone have any slick SQL code for this?
create table #MyHistory
(
MyGroup varchar(10),
MyMonth smalldatetime,
MyUnits int
)
insert #MyHistory select 'Alpha', '2007/01/01', 1
insert #MyHistory select 'Alpha', '2007/02/01', 3
insert #MyHistory select 'Alpha', '2007/03/01', 1
insert #MyHistory select 'Alpha', '2007/04/01', 3
insert #MyHistory select 'Alpha', '2007/05/01', 1
insert #MyHistory select 'Alpha', '2007/06/01', 8
insert #MyHistory select 'Alpha', '2007/07/01', 1
insert #MyHistory select 'Alpha', '2007/01/01', 20
insert #MyHistory select 'Alpha', '2007/08/01', 1
insert #MyHistory select 'Alpha', '2007/09/01', 15
insert #MyHistory select 'Alpha', '2007/10/01', 1
insert #MyHistory select 'Alpha', '2007/11/01', 1
insert #MyHistory select 'Alpha', '2007/12/01', 1
insert #MyHistory select 'Beta', '2007/01/01', 10
insert #MyHistory select 'Beta', '2007/02/01', 3
insert #MyHistory select 'Beta', '2007/03/01', 1
insert #MyHistory select 'Beta', '2007/04/01', 3
insert #MyHistory select 'Beta', '2007/05/01', 1
insert #MyHistory select 'Beta', '2007/06/01', 8
insert #MyHistory select 'Beta', '2007/07/01', 1
insert #MyHistory select 'Beta', '2007/01/01', 20
insert #MyHistory select 'Beta', '2007/08/01', 1
insert #MyHistory select 'Beta', '2007/09/01', 15
insert #MyHistory select 'Beta', '2007/10/01', 1
insert #MyHistory select 'Beta', '2007/11/01', 1
insert #MyHistory select 'Beta', '2007/12/01', 1
Regards,
Michael Lato
April 19, 2007 at 7:42 am
Don't know about slick but
SELECT MyGroup,MIN(MyMonth) AS [MyMonth]
FROM (SELECT a.MyGroup,a.MyMonth,SUM(b.MyUnits) As [MyUnits]
FROM #MyHistory a
INNER JOIN #MyHistory b
ON b.MyGroup = a.MyGroup
AND b.MyMonth >= a.MyMonth AND b.MyMonth < DATEADD(month,4,a.MyMonth)
GROUP BY a.MyGroup,a.MyMonth
HAVING SUM(b.MyUnits) >= 10) c
GROUP BY MyGroup
ORDER BY MyGroup
your result do not match your data
ie what are these two lines stuck in the middle?
insert #MyHistory select 'Alpha', '2007/01/01', 20
insert #MyHistory select 'Beta', '2007/01/01', 20
and Alpha 1st 4 month starts 2007/03 not 2007/04
Far away is close at hand in the images of elsewhere.
Anon.
April 19, 2007 at 7:47 am
Slick enough! Thanks for all your help.
Those two lines in the middle must have crept in during a cut and paste - I had to post this twice since the first one just disappeared.
Regards,
Michael Lato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply