How to write a cursor in sql to compare two table data and finding fifocost from one by one record

Question

0.00/5 (No votes)

See more:

Hi,

I want to write a cursor to compare two tables data and find a fifo cost.

I have written this query for a cursor but it is not getting correct cost.

SQL

USE [RISE_DB]
GO
/****** Object:  StoredProcedure [dbo].[SP_GETFIFOCOST]    Script Date: 09/11/2015 14:21:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
Begin
  Declare @qty decimal(18,2),@cost decimal(18,2),@line int
  Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
  Declare MyCur Cursor for 
    select QTY_IN,UNIT_COST,LINE 
    from STOCKDETAILS  
    where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') 
    and PRODUCT_NO=@pid order   by STOCK_DATE
  Declare @fqty decimal(18,2)
  Open MyCur
  Fetch next from mycur into @qty,@cost,@line
  While @@Fetch_Status=0
  Begin
    Declare MyCur1 Cursor for 
    select QTY_IN,UNIT_COST,LINE 
    from STOCKDETAILS  
    where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') 
    and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur1
    Fetch next from mycur1 into @qty1,@cost1,@line1
    While @@Fetch_Status=0
    Begin
      if @qty>=@qty1 
        set @qty=@qty-@qty1
      set @fqty=@fqty+@qty
      if @qty<@qty1 
        set @qty=@qty-@qty1
      set @fqty=@fqty+@qty-@qty1
      Fetch Next From MyCUr into @qty,@cost,@line
      Fetch Next From MyCUr1 into @qty1,@cost1,@line1
      while @fqty=0
      Begin
        update STOCKDETAILS 
        set FIFO_COST=@cost 
        where LINE=@line1
      End
    End
    Close Mycur
    Close Mycur1
    Deallocate Mycur
    Deallocate Mycur1
  End
End

Exec SP_GETFIFOCOST 'F1'

I have written same logic at the front end like this

C#

b = table2.Rows.Count - 1;
                   try
                   {
                       for (a = table1.Rows.Count - 1; a >= 0; a--)
                       {
                           total();
                           while (fqty != 0 && fqty >= 0)
                           {
                               if (b >= 0)
                               {
                                   b = b - 1;
                                   total();
                               }
                               else
                               {
                                   fqty = 0;
                               }
                           }
                       }
                   }
                   catch (Exception EX) { }

C#

void total()
       {
           try
           {
               fout = Convert.ToDecimal(table2.Rows[b]["QTY_OUT"].ToString());
           }
           catch (Exception EX) { fout = 0; }
           try
           {
               fin = Convert.ToDecimal(table1.Rows[a]["QTY_IN"].ToString());
           }
           catch (Exception EX) { fin = 0; }
           if (fin >= fout)
           {
               fqty = fin - fout;
               table1.Rows[a]["QTY_IN"] = fqty;
               try
               {
                   string fc = table1.Rows[a]["UNIT_COST"].ToString();
                   string sn = table2.Rows[b]["LINE"].ToString();
                   TRcmd.Connection = cn;
                   TRcmd.CommandText = "update STOCKDETAILS set FIFO_COST='" + fc + "' where LINE='" + sn + "'";
                   TRcmd.CommandType = CommandType.Text;
                   TRcmd.ExecuteNonQuery();
               }
               catch (Exception EX) { }
               if (fqty == 0)
               {
                   b = b - 1;
               }

           }
           else
           {
               fqty = 0;
               table1.Rows[a]["QTY_IN"] = fqty;
               try
               {
                   string fc = table1.Rows[a]["UNIT_COST"].ToString();
                   string sn = table2.Rows[b]["LINE"].ToString();
                   TRcmd.Connection = cn;
                   TRcmd.CommandText = "update STOCKDETAILS set FIFO_COST='" + fc + "' where LINE='" + sn + "'";
                   TRcmd.CommandType = CommandType.Text;
                   TRcmd.ExecuteNonQuery();
               }
               catch (Exception EX) { }
               a = a - 1;
               try
               {
                   table1.Rows[a]["QTY_IN"] = Convert.ToDecimal(table1.Rows[a]["QTY_IN"].ToString()) + (fin - fout);
               }
               catch (Exception EX) { }
               a = a + 1;
               b = b - 1;
           }

       }

Would u pls help me

Posted 10-Sep-15 23:18pm

TarunKumarSusarapu

Updated 14-Sep-15 18:53pm

v2

Add a Solution

2 solutions

Solution 1

I have code-tagged the sql and added indents.

This is always good practice because A: our poor eyes and B: you can spot mistakes like your if statements:

Current:

SQL

if @qty>=@qty1
  set @qty=@qty-@qty1
set @fqty=@fqty+@qty
if @qty<@qty1
  set @qty=@qty-@qty1
set @fqty=@fqty+@qty-@qty1

I assume you do not intend to set @fqty twice before using it so:
Fixed:

SQL

if @qty>=@qty1
BEGIN
  set @qty=@qty-@qty1
  set @fqty=@fqty+@qty
END
if @qty<@qty1
BEGIN
  set @qty=@qty-@qty1
  set @fqty=@fqty+@qty-@qty1
END

Just as with C#, without a syntax wrapper an 'if' statement only applies to the next line.

I hope that helps ^_^
Andy

PS: This was just what I spotted. There may be logical errors for all I know but the scenario is too complex for me to replicate tests.

UPDATE:

I have been playing with the C# version first. Once I can simplify this down then the cursor will be much easier to write.

Here is what I have so far:

C#

class Class1
{

    public DataTable Table1;
    public DataTable Table2;
    private decimal _quantity;
    private int _a, _b;

    public SqlConnection Cn;
    public SqlCommand Rcmd;

    public void Test()
    {
        _b = Table2.Rows.Count - 1;
        for (_a = Table1.Rows.Count - 1; _a >= 0; _a--)
        {
            Total();
            while (_quantity > 0)
            {
                if (_b >= 0)
                {
                    _b--;
                    Total();
                }
                else
                    _quantity = 0;
            }
        }
    }
    void Total()
    {
        decimal dIn, dOut;

        decimal.TryParse(Table2.Rows[_b]["QTY_OUT"].ToString(), out dOut);
        decimal.TryParse(Table2.Rows[_b]["QTY_IN"].ToString(), out dIn);

        Table1.Rows[_a]["QTY_IN"] = _quantity;

        UpdateStock(Table1.Rows[_a]["UNIT_COST"].ToString(), Table2.Rows[_b]["LINE"].ToString());

        if (dIn >= dOut)
            _quantity = dIn - dOut;
        else
        {
            _quantity = 0;
            Table1.Rows[_a - 1]["QTY_IN"] = Convert.ToDecimal(Table1.Rows[_a - 1]["QTY_IN"].ToString()) + (dIn - dOut);
        }
        if (_quantity == 0)
            _b--;
    }
    private void UpdateStock(string unitCost, string line)
    {
        Rcmd.Connection = Cn;
        Rcmd.CommandText = "update STOCKDETAILS set FIFO_COST=@UnitPrice where LINE=@Line";
        Rcmd.CommandType = CommandType.Text;

        Rcmd.Parameters.Add(new SqlParameter("@UnitPrice", SqlDbType.VarChar) { Value = unitCost });
        Rcmd.Parameters.Add(new SqlParameter("@Line", SqlDbType.VarChar) { Value = line });

        Rcmd.ExecuteNonQuery();
    }
}

This is precisely the same as your code. Notice I have refactored the UpdateStock process and got rid of all of the try/catch blocks for simplicity.

I want to simplify this further: The updates to Table1 do not appear in your cursor. For the purposes of simplifying for the cursor, can I get rid of all of the DataTable updates?

Posted 10-Sep-15 23:33pm

Andy Lanng

Updated 14-Sep-15 2:04am

v3

Comments

TarunKumarSusarapu 11-Sep-15 5:37am

Thanks for your Help But i want logic to write cursor in sql for that you can follow below code in front end

Andy Lanng 11-Sep-15 5:53am

Ok - I may be able to help, but could you please provide a breakdown of the cursors so I know what is going on and why. It's pretty difficult to get my head around, especially without test data :S
Thanks ^_^&

TarunKumarSusarapu 11-Sep-15 6:30am

take table 1 qty_in column and table 2 qty_out column if Qty_in is greater than or equal to qty_out then find the stock followed by calculate fifo cost. I have clearly written that code

Andy Lanng 11-Sep-15 6:32am

Oh wait - I may have got it backwards: You have to code in C# - You want to create the cursor in SQL with the same logic? Have I understood correctly? Apologies for confusion

TarunKumarSusarapu 14-Sep-15 0:27am

Its OK bro what about the solution have u tried or not

Andy Lanng 14-Sep-15 4:08am

No - Time became a factor in my own project Friday. Is this an issue?

TarunKumarSusarapu 14-Sep-15 4:43am

Yeah I tried this from last two days would u pls spend a 10 min for this

Andy Lanng 14-Sep-15 4:51am

I should get some time this morning. I'll post again when I have something for you

TarunKumarSusarapu 14-Sep-15 5:04am

K Thank you

Andy Lanng 14-Sep-15 7:59am

Please look at the Update section of the solution

TarunKumarSusarapu 15-Sep-15 1:29am

are u free to do this have u found any solution for this?

Andy Lanng 15-Sep-15 4:50am

There is a question in the updated solution. Please read everything below "UPDATE" and respond

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TarunKumarSusarapu · Accepted Answer · 2015-09-15T00:06:00

Code to Find FIFO COST

SQL

<pre lang="SQL"><pre lang="SQL"><pre lang="SQL">

USE [RISE_DB]
GO
/****** Object: StoredProcedure [dbo].[SP_GETFIFOCOST] Script Date: 09/15/2015 15:34:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
Begin
Declare @qty decimal(18,2),@cost decimal(18,2),@line int
Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
Declare @fqty decimal(18,2)
set @fqty=0
Declare MyCur1 Cursor for select QTY_OUT,UNIT_COST,LINE from STOCKDETAILS where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') and PRODUCT_NO=@pid order by STOCK_DATE
Open MyCur1
Declare MyCur Cursor for select QTY_IN,UNIT_COST,LINE from STOCKDETAILS where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') and PRODUCT_NO=@pid order by STOCK_DATE
Open MyCur
if @fqty=0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
Fetch Next From MyCur into @qty,@cost,@line
End
While @@Fetch_Status=0
Begin
While @@Fetch_Status=0
Begin
if @qty>@qty1
begin
set @qty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
set @fqty=@fqty+@qty
if @fqty>0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
set @fqty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
end
end
if @qty<@qty1
begin
set @fqty=@qty-@qty1
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
if @fqty<0
begin
Fetch Next From MyCur into @qty,@cost,@line
Fetch Next From MyCur1 into @qty1,@cost1,@line1
set @qty1=@fqty-@qty1
set @fqty=@qty+@qty1
if @fqty>0
begin
set @qty=@qty+@qty1
end
update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
end
end
if @fqty=0
begin
Fetch Next From MyCur1 into @qty1,@cost1,@line1
Fetch Next From MyCur into @qty,@cost,@line
End
End
end
Close MyCur1
Close MyCur
Deallocate MyCur1
Deallocate MyCur
End