Get the availability/Available Stock RSS

• • Reply
  

  sanjayverma_...

  Participant

  1428 Points

  334 Posts

  Get the availability/Available Stock

  Dec 31, 2012 10:46 AM|LINK

  He friends,

  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

  Be the Best.........
• • Reply
  

  usman400

  Contributor

  3513 Points

  721 Posts

  Re: Get the availability/Available Stock

  Dec 31, 2012 10:57 AM|LINK

  "Select * from Stock where ReorderQuantity>=" + Convert.ToInt32(txtQuantity.Text) + " and (DateOfBooking >'" + getDateValue + "'" +
  " and DateOfBooking<'" + getSecondDateValue + ")";

  Usman Waheed
  • Edited by usman400 on Dec 31, 2012 10:57 AM
• • Reply
  

  sanjayverma_...

  Participant

  1428 Points

  334 Posts

  Re: Get the availability/Available Stock

  Dec 31, 2012 11:25 AM|LINK

  Hi usman400,

  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

  Thanks

  Be the Best.........
  • Marked as answer by sanjayverma_mca on Jan 04, 2013 10:03 AM
• • Reply
  

  sanjayverma_...

  Participant

  1428 Points

  334 Posts

  Re: Get the availability/Available Stock

  Jan 02, 2013 10:20 AM|LINK

  Hi All,

  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
          union all
          select dateadd(d, 1, dt) from dates  where dt < @todate
  )
  
  select MIN([Available])
  from (
          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
  ) tab

  Provide by sandeepmittal11.

  You may also go with the linkand check the solution

  http://forums.asp.net/t/1871022.aspx/1?Get+available+Quantity+to+Issue

  Thank you

  Be the Best.........
  • Marked as answer by sanjayverma_mca on Jan 04, 2013 10:03 AM