none
Need a way to calculate on column

    Question

  • Hello Experts,

    I am facing problem in this type of scenario,i have made one stored procedure for  getting mine output.

    I need to fetch values based upon the two tables and these two table are not linked with any type of relationship.

    First Table:- GRPOData
    Second Table:- InvoiceData

    GRPOData Table gives following output:-

    GRPOID 	GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty
    100	550	01/01/2010	ABC Corp	ItemXXXA	25
    

    InvoiceData Table gives following output:-

    InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem	InvoiceQty
    11	100	02/02/2010	VDC Corp	ItemXXXA	15
    22	200	03/02/2010	HJK CORP	ItemXXXA	25
    33	300	05/02/2010	MMM CORP	ItemXXXA	35
    44	400	06/02/2010	GGG corp	ItemXXXA	45
    



    GRPOData Table is for Particular Item incoming entry
    and Invoice Data table is used for the containing sales data for item.

    So i want to know based upon the particular GRPONO 550 we recived ItemXXXA for 30 qty
    what shoulb the sales data i.e InvNo, InvQty etc for this paricular ItemXXXA for 30 qty by using this tables.

    I have made inner join between this table by taking GRPOItemCode=InvItem, as excpet of that there is no relationship. And added one more condition to fetch values from InvoiceData table that the sales data should be have condition that invoiceDate =>GRPODate.Because by taking the incoming entry of Item, then only salesData has been made for particular item.

    So now its showing data in this manner:-

    GRPOID	 GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty	InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem	InvoiceQty
    100	550	01/01/2010	ABC Corp	ItemXXXA	25	11	100	02/02/2010	VDC Corp	ItemXXXA	15
    						22	200	03/02/2010	HJK CORP	ItemXXXA	25
    						33	300	05/02/2010	MMM CORP	ItemXXXA	35
    						44	400	06/02/2010	GGG corp	ItemXXXA	45
    



    Now i have a problem in InvoiceQty which is coming here.

    The final result should be:-

    GRPOID	 GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty	InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem	InvoiceQty
    100	550	01/01/2010	ABC Corp	ItemXXXA	25	11	100	02/02/2010	VDC Corp	ItemXXXA	15
    						22	200	03/02/2010	HJK CORP	ItemXXXA	10
    



    Means first it should compare the GRPOTotQty (25) with InvoiceQty (15) and put 15 at first row,and then it go to second row of invoiceqty, its 25. But first row and second row invqty is greater then GRPOTotQty hence it will keep 10 at second row of Inv qty and it will ignore all the below row of Invoice.

    Thanks,
    ABHI

    Tuesday, February 23, 2010 9:37 AM

All replies

  • see this example
    declare @t table
    (
    	ItemId int,
    	Qty int
    )
    insert into @t
    select 1,25 union all
    select 2,55 
    
    
    declare @t1 table
    (
    	Id int identity(1,1),
    	ItemId int,
    	Qty int
    )
    insert into @t1
    select 1,15 union all
    select 1,25 union all
    select 2,25 union all
    select 2,10 union all
    select 2,15 union all
    select 2,55 
    
    declare @ItemId int
    set @ItemId = 2
    declare @TotalQty int
    set @TotalQty = (select Top 1 Qty from @t where ItemId = @ItemId)
    
    ;with mydata as
    (
    	select *,ROW_NUMBER() over (PARTITION by ITemId order by Qty) as rn from @t1 where ItemId = @ItemId
    )
    ,cte as
    (
    	select  Id,@ItemId as ItemId,@TotalQty as TotalQty,Qty,@TotalQty - Qty as RemQuantity   from mydata 
    	where rn = 1
    	union all
    	select t1.Id,@ItemId,@TotalQty as TotalQty,t1.Qty,c.RemQuantity - t1.Qty as RemQuantity   from cte c
    	inner join mydata t1 on  c.Id + 1= t1.Id		
    )
    select 
    Id,ItemId,
    CASe
    	when RemQuantity > 0 then Qty
    	else Qty + RemQuantity 
    end as Quantity
     from cte where RemQuantity > 0 or Qty + RemQuantity > 0
    Tuesday, February 23, 2010 9:55 AM
  • Hello Frnd, i think u all are not clear with my requirements, please visit this thread its gives you the detailed information about my requirement.

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

    Regards,

    ABHI 

    Thursday, February 25, 2010 5:34 AM