QUERY - 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

SQL Server 2005 Forums

Transact-SQL (2005)

QUERY

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-27 : 04:01:53

Hi

I have table like this….i need to find on_hand and order.

I have the values for Min and Max
Min =50 and Max = 75
So based on the min and max values find out the On_hand and order


Period	      Demand	      On_hand	   order	
1	70		
2	29		
3	40		
4	57		
5	33		
6	44

Conditions

Period one we took on_hand value is zero and order value is the max value 80
Period 2 we have to add the values previous On_hand and order value minus (-) current demand is On_hand for period two
For example


1	70	0	80    
2	29	10	70   Onhand(70-80+0) order max-current on_hand

Order value for period two is 70(which means max is 80 we have current On_hand is 10 (80-10)=70 is order values for period two

Every time we have to check the On_hand value is > min value that scenario the order value is zero (0).

The result should be like this..


Period	Demand On_hand	order
1	70	0	80
2	29	10	70
3	40	51	0     ---here on_hand is > min so order is 0
4	57	11	69
5	33	23	57
6	44	47	33

Please help out this..

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-27 : 05:02:55

Hi

Please help on this

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 09:00:03

How about providing schema definitions, sample data and expected output? You're almost there but there are no columns on your expected output and it's unclear which columns the data belongs to in your initial table.

Is this a homework assignment?

An infinite universe is the ultimate cartesian product.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-27 : 10:34:18

This looks remarkably similar to:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131067

Suggests that th OP is not really trying to learn.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-28 : 05:31:26

Try this....


DECLARE @t TABLE
(
    Period int NOT NULL
    ,Demand int NOT NULL
)
INSERT INTO @t
SELECT 1,  70  UNION ALL
SELECT 2,  29  UNION ALL
SELECT 3,  40  UNION ALL
SELECT 4,  57  UNION ALL
SELECT 5,  33  UNION ALL
SELECT 6,  44  

-- *** End Test Data ***

DECLARE @MAX FLOAT
DECLARE @MIN FLOAT
SET @MAX = 80
SET @MIN = 50

;WITH LotOnHand (Period, Demand, OnHand,Lot)
AS
(
    SELECT Period, Demand,
        CAST( 0 AS FLOAT),
		@MAX
    FROM @t
    WHERE Period = 1
    
    UNION ALL
    
    SELECT T.Period, T.Demand
        ,CASE
            WHEN (L.Onhand + L.LOT)-L.DEMAND <= @MIN
            THEN (L.Onhand + L.LOT)-L.DEMAND
            ELSE (L.Onhand + L.LOT)-L.DEMAND
        END
		,CASE
            WHEN (L.Onhand + L.LOT)-L.DEMAND <= @MIN
            THEN @MAX-((L.Onhand + L.LOT)-L.DEMAND)
            ELSE 0
        END
    FROM @t T
        JOIN LotOnHand L
            ON T.Period = L.Period + 1
)
SELECT *
FROM LotOnHand
ORDER BY Period

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources