select last time quantity starts increasing

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

SQL Server 2005 Forums

Transact-SQL (2005)

select last time quantity starts increasing

Author

Topic

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 15:34:33

Hi, i am trying to retreive the last date when the quantity starts increasing. Below is an example to show you what I mean:

date qty
2009-02-13 13
2009-04-27 33
2009-05-07 13
2009-12-10 15
2009-12-11 30
2009-12-12 40

Therefore the last time the quantity starts increasing is on
2009-05-07. The one before that is on 2009-02-13 but since i want the last time it starts increasing then my result should be 2009-05-07.

Mu query looks like this for now

Select max(M.[Date])
from atp_temp T
OUTER APPLY
(Select date from atp_temp
Where [qty] < T.[qty])M

But the result is not what i want as it retuns 2009-12-11.

I know it can be done in a while loop but im trying to avoid while loops.

Any ideas???

Thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 15:50:52

[code]Select max(at.dated) from atp_temp at
Left outer join atp_temp ap
on ap.qty<at.qty
Where ap.dated is null[/code]

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 16:05:43

thanks, this works but only for positive numbers.

for example:

2009-02-13 00:00:00 13
2009-04-27 00:00:00 -33
2009-05-07 00:00:00 5
2009-12-10 00:00:00 1
2009-12-11 00:00:00 85

the query retuns 2009-04-27 instead of 2009-12-10.

If I change the -33 to 33 then the correct date 2009-12-10 is retuned.

Is there a special way to handle integers with signs?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 16:07:57

quote:
Originally posted by eliewadi

thanks, this works but only for positive numbers.

for example:

2009-02-13 00:00:00 13
2009-04-27 00:00:00 -33
2009-05-07 00:00:00 5
2009-12-10 00:00:00 1
2009-12-11 00:00:00 85

the query retuns 2009-04-27 instead of 2009-12-10.

If I change the -33 to 33 then the correct date 2009-12-10 is retuned.

Is there a special way to handle integers with signs?

But you said quantity is increasing? what is your exact requirement?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 16:14:50

I don't understand the outputt you need. Considering your first eg, qty starting increasing on 2009-04-27 . Going by 2nd example, 2009-05-07.

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 16:22:59

It is the last time it starts increasing.

So in the first example
it is 2009-05-07 since it increase from 13 to 15 and never decreases after that. 2009-04-27 is not correct coz it decreases from 33 to 13

In the second example it is 09-12-10 because it goes from 1 to 85 and never decreases again. 2009-05-07 is not correct because it decreases from 5 to 1.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 16:31:09

[code]Select max(at.dated) from atp_temp at
Left outer join atp_temp ap
on ABS(ap.qty)< ABS(at.qty)
Where ap.dated is null[/code]

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 16:59:51

hi sodeep, the ABS function does not always work as the absolute of the negative number could end up bigger then the absolute of the positive number.

In the example i sent it works but if you change the numbers for something like:

2009-02-13 00:00:00 13
2009-04-27 00:00:00 -33
2009-05-07 00:00:00 5
2009-12-10 00:00:00 1
2009-12-11 00:00:00 -85

then the result is 2009-12-10 because ABS(1) is < ABS(-85) so it thinks that the quantity is increasing when it is not.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:06:44

[code]DECLARE @Sample TABLE
(
Row CHAR(2) NOT NULL,
Value TINYINT NOT NULL
)

INSERT @Sample
SELECT 'R1', 1 UNION ALL
SELECT 'R2', 4 UNION ALL
SELECT 'R3', 3 UNION ALL
SELECT 'R4', 5 UNION ALL
SELECT 'R5', 6 UNION ALL
SELECT 'R6', 4 UNION ALL
SELECT 'R7', 5 UNION ALL
SELECT 'R8', 7 UNION ALL
SELECT 'R9', 8

-- Peak values
SELECT s.Row,
s.Value
FROM @Sample AS s
OUTER APPLY (
SELECT TOP 1 o.Value
FROM @Sample AS o
WHERE o.Row < s.Row
ORDER BY o.Row DESC
) AS a
OUTER APPLY (
SELECT TOP 1 o.Value
FROM @Sample AS o
WHERE o.Row > s.Row
ORDER BY o.Row
) AS b
WHERE COALESCE(a.Value, 0) <= s.Value
AND COALESCE(b.Value, 0) <= s.Value

-- Antipeak values
SELECT s.Row,
s.Value
FROM @Sample AS s
OUTER APPLY (
SELECT TOP 1 o.Value
FROM @Sample AS o
WHERE o.Row < s.Row
ORDER BY o.Row DESC
) AS a
OUTER APPLY (
SELECT TOP 1 o.Value
FROM @Sample AS o
WHERE o.Row > s.Row
ORDER BY o.Row
) AS b
WHERE COALESCE(a.Value, 0) >= s.Value
AND COALESCE(b.Value, 0) >= s.Value

-- Antipeak value
SELECT TOP 1 s.Row,
s.Value
FROM @Sample AS s
OUTER APPLY (
SELECT TOP 1 o.Value
FROM @Sample AS o
WHERE o.Row < s.Row
ORDER BY o.Row DESC
) AS a
OUTER APPLY (
SELECT TOP 1 o.Value
FROM @Sample AS o
WHERE o.Row > s.Row
ORDER BY o.Row
) AS b
WHERE COALESCE(a.Value, 0) >= s.Value
AND COALESCE(b.Value, 0) >= s.Value
ORDER BY s.Row DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 17:24:50

One Last try:

Select max(at.dated) from atp_temp  at
Left outer join atp_temp  ap
on ABS(ap.qty)< at.qty
Where ap.dated is null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 17:29:04

Try this sample data

SELECT	'2009-02-13',  13 UNION ALL
SELECT	'2009-04-27',   1 UNION ALL
SELECT	'2009-05-07',   5 UNION ALL
SELECT	'2009-12-10',   3 UNION ALL
SELECT	'2009-12-11',  85

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 17:31:29

quote:
Originally posted by Peso

Try this sample data
SELECT '2009-02-13', 13 UNION ALL
SELECT '2009-04-27', 1 UNION ALL
SELECT '2009-05-07', 5 UNION ALL
SELECT '2009-12-10', 3 UNION ALL
SELECT '2009-12-11', 85

E 12°55'05.63"
N 56°04'39.26"

That was just a try from DBA side

.Thanks peso for pointing out.

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 17:50:28

sodeep, the example works but the problem is when I have negative numbers.

Example below with the last query returns 4/27/2009
instead of 12/10/2009

2/13/2009 13
4/27/2009 -33
5/7/2009 70
12/10/2009 52
12/11/2009 85

I am going to try peso query. thanks guys for your help, if u have any other suggestions i'll be around, i have to finish this

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 18:20:00

hi peso , this seems to work but i dont know why in some odd cases it doesnt: ex:

2/13/2009 13
5/13/2009 33

it retuns nothing. any idea?

SELECT TOP 1 s.date,
s.qty
FROM atp_temp AS s
OUTER APPLY (
SELECT TOP 1 o.qty
FROM atp_temp AS o
WHERE o.date < s.date
ORDER BY o.date DESC
) AS a
OUTER APPLY (
SELECT TOP 1 o.qty
FROM atp_temp AS o
WHERE o.date > s.date
ORDER BY o.date
) AS b
WHERE COALESCE(a.qty, 0) >= s.qty
AND COALESCE(b.qty, 0) >= s.qty
ORDER BY s.date DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:26:07

Are those two the only sample data, or addition to previously posted sample data?

E 12°55'05.63"
N 56°04'39.26"

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 18:28:34

these are not an addition, they are the only two sample data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:36:44

Well, if someone doesn't beat me to it, I'll have a look at it again tomorrow at work.
It's 12:30 am here right now...

E 12°55'05.63"
N 56°04'39.26"

eliewadi
Starting Member

20 Posts

Posted - 2009-02-17 : 19:01:43

yes its very late...thanks man

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-18 : 02:17:01

Well, my first suggestion was about finding peaks, that's the reason for COALESCE( ... , 0)
When you want the opposite, use COALESCE( ... , 32767)

DECLARE	@Sample TABLE
	(
		Row DATETIME NOT NULL,
		Value SMALLINT NOT NULL
	)

INSERT	@Sample
SELECT	'02/13/2009',  13 UNION ALL
SELECT	'04/27/2009', -33 UNION ALL
SELECT	'05/07/2009',  70 UNION ALL
SELECT	'12/10/2009',  52 UNION ALL
SELECT	'12/11/2009',  85

SELECT TOP 1	s.Row,
		s.Value
FROM		@Sample AS s
OUTER APPLY	(
			SELECT TOP 1	o.Value
			FROM		@Sample AS o
			WHERE		o.Row < s.Row
			ORDER BY	o.Row DESC
		) AS a
OUTER APPLY	(
			SELECT TOP 1	o.Value
			FROM		@Sample AS o
			WHERE		o.Row > s.Row
			ORDER BY	o.Row
		) AS b
WHERE		COALESCE(a.Value, 32767) >= s.Value
		AND COALESCE(b.Value, 32767) >= s.Value
ORDER BY	s.Row DESC
GO

DECLARE	@Sample TABLE
	(
		Row DATETIME NOT NULL,
		Value SMALLINT NOT NULL
	)

INSERT	@Sample
SELECT	'02/13/2009',  13 UNION ALL
SELECT	'05/13/2009',  33

SELECT TOP 1	s.Row,
		s.Value
FROM		@Sample AS s
OUTER APPLY	(
			SELECT TOP 1	o.Value
			FROM		@Sample AS o
			WHERE		o.Row < s.Row
			ORDER BY	o.Row DESC
		) AS a
OUTER APPLY	(
			SELECT TOP 1	o.Value
			FROM		@Sample AS o
			WHERE		o.Row > s.Row
			ORDER BY	o.Row
		) AS b
WHERE		COALESCE(a.Value, 32767) >= s.Value
		AND COALESCE(b.Value, 32767) >= s.Value
ORDER BY	s.Row DESC

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 02:40:28

Would this work ?


select 
	min(dated) 
from 
	atp_temp 
where 
	dated >=
			(select ISNULL(max(dated),'1900-01-01') from(
			select (dated),qty-(select top 1 qty from atp_temp where dated<a.dated order by dated desc) delta from atp_temp a 
			)s
			where delta<0
			)
	and dated <=
	
		(select max(dated) from(
		select (dated),qty-(select top 1 qty from atp_temp where dated<a.dated order by dated desc) delta from atp_temp a 
		)s
where delta>0)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 02:48:02

Peso, Try your post with

SELECT	'02/13/2009',  13 UNION ALL
SELECT	'05/13/2009',  33 union all
SELECT	'09/13/2009',  10

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources