SyteLine Inventory Aging Query Generator

DECLARE @Site SiteType     = N'MAIN';
DECLARE @Whse NVARCHAR(10) = N'MAIN';
DECLARE @Item NVARCHAR(60) = N'%';
DECLARE @B1 INT = 30, @B2 INT = 60, @B3 INT = 90, @B4 INT = 180, @B5 INT = 365;
DECLARE @IncludeZeroCost BIT = 1;

IF OBJECT_ID('tempdb..#aged') IS NOT NULL DROP TABLE #aged;

WITH last_rcpt AS (
  SELECT m.item, m.whse, m.loc, MAX(m.trans_date) AS last_rcpt_date
  FROM matltran_mst AS m
  WHERE m.site_ref = @Site
    AND m.trans_type IN ('R')
  GROUP BY m.item, m.whse, m.loc
)
SELECT il.item, il.whse, il.loc, il.qty_on_hand,
       lr.last_rcpt_date,
       DATEDIFF(day, lr.last_rcpt_date, GETDATE()) AS age_days,
       ISNULL(i.unit_cost, 0) AS unit_cost,
       il.qty_on_hand * ISNULL(i.unit_cost, 0) AS ext_value,
       CASE WHEN lr.last_rcpt_date IS NULL THEN 7
            WHEN DATEDIFF(day, lr.last_rcpt_date, GETDATE()) <= @B1 THEN 1
            WHEN DATEDIFF(day, lr.last_rcpt_date, GETDATE()) <= @B2 THEN 2
            WHEN DATEDIFF(day, lr.last_rcpt_date, GETDATE()) <= @B3 THEN 3
            WHEN DATEDIFF(day, lr.last_rcpt_date, GETDATE()) <= @B4 THEN 4
            WHEN DATEDIFF(day, lr.last_rcpt_date, GETDATE()) <= @B5 THEN 5
            ELSE 6 END AS bucket_no
INTO #aged
FROM itemloc_mst AS il
JOIN item_mst AS i
  ON i.site_ref = il.site_ref AND i.item = il.item
LEFT JOIN last_rcpt AS lr
  ON lr.item = il.item AND lr.whse = il.whse AND lr.loc = il.loc
WHERE il.site_ref = @Site
  AND (@Whse = N'' OR il.whse = @Whse)
  AND il.item LIKE @Item
  AND il.qty_on_hand <> 0
  AND (@IncludeZeroCost = 1 OR ISNULL(i.unit_cost, 0) <> 0);

SELECT a.*,
       CASE a.bucket_no
         WHEN 1 THEN CONCAT('0-', @B1)
         WHEN 2 THEN CONCAT(@B1 + 1, '-', @B2)
         WHEN 3 THEN CONCAT(@B2 + 1, '-', @B3)
         WHEN 4 THEN CONCAT(@B3 + 1, '-', @B4)
         WHEN 5 THEN CONCAT(@B4 + 1, '-', @B5)
         WHEN 6 THEN CONCAT('Over ', @B5)
         ELSE 'No receipt found' END AS age_bucket
FROM #aged AS a
ORDER BY a.bucket_no DESC, a.ext_value DESC;

SELECT a.bucket_no,
       COUNT(*)           AS location_rows,
       SUM(a.qty_on_hand) AS qty_on_hand,
       SUM(a.ext_value)   AS ext_value,
       CAST(100.0 * SUM(a.ext_value) / NULLIF((SELECT SUM(ext_value) FROM #aged), 0) AS DECIMAL(6,2)) AS pct_of_value
FROM #aged AS a
GROUP BY a.bucket_no
ORDER BY a.bucket_no;
-- bucket_no 7 = no receipt found in matltran for that item, warehouse and location.

DECLARE @Site SiteType     = N'MAIN';
DECLARE @Whse NVARCHAR(10) = N'MAIN';
DECLARE @Item NVARCHAR(60) = N'%';

-- CreateDate is the record audit column, used here as a proxy for lot age.
-- If your lot_mst holds a receipt or manufacture date you trust, age by that column instead.
SELECT ll.item, ll.whse, ll.loc, ll.lot, ll.qty_on_hand,
       l.CreateDate AS lot_created,
       DATEDIFF(day, l.CreateDate, GETDATE()) AS age_days
FROM lot_loc_mst AS ll
JOIN lot_mst AS l
  ON l.site_ref = ll.site_ref AND l.item = ll.item AND l.lot = ll.lot
WHERE ll.site_ref = @Site
  AND (@Whse = N'' OR ll.whse = @Whse)
  AND ll.item LIKE @Item
  AND ll.qty_on_hand <> 0
ORDER BY age_days DESC;