currently i m working on reports of inventory application of a electronic shop, stock .sold and balance..
i want to make a daily report ,which will give me report of total stock,sold and balance
also i have maintain the date of stock entry of each product,sold product.
for e,g on 1st april, i have 200 piece of product A next day i sold 50 piece of product A, so balance would be 150. Same day ,i add 75 more inward stock to the stock,, then balance would be 225
You could use stored procedure to finish your target. I suppose your table is define as below, please check:
alter proc calculated
(
@datetime datetime,
@sold int,
@added int
)
as
begin
declare @stock int
declare @table1 table (
date datetime, stock int, sold int, added int)
declare @table2 table (
date datetime, stock int, sold int, balance int)
select @stock = case when (select stock from @table1) is null
then @added-@sold else @stock-@sold+@added
end
insert into @table1 select @datetime,@stock,@sold,@added
insert into @table2 select @datetime,@stock,@sold,@stock
select * from @table1
select * from @table2
end
--exec calculated '2012-05-04',0,200
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
but i m looking that when user selects fromdate and todate any between my start dates to any among till date,but it should calculate from day 1(31 march 12) in sql every time,but it will show the result on gridview depend only on the dates given by user.
prince2485
Member
289 Points
257 Posts
dates issues,sq; server?
Apr 30, 2012 11:06 AM|LINK
guys,
currently i m working on reports of inventory application of a electronic shop, stock .sold and balance..
i want to make a daily report ,which will give me report of total stock,sold and balance
also i have maintain the date of stock entry of each product,sold product.
for e,g on 1st april, i have 200 piece of product A next day i sold 50 piece of product A, so balance would be 150. Same day ,i add 75 more inward stock to the stock,, then balance would be 225
date stock sold balance
1 april 200 0 0
2nd april 200 50 150
3rd april 225 0 225
4rd april 250 0 250
Any idea, how to get such reports,
gimimex
Participant
1052 Points
157 Posts
Re: dates issues,sq; server?
Apr 30, 2012 01:05 PM|LINK
Hi,
I think you better post the structure of the tables involved, and a sample of data.
Chen Yu - MS...
All-Star
21581 Points
2493 Posts
Microsoft
Re: dates issues,sq; server?
May 04, 2012 10:40 AM|LINK
Hi,
You could use stored procedure to finish your target. I suppose your table is define as below, please check:
alter proc calculated ( @datetime datetime, @sold int, @added int ) as begin declare @stock int declare @table1 table ( date datetime, stock int, sold int, added int) declare @table2 table ( date datetime, stock int, sold int, balance int) select @stock = case when (select stock from @table1) is null then @added-@sold else @stock-@sold+@added end insert into @table1 select @datetime,@stock,@sold,@added insert into @table2 select @datetime,@stock,@sold,@stock select * from @table1 select * from @table2 end --exec calculated '2012-05-04',0,200Thanks.
Feedback to us
Develop and promote your apps in Windows Store
prince2485
Member
289 Points
257 Posts
Re: dates issues,sq; server?
May 05, 2012 05:04 AM|LINK
tx for ur gr8 code
but i m looking that when user selects fromdate and todate any between my start dates to any among till date,but it should calculate from day 1(31 march 12) in sql every time,but it will show the result on gridview depend only on the dates given by user.
actually , in my inventory report,
date stock sold balance
31 march 0 0 0
1 april 200 0 0
2nd april 200 50 150
3rd april 225 0 225
4rd april 250 0 250
so on..........................
Chen Yu - MS...
All-Star
21581 Points
2493 Posts
Microsoft
Re: dates issues,sq; server?
May 10, 2012 07:17 AM|LINK
Hi,
Do you means you store date, stock, sold, balance in your table, you need to calculate the balance again from user fromdate to todate? If so,
you could reference on below thread, use temp table in your query, insert new records into the temp table when the balance value changed.
http://forums.asp.net/p/1710294/4557474.aspx/1?Re+Get+The+records+from+this+year+and+past+year
Thanks.
Feedback to us
Develop and promote your apps in Windows Store