Search code examples
sqlsql-serverdateinventory

Get daily inventory using SQL Server even using while loop

I have a Stocktaking inventory table that has an entry for once a month date and for all other dates that the inventory balance has can't be. The problem that I have is that I need to generate a report that shows daily balances for all days.

StoreNo   Date     ProductBarCode  ProducQty
--------------------------------------------
    61  2015-11-03  2300007115072   7
    61  2015-12-15  2300012213046   5
    21  2016-01-03  2300012712075   8
    61  2016-03-04  2300019012093   7
    61  2016-04-29  2017961746012   100
    61  2016-06-29  0065103700004   33

I want show daily bases inventory!

I do have two problems

  1. how can I show every day's inventory with dates? If I add calendar table also, how can I get 2015-11-03 productBarcode and Qty until 2015-12-15 same productBarcode like this (61 2015-11-03 2300007115072 7)

  2. if I choose @date1 = 2015-11-05 and @date2= 2015-11-08 still I want to show result like this during chosen dates where no product shown.

Result like this

StoreNo   Date     ProductBarCode  ProducQty
--------------------------------------------
    61  2015-11-05  2300007115072   7
    61  2015-11-06  2300007115072   7
    61  2015-11-07  2300007115072   7
    61  2015-11-08  2300007115072   7

I was facing this problem past one week! I'm not able to find any solution ..

I'm beginner at SQL trying to learn if there are any possibilities to solve this. Thanks in advance for your help.

I tried a calendar table here 

Declare 
     @date1 date = '2015-12-01',
     @date2 date = '2015-12-05',
     @date_index date

create Table #calendar (Date date)

SET @date_index = @date1

WHILE @date_index <= @date2
BEGIN
    INSERT INTO #calendar
       SELECT @date_index

    SET @date_index = dateadd(day, 1, @date_index)

    IF @date_index > @date2
       BREAK
    ELSE
       CONTINUE
END
Solution

  • I'm using temp table for Inventory which you can replace it with your inventory table.

    create table #inventory (StoreNo int ,[Date] date, ProductBarCode varchar(14),ProductQty int )

insert into #inventory

SELECT 61,  '2015-11-03',  '2300007115072',   7   UNION ALL
SELECT 61,  '2015-12-15',  '2300012213046',   5   UNION ALL
SELECT 21,  '2016-01-03',  '2300012712075',   8   UNION ALL
SELECT 61,  '2016-03-04',  '2300019012093',   7   UNION ALL
SELECT 61,  '2016-04-29',  '2017961746012',   100 UNION ALL
SELECT 61,  '2016-06-29',  '0065103700004',   33  


DECLARE @dt1 Datetime='2015-11-05'
DECLARE @dt2 Datetime='2015-11-08'

;WITH ctedaterange 
     AS (SELECT [Dates]=@dt1 
         UNION ALL
         SELECT [dates] + 1 
         FROM   ctedaterange 
         WHERE  [dates] + 1<= @dt2) 
SELECT A.StoreNo,C.[dates],A.ProductBarCode,A.ProductQty
FROM   ctedaterange C
OUTER APPLY 
(
    SELECT TOP 1 * FROM #inventory I WHERE I.Date < C.DATES ORDER BY I.Date DESC
) A
OPTION (maxrecursion 0)

    It will work for other date ranges too. Just change the values of @dt1 and @dt2.