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-12 : 10:39:37

Hi

I have table below format…I need to find out Lot & On hand..


		Ordering_quantity	6 
Period	Demand data	Lot Quantity	On hand
1	5		
2	6		
3	7		
4	3		
5	6		
6	3		
7	4		
8	6		
9	3		
10	5		
11	6		
12	3

A condition meets below.
1. First time I need to check the Ordering_quantity > Demand in first row. If it’s false will take demand value for when conditions meet true. If conditions is true will take Ordering_quantity else will take Ordering_quantity

The output like this....

                                 
Period	Demand	Lot 	On_hand	 how_works (understand on_hand s came)
1	5	6	1	 6-5			
2	6	6	1	 1+6-6
3	7	6	0	 1+6-7
4	3	6	3	 0+6-3
5	6	6	3	 3+6-6
6	3	0	0	 3-3		--no need to take rqty
7	4	6	2	 0+6-4
8	6	6	2	 2+6-6
9	3	6	5	 2+6-3
10	5	0	0	 5-5		--no need to take rqty
11	6	6	0	 0+6-6
12	3	6	3	 0+6-3

On_hand

Period 1
we can take 6(Ordering_quantity)-5(demand) so on_hand = 1
Period 2
we can take 1(previous on_hand)+6(Ordering_quantity)-6(current demand)
here now on_hand = 1
.
.
.

Period6
We need not take Ordering_quantity bcoz previous on_hand is 3 so current demand is only three so we can use 3(on_hand)-3demans
…

Lot

Where ever we can take the Ordering_quantity that place we can update LOT= Ordering_quantity
ELSE
0(zero).

Please help out this....

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-12 : 11:10:36

Hi Guys..

Please help out this...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-12 : 11:57:55

I am not sure what you are asking, but it seems as though you need either recursion or iteration.
The following uses recursion as it is easier to write:

-- *** Test Data ***
DECLARE @t TABLE
(
    Period int NOT NULL
    ,Demand int NOT NULL
)
INSERT INTO @t
SELECT 1, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 6, 3 UNION ALL
SELECT 7, 4 UNION ALL
SELECT 8, 6 UNION ALL
SELECT 9, 3 UNION ALL
SELECT 10, 5 UNION ALL
SELECT 11, 6 UNION ALL
SELECT 12, 3
-- *** End Test Data ***

DECLARE @OrderingQuantity int
SET @OrderingQuantity = 6

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

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-13 : 03:35:11

HI

Its not working with
DECLARE @OrderingQuantity FLOAT
SET @OrderingQuantity = 3.474

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources