Click here to Skip to main content
15,908,455 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have table called SpDet having pcode(product code),qty(quantity),vtype(voucher type sale/purchase) ,spdt(transaction date)rate ,amt etc.Now the problem is i have find the closing stock of all products between a specific date.Though i have done it for single item but i can not do it for all products can you all help me !!!!

Sql Stored Procedure
SQL
create procedure [dbo].[stocktrial3] @dtfrom date,@dtto date

as

begin

SET NOCOUNT ON ;

declare @inqty int,@outqty int,@opinqty int,@opoutqty int,@opqty int,@opclqty int,@clqty int

select @opqty=opqty from Item order by PCode

select @opinqty = SUM(qty) from SPDet where Vtype='PU' and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @opoutqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @inqty =SUM(qty) from SPDet where Vtype='PU' and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

select @outqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

set @opclqty=@opqty+ISNULL(@opinqty,0)-ISNULL(@opoutqty,0)

set @clqty=ISNULL(@opclqty,0)+ISNULL(@inqty,0)-ISNULL(@outqty,0)

select Item.Pname,@opclqty as PopQty,ISNULL(@inqty,0) as PinQty,ISNULL(@outqty,0) as POutQty,@clqty as PClqty

 from Item order by PCode

end


Result
PName   Op.Qty  InQty  OutQty   Clqty
VAIO    20  0   5   15
Lenevo Laptop   20  0   5   15
LCD 32" 20  0   5   15
LED 32" 20  0   5   15
SS  20  0   5   15
DD  20  0   5   15
Pendrive    20  0   5   15
Raid & Tailor   20  0   5   15
Laptop  20  0   5   15
Tab 20  0   5   15
Lenevo Desktop  20  0   5   15


As u can see that result is all product is same.
Posted
Updated 4-Mar-14 9:07am
v2

The problem is not clearly explained. For "looping" in SQL stored procedures, please see, for example:
using for loop in stored procedure[^],
http://stackoverflow.com/questions/16115043/sql-stored-procedure-while-loop[^].

—SA
 
Share this answer
 
Comments
Maciej Los 4-Mar-14 15:16pm    
Useful links, but i think OP does not need any loop. Have a look at my answer.
BTW: +5!
Sergey Alexandrovich Kryukov 4-Mar-14 15:30pm    
Thank you, Maciej.
I responded exclusively to the title of the question, though the linked threads provide interesting ideas on "looping".
However, I kinda failed to understand that "terrible solution" by OP. So, your point is much better.
—SA
Maciej Los 4-Mar-14 16:33pm    
Sergey, Paul (OriginalGriff) point me out that improving question's subject like that: [Not a question] is undesirable action. CP team recommend to properly report it, instead make correction to the question. For example: http://www.codeproject.com/Questions/738746/Event-handeling-program-in-csharp

Sorry for off-topic comment, but i didn't knew how to inform you.
Sergey Alexandrovich Kryukov 4-Mar-14 16:35pm    
Thank you for the note...
—SA
Wow! What a terrible solution! Sorry.

To achieve that what you need, use aggregate functions[^], eg.:
SQL
SELECT Product, SUM(Quality) AS SumOfQty
FROM TableName
GROUP BY Product
WHERE DateField BETWEEN @start AND @end


In some cases, pivot table[^] might be helpful.
You are able to achieve that using Common Table Expressions[^] too.
 
Share this answer
 
Comments
Sergey Alexandrovich Kryukov 4-Mar-14 15:27pm    
Good point, a 5.
—SA
Maciej Los 4-Mar-14 15:31pm    
Thank you, Sergey ;)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900