dates issues,sq; server?

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,

Re: dates issues,sq; server?

Hi,

I think you better post the structure of the tables involved, and a sample of data.

Re: dates issues,sq; server?

prince2485

date       stock sold   balance

1 april      200     0         0

2nd april 200    50       150
3rd april 225     0        225

4rd april 250     0         250

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,200

Thanks.

Re: dates issues,sq; server?

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..........................

Re: dates issues,sq; server?

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.