Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jan 02, 2013 10:20 AM by sanjayverma_mca
Dec 31, 2012 10:46 AM|LINK
I need your hel to find the availably quantity in between date selection .
Let suppose I ahve 10 product A In stock .
My database structure will be like this .
StockID ProductID OrderID ReorderQuantity DateOfBooking DateOfReturning CreatedDate ModifiedDate IsActive
1 1 1 5 2013-01-01 02:21:16.910 2013-01-04 02:21:16.910 2012-12-31 02:21:16.910 2012-12-31 02:21:16.910 0
2 1 2 5 2013-01-03 02:21:54.857 2013-01-07 02:21:54.857 2012-12-31 02:21:54.857 2012-12-31 02:21:54.857 0
Now What I want is if customer want the product A on Rent for between tow selected date with quantity I want to find out Available quantity between the selected date and quantity.
So please help me to find out the solution.
It is just like to find the seat Availablity in train as we provide product on rent.
Thank in Advance
Dec 31, 2012 10:57 AM|LINK
"Select * from Stock where ReorderQuantity>=" + Convert.ToInt32(txtQuantity.Text) + " and (DateOfBooking >'" + getDateValue + "'" +
" and DateOfBooking<'" + getSecondDateValue + ")";
Dec 31, 2012 11:25 AM|LINK
I alrady try this logic in sql server.
I have total 10 Product A .My problem is if 5 Product A is book between 31st Dec to 2nd jan and 5 product A is book between 4 jan to 6 jan
then the customer want to book product A between 1 jan to 4 jan then how do i come to know How much quantity is Available so that I can issue product A to him between the date 1 to 4 ???
I hope you understand my problem
Jan 02, 2013 10:20 AM|LINK
This is the solution
declare @StockManagement table(
PId int, Qty int, DateofBooking date, DateOfReturning date
insert into @StockManagement
select 1, 5, '1 Jan 2013', '3 Jan 2013' union all
select 1, 5, '1 Jan 2013', '4 Jan 2013'
declare @fromdate date, @todate date, @PId int, @stockQty int
set @fromdate = '1/4/2013'
set @todate = '1/7/2013'
set @PId = 1
set @stockQty = 10 --set stock qty here
;with dates as(
select @fromdate as dt
select dateadd(d, 1, dt) from dates where dt < @todate
select d.dt, @stockQty - SUM(isnull(Qty,0)) as [Available]
from dates d
left join @StockManagement s on d.dt between s.DateofBooking and s.DateOfReturning and PId = @PId
group by d.dt
You may also go with the linkand check the solution