MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab
AS
SELECT
'A1' AS Item,         'L1' AS Location,           0 AS Projected   ,  CAST('2018-08-20' AS DATE) AS Inventory_date    UNION ALL SELECT 
'A1',         'L1',           0    ,  '2018-08-27' UNION ALL SELECT 
'A1',         'L1',           54   ,   '2018-09-03'   UNION ALL SELECT 
'A1',         'L1',           49   ,   '2018-09-10'   UNION ALL SELECT 
'A1',         'L1',           44   ,   '2018-09-17'    UNION ALL SELECT 
'A1',         'L1',           39   ,    '2018-09-24'     UNION ALL SELECT 
'A1',         'L1',           32   ,     '2018-10-01'     UNION ALL SELECT 
'A1',         'L1',          25    ,     '2018-10-08'  UNION ALL SELECT 
'A1',         'L1',          18    ,     '2018-10-15' UNION ALL SELECT 
'A1',         'L1',          12    ,     '2018-10-22'    UNION ALL SELECT 
'A1',         'L1',          5     ,     '2018-10-29' UNION ALL SELECT 
'A1',         'L1',          55    ,    '2018-11-05' UNION ALL SELECT 
'A1',         'L1',          45    ,    '2018-11-12';

Item	Location	Projected	Inventory_date	subgrp	rn
A1	L1	0	2018-08-20	0	1
A1	L1	18	2018-10-15	6	2