processing negative values with sql possibly using

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 2008 Forums

Transact-SQL (2008)

processing negative values with sql possibly using

Author

Topic

jdelwood
Starting Member

4 Posts

Posted - 2012-06-14 : 12:33:08

I have a data set that lists the date and quantity of future stock of products. Occasionally our demand outstrips our future supply and we wind up with a negative future quantity. I need to factor that future negative quantity into previous supply so we don't compound the problem by overselling our supply.

In the following data set, I need to prepare for demand on 10-19 by applying the negative quantity up the chain until i'm left with a positive quantity:


"ID","SKU","DATE","QUANTITY"
"1","001","2012-06-22","1656"
"2","001","2012-07-13","1986"
"3","001","2012-07-27","-283"
"4","001","2012-08-17","2718"
"5","001","2012-08-31","-4019"
"6","001","2012-09-14","7212"
"7","001","2012-09-21","782"
"8","001","2012-09-28","2073"
"9","001","2012-10-12","1842"
"10","001","2012-10-19","-12159"

I need to get it to this:

"ID","SKU","DATE","QUANTITY"
"1","001","2012-06-22","1656"
"2","001","2012-07-13","152"

I have looked at using a while loop as well as an outer apply but cannot seem to find a way to do this yet. Any help would be much appreciated. This would need to work for sql server 2008 R2.

Here's another example:

"1","002","2012-07-13","1980"
"2","002","2012-08-10","-306"
"3","002","2012-09-07","826"

Would become:

"1","002","2012-07-13","1674"
"3","002","2012-09-07","826"

Essentially what i need to do is start with the furthest out date with a negative value, and work back until I have my first positive value. Then to my next furthest out date and work back until I again have a positive value. So for the first example, I start with -12,159 and work back until row 2, which finally gives me a positive value. Ex 2, -306 works back to 1980 giving me 1674.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-14 : 13:42:27

hack

works for either data sample. my question is why are you even allowing negative values in the first place? there seems to be something off in this approach?




declare @jdelwood table(ID int,SKU varchar(25),[DATE] datetime,QUANTITY float)
insert into  @jdelwood
SELECT 1,'002','2012-07-13','1980'
UNION
SELECT 2,'002','2012-08-10','-306'
UNION
SELECT 3,'002','2012-09-07','826'
/*
SELECT 1,'001','2012-06-22','1656'
UNION
SELECT '2','001','2012-07-13','1986'
UNION
SELECT '3','001','2012-07-27','-283'
UNION
SELECT '4','001','2012-08-17','2718'
UNION
SELECT '5','001','2012-08-31','-4019'
UNION
SELECT '6','001','2012-09-14','7212'
UNION
SELECT '7','001','2012-09-21','782'
UNION
SELECT '8','001','2012-09-28','2073'
UNION
SELECT '9','001','2012-10-12','1842'
UNION
SELECT '10','001','2012-10-19','-12159'
*/
declare @id int
declare @firstnegativeid int
declare @lastnegativeid int

SELECT top 1 @id = ID from @jdelwood order by [DATE] ASC
select top 1 @firstnegativeid = ID From @jdelwood where QUANTITY < 0 order by [DATE] ASC  

PRINT @firstnegativeid
 
IF (select top 1 QUANTITY From @jdelwood where ID > @id order by [DATE] ASC) > -1
	BEGIN
		PRINT 'First Entry Point'
		SELECT *
		  FROM @jdelwood
		 where ID = @id	
		 UNION
		SELECT a.*, SUM(QUANTITY)
		  FROM @jdelwood j1
		  cross apply (select top 1 ID ,SKU ,[DATE]  From @jdelwood where ID > @id) a
		 where j1.ID > @id
		 group by a.DATE, a.ID, a.SKU
	END
ELSE
	BEGIN
		PRINT 'Second Entry Point'

		SELECT *
		  FROM @jdelwood
		 where ID <> @id and ID <> @firstnegativeid
		 UNION
		SELECT a.*, SUM(QUANTITY)
		  FROM @jdelwood j1
		  cross apply (select top 1 ID ,SKU ,[DATE]  From @jdelwood where ID = @id) a
		 where j1.ID IN ( @id, @firstnegativeid)
		 group by a.DATE, a.ID, a.SKU	
	END

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-14 : 13:57:06

what if it is like this

SELECT 1,'001','2012-06-22','1656'
UNION
SELECT '2','001','2012-07-13','1986'
UNION
SELECT '3','001','2012-07-27','-283'
UNION
SELECT '4','001','2012-08-17','2718'
UNION
SELECT '5','001','2012-08-31','-4019'
UNION
SELECT '6','001','2012-09-14','7212'
UNION
SELECT '7','001','2012-09-21','782'
UNION
SELECT '8','001','2012-09-28','2073'
UNION
SELECT '9','001','2012-10-12','1842'
UNION
SELECT '10','001','2012-10-19','-12159'
UNION
SELECT '11','001','2012-10-29','12159'

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jdelwood
Starting Member

4 Posts

Posted - 2012-06-14 : 14:17:03

Thanks @yosiasz - I would look for this result:

'1','001','2012-06-22','1656'

'2','001','2012-07-13','402'

'6','001','2012-09-14','7212'

'7','001','2012-09-21','782'

'8','001','2012-09-28','2073'

'9','001','2012-10-12','1842'

'10','001','2012-10-19','0'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-14 : 14:21:20

why? sounds very arbitrary to me.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jdelwood
Starting Member

4 Posts

Posted - 2012-06-14 : 14:37:28

Row 11 is applied against row 10 and left at that since it is a non negative value. Row 5 is applied all the way up to row 2 since that again is the first non negative outcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:38:31

so will date to prepare for demand (in this case 10-19) be provided by user?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jdelwood
Starting Member

4 Posts

Posted - 2012-06-14 : 16:16:24

The date is provided by our inventory system and is view-able by users.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources