Inventory Coverage Calculation

Transact-SQL
1

I am working on a Report and require some help with the query design. In the query, I have the Forecasted Sales and the Forecasted Inventory ( in monthly buckets). What I need to do is to calculate the inventory coverage (in number of months) in each month. The definition of inventory coverage would be as follows:

If the inventory I have at the end of the month is able to meet the demand ( forecasted sales) for the next X months, then the Inventory Coverage that month is X.

Suppose I have the following scenario:

           Month1 Month2  Month3  Month4   Month5
Net Sales	100	   300	   300 	   400      200

Inventory	400	   800	   500	   600	    300

Coverage	1.33   2.5	   1.5	    1	     -

Calculation: For month 1 : Inventory = 400.

Net sales for month 2 = 300

Hence Inventory that would be left = 400 - 300 = 100

Net Sales for month 3 = 300

The remaining inventory can meet 100/300 = 0.33 of this month.

Hence the Inventory Coverage in month 1 is 1.33

Similarly for month 2 and 3.

Month 4 can cover for the the whole of month 5, and since no further months are available, we would show the coverage as 1.

Can someone help me with this. If someone has done something similar to this, kindly respond.

My table like that:

Period	Inventory	NetSales

201701	  400	      100

201702	  800	      300

201703	  500	      300

201704	  600	      400

201705	  300	      200

Thanks

2

Please post create table or declare table variable with insert(s) of sample data and what the desired query output should be. Thanks.

3

Create and insert statement of table is in below side.

CREATE TABLE [dbo].[TEST_TABLE](
[PERIOD] nchar NULL,
[INVENTORY] [numeric](10, 0) NULL,
[NETSALES] [numeric](10, 0) NULL
) ON [PRIMARY]

INSERT [dbo].[TEST_TABLE] ([PERIOD], [INVENTORY], [NETSALES]) VALUES (201701,400,100)
INSERT [dbo].[TEST_TABLE] ([PERIOD], [INVENTORY], [NETSALES]) VALUES (201701,800,300)
INSERT [dbo].[TEST_TABLE] ([PERIOD], [INVENTORY], [NETSALES]) VALUES (201701,500,300)
INSERT [dbo].[TEST_TABLE] ([PERIOD], [INVENTORY], [NETSALES]) VALUES (201701,600,400)
INSERT [dbo].[TEST_TABLE] ([PERIOD], [INVENTORY], [NETSALES]) VALUES (201701,300,200)

4

This could get you started:

with cte1
  as (select a.[period]
            ,case
                when a.inventory
                    >sum(b.netsales) over(partition by a.[period]
                                          order by b.[period]
                                          rows unbounded preceding
                                         )
                then 1.
                else (a.inventory
                     -sum(b.netsales) over(partition by a.[period]
                                           order by b.[period]
                                           rows unbounded preceding
                                          )
                     +b.netsales
                     )
                     /b.netsales
             end as coverage
            ,row_number() over(partition by a.[period]
                               order by b.[period]
                              )
             as rn
         from dbo.test_table as a
              left outer join dbo.test_table as b
                           on b.[period]>a.[period]
     )
    ,cte2
  as (select a.[period]
            ,sum(b.coverage) as coverage
        from (select [period]
                    ,max(rn)+1-case when sum(coverage) is null then 1 else 0 end as rn
                from cte1
               where isnull(coverage,0)>=1
                  or rn=1
               group by [period]
             ) as a
             inner join cte1 as b
                     on b.[period]=a.[period]
                    and b.rn<=a.rn
       group by a.[period]
     )
select p.txt as [ ]
      ,p.[201701] as Month1
      ,p.[201702] as Month2
      ,p.[201703] as Month3
      ,p.[201704] as Month4
      ,p.[201705] as Month5
  from (select 'Net Sales' as txt
              ,[period]
              ,trim(str(netsales)) as thevalue
              ,1 as seq
          from dbo.test_table
        union all
        select 'Inventiry' as txt
              ,[period]
              ,trim(str(inventory)) as thevalue
              ,2 as seq
          from dbo.test_table
        union all
        select 'Coverage' as txt
              ,[period]
              ,case when coverage is null then '-' else trim(str(coverage,10,2)) end as thevalue
              ,3 as seq
          from cte2
       ) as a
       pivot (min(a.thevalue) for a.[period] in ([201701],[201702],[201703],[201704],[201705])) as p
 order by p.seq
;
1 Like
5

Thanks bitsmed for query. This is what I want actually.