determing min_max and max_max - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

determing min_max and max_max

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-29 : 02:10:14

hi,

i'm actually looking for intuitive way to determine the peaks in sales. instead of going through hunders of rows to determine the first peak and the last peak of sale, i would rather write sql code to do it for me. i just need to find it more intuitive, more softer.

sample code


create  table temp
(id int
,Y smallint
,M smallint
,running_percent decimal(6,3)
,quantity_cum int
,productID varchar(10)
) 

insert into temp

select 1,2007,6,0.002,4,'ABC' union all
select 2,2007,7,3.625,1483,'ABC' union all
select 3,2007,8,9.117,5227,'ABC' union all
select 4,2007,9,7.547,8137,'ABC' union all
select 5,2007,10,5.496,10559,'ABC' union all
select 6,2007,11,8.101,13864,'ABC' union all
select 7,2007,12,10.231,18038,'ABC' union all
select 8,2008,1,7.474,21087,'ABC' union all
select 9,2008,2,8.712,24641,'ABC' union all
select 10,2008,3,6.719,27382,'ABC' union all
select 11,2008,4,5.525,29636,'ABC' union all
select 12,2008,5,4.542,31489,'ABC' union all
select 13,2008,6,2.032,32318,'ABC' union all
select 14,2008,7,1.917,33100,'ABC' 

select * from temp

from this sample code you will see the calculated running_percent telling that the first peak is: 9.117 (year/month 2007/8), the second one 10.231 (2007/12) and the last peak 8.712 (2008/2).

to tell what and how to find a peak i believe at least 2 months or two values of running_percent must be in trend (either going up or going down). and also some threshold should be defined because the last peak 8.712 my intuition is telling me that i wasn't actually a peak more a sway and i should ignore that.

thank you for your idead.

:)

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 02:34:39

I've tried to indicate the "strength" by indicating whether the preceeding/suceeding 3 months were up or down.

If month-3 or month+3 is down on the peak but NOT down on month-2/month+2 I show a small "v", if it continues to be downward I show a large "V". Well, that's if I've got the code right!


SELECT	[Weight] = 
		CASE WHEN L3.id IS NULL THEN '^' 
			WHEN L3.running_percent < L2.running_percent THEN 'V' 
			WHEN L2.id IS NULL THEN 'x' 
			ELSE 'v' END
		+ CASE WHEN L2.id IS NULL THEN '^' ELSE 'V' END
		+ 'V|V'
		+ CASE WHEN R2.id IS NULL THEN '^' ELSE 'V' END
		+ CASE WHEN R3.id IS NULL THEN '^' 
			WHEN R3.running_percent < R2.running_percent THEN 'V' 
			WHEN R2.id IS NULL THEN 'x' 
			ELSE 'v' END,
	C.*
FROM	temp AS C
	JOIN temp AS L1
		 ON L1.id = C.id-1
		AND L1.running_percent < C.running_percent
	LEFT OUTER JOIN temp AS L2
		 ON L2.id = L1.id-1
		AND L2.running_percent < L1.running_percent
	LEFT OUTER JOIN temp AS L3
		 ON L3.id = L2.id-1
		AND L3.running_percent < L1.running_percent

	JOIN temp AS R1
		 ON R1.id = C.id+1
		AND R1.running_percent < C.running_percent
	LEFT OUTER JOIN temp AS R2
		 ON R2.id = R1.id+1
		AND R2.running_percent < R1.running_percent
	LEFT OUTER JOIN temp AS R3
		 ON R3.id = R2.id+1
		AND R3.running_percent < R1.running_percent

values in the first/last three months of data will not have preceding/succeeding values and ought to be handled as special case - I'm just going to assume that absence of data is a upward movement which is a bit pants!

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 02:47:57

Version 2 - takes care of end conditions, shows preceding/succeeding 3 months and whether up/down, plus the count of months lower than the peak (so a fall/rise, but where the rise is still below the peak, is detectable)


SELECT	[Weight] = 
		CASE
			WHEN L3.running_percent > L2.running_percent THEN '^' 
			WHEN L3.running_percent < L2.running_percent THEN 'V' 
			WHEN L3.running_percent < L1.running_percent THEN 'v' 
			WHEN L3.id IS NULL THEN 'x' 
			ELSE '?' END
		+ CASE
			WHEN L2.running_percent > L1.running_percent THEN '^' 
			WHEN L2.running_percent < L1.running_percent THEN 'V' 
			WHEN L2.running_percent < C.running_percent THEN 'v' 
			WHEN L2.id IS NULL THEN 'x' 
			ELSE '?' END
		+ 'V|V'
		+ CASE
			WHEN R2.running_percent > R1.running_percent THEN '^' 
			WHEN R2.running_percent < R1.running_percent THEN 'V' 
			WHEN R2.running_percent < C.running_percent THEN 'v' 
			WHEN R2.id IS NULL THEN 'x' 
			ELSE '?' END
		+ CASE
			WHEN R3.running_percent > R2.running_percent THEN '^' 
			WHEN R3.running_percent < R2.running_percent THEN 'V' 
			WHEN R3.running_percent < R1.running_percent THEN 'v' 
			WHEN R3.id IS NULL THEN 'x' 
			ELSE '?' END,
	[Left] = CASE WHEN L2.running_percent < C.running_percent THEN 1 ELSE 0 END
		+ CASE WHEN L3.running_percent < C.running_percent THEN 1 ELSE 0 END,
	[Right] = CASE WHEN R2.running_percent < C.running_percent THEN 1 ELSE 0 END
		+ CASE WHEN R3.running_percent < C.running_percent THEN 1 ELSE 0 END,
	[L3] = L3.running_percent,
	[L2] = L2.running_percent,
	[L1] = L1.running_percent,
	[C] = C.running_percent,
	[R1] = R1.running_percent,
	[R2] = R2.running_percent,
	[R3] = R3.running_percent,
	C.*
FROM	temp AS C
	JOIN temp AS L1
		 ON L1.id = C.id-1
		AND L1.running_percent < C.running_percent
	LEFT OUTER JOIN temp AS L2
		 ON L2.id = L1.id-1
	LEFT OUTER JOIN temp AS L3
		 ON L3.id = L2.id-1
	JOIN temp AS R1
		 ON R1.id = C.id+1
		AND R1.running_percent < C.running_percent
	LEFT OUTER JOIN temp AS R2
		 ON R2.id = R1.id+1
	LEFT OUTER JOIN temp AS R3
		 ON R3.id = R2.id+1

Weight  Left Right ID
------- ---- ----- --
xVV|VV^ 1    2    3
^VV|V^V 2    2    7
V^V|VVV 1    2

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-29 : 03:37:56

thank you.

this will be perfect. :)

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-29 : 03:40:35

second version is even better!

thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources