Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: SQL Running Subtraction and Deviation

    Code:
    --    Just a brief of business scenario is table has been created for a good receipt. 
    --    So here we have good expected line with PurchaseOrder(PO) in first few line. 
    --    And then we receive each expected line physically and that time these quantity may be different 
    --    due to business case like quantity may damage and short quantity like that. 
    --    So we maintain a status for that eg: OK, Damage, also we have to calculate short quantity 
    --    based on total of expected quantity of each item and total of received line.
    
    
    if object_id('DEV..Temp','U') is not null
    drop table Temp
    
    CREATE TABLE Temp 
    (        
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,        
    Item VARCHAR(32),
    PO VARCHAR(32) NULL,        
    ExpectedQty INT NULL,
    ReceivedQty INT NULL,
    [STATUS] VARCHAR(32) NULL,
    BoxName VARCHAR(32) NULL
    )
    
    
    --  Please see first few line with PO data will be the expected lines, 
    --  and then rest line will be received line
    
    INSERT INTO TEMP (Item,PO,ExpectedQty,ReceivedQty,[STATUS],BoxName)
    SELECT 'ITEM01','PO-01','30',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM01','PO-02','20',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM02','PO-01','40',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM03','PO-01','50',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM03','PO-02','30',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM03','PO-03','20',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM04','PO-01','30',NULL,NULL,NULL UNION ALL 
    SELECT 'ITEM01',NULL,NULL,'20','OK','box01' UNION ALL 
    SELECT 'ITEM01',NULL,NULL,'25','OK','box02' UNION ALL 
    SELECT 'ITEM01',NULL,NULL,'5','DAMAGE','box03' UNION ALL 
    SELECT 'ITEM02',NULL,NULL,'38','OK','box04' UNION ALL 
    SELECT 'ITEM02',NULL,NULL,'2','DAMAGE','box05' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'30','OK','box06' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'30','OK','box07' UNION ALL 
    SELECT 'ITEM03',NULL,NULL,'10','DAMAGE','box09' UNION ALL
    SELECT 'ITEM04',NULL,NULL,'25','OK','box10' 
    
    
    
    --  Below Table is my expected result based on above data. 
    --  I need to show those data following way. 
    --  So I appreciate if you can give me an appropriate query for it. 
    --  Note: first row is blank and it is actually my table header. :) 
    -- Conditions : any of row, we cant have ReceivedQty, DamageQty and ShortQty 
    -- values more than ExpectedQty value. Item03 has this scenario
    -- Query should run in SQL 2000 DB
    
    SELECT  ''as'ITEM', ''as'PO#', ''as'ExpectedQty',''as'ReceivedQty',''as'DamageQty' ,''as'ShortQty' UNION ALL 
    SELECT 'ITEM01','PO-01','30','30','0' ,'0'  UNION ALL 
    SELECT 'ITEM01','PO-02','20','15','5' ,'0'  UNION ALL 
    SELECT 'ITEM02','PO-01','40','38','2' ,'0'  UNION ALL 
    SELECT 'ITEM03','PO-01','50','50','0' ,'0'  UNION ALL 
    SELECT 'ITEM03','PO-02','30','20','10' ,'10'  UNION ALL 
    SELECT 'ITEM03','PO-03','20','0','0','20' UNION ALL 
    SELECT 'ITEM04','PO-01','30','25','0' ,'5'

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    For ITEM01
    How do you know whether box01 is for 'PO-01' or 'PO-02'
    How do you know whether box02 is for 'PO-01' or 'PO-02'
    How do you know whether box03 is for 'PO-01' or 'PO-02'

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah. I don't see how to determine what PO a damages quantity is associated with.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I think there is a typo in the first post
    Code:
    SELECT 'ITEM03','PO-02','30','10','10' ,'10'  UNION ALL
    While it is not impossible to program this in pure SQL, it will need quite complex coding. Debugging and maintenance will cause headaches.

    I think solving this in a procedural way (SP, Java, C#, ..) is much more sane.

    This is part of a possible solution. It only becomes more complex after this. I haven't worked it out till the end.
    Code:
    DROP TABLE #Temp2 
    CREATE TABLE #Temp2(      
    	Id				INT			NOT NULL,  
    	Item			VARCHAR(32)	NOT NULL,
    	PO				VARCHAR(32)	NOT NULL,        
    	ExpectedQty		INT			NOT NULL,
    	ReceivedQty		INT				NULL,
    	DamagedQty		INT				NULL,
    	ShortQty		INT				NULL
    )
    INSERT INTO #Temp2(Id, Item, PO, ExpectedQty)
    SELECT Id, Item, PO, ExpectedQty
    FROM #Temp
    WHERE ExpectedQty IS NOT NULL
    
    --SELECT * FROM #Temp2
    
    SELECT Item, 
    	SUM(COALESCE(ExpectedQty, 0)) as TotalExpectedQty, 
    	SUM(CASE WHEN STATUS = 'OK' THEN ReceivedQty ELSE 0 END) as TotalReceivedQty, 
    	SUM(CASE WHEN STATUS = 'DAMAGE' THEN ReceivedQty ELSE 0 END) as TotalDamageQty
    FROM #Temp
    GROUP BY Item
    GO
    
    WITH CTE AS(
    SELECT Item, 
    	SUM(CASE WHEN STATUS = 'OK' THEN ReceivedQty ELSE 0 END) as TotalReceivedQty, 
    	SUM(CASE WHEN STATUS = 'DAMAGE' THEN ReceivedQty ELSE 0 END) as TotalDamagedQty
    FROM #Temp
    WHERE ExpectedQty IS NULL
    GROUP BY Item
    )
    UPDATE U
    	SET ReceivedQty = CASE WHEN CTE.TotalReceivedQty - 
    				COALESCE((	SELECT SUM(ExpectedQty)
    							FROM #Temp as T
    							WHERE T.ExpectedQty IS NOT NULL AND
    								T.Item = U.Item AND
    								T.Id < U.Id
    							), 0) > ExpectedQty
    		THEN ExpectedQty
    		ELSE CASE WHEN CTE.TotalReceivedQty - 
    				COALESCE((	SELECT SUM(ExpectedQty)
    							FROM #Temp as T
    							WHERE T.ExpectedQty IS NOT NULL AND
    								T.Item = U.Item AND
    								T.Id < U.Id
    							), 0) < 0 
    					THEN 0
    					ELSE CTE.TotalReceivedQty - 
    				COALESCE((	SELECT SUM(ExpectedQty)
    							FROM #Temp as T
    							WHERE T.ExpectedQty IS NOT NULL AND
    								T.Item = U.Item AND
    								T.Id < U.Id
    							), 0)
    			END	
    	END
    FROM #Temp2 as U
    	INNER JOIN CTE ON
    		U.Item = CTE.Item
    GO
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •