SyteLine Material Transaction History Query Generator (matltran)

DECLARE @Site SiteType     = N'MAIN';
DECLARE @From DATE         = '2026-01-01';
DECLARE @To   DATE         = '2026-09-30';
DECLARE @Item NVARCHAR(60) = N'PN-%';
DECLARE @Whse NVARCHAR(10) = N'MAIN';

SELECT m.trans_num, m.trans_date, m.trans_type, m.ref_type,
       m.ref_num, m.ref_line_suf, m.ref_release,
       m.item, m.whse, m.loc, m.lot, m.qty, m.cost
FROM matltran_mst AS m
WHERE m.site_ref = @Site
  AND m.trans_date >= @From
  AND m.trans_date <  DATEADD(day, 1, @To)
  AND m.item LIKE @Item
  AND (@Whse = N'' OR m.whse = @Whse)
  AND m.trans_type IN ('R', 'I')
ORDER BY m.trans_date, m.trans_num;

DECLARE @Site SiteType     = N'MAIN';
DECLARE @From DATE         = '2026-01-01';
DECLARE @To   DATE         = '2026-09-30';
DECLARE @Item NVARCHAR(60) = N'PN-%';
DECLARE @Whse NVARCHAR(10) = N'MAIN';

SELECT m.item,
       DATEFROMPARTS(YEAR(m.trans_date), MONTH(m.trans_date), 1) AS month_start,
       SUM(-1 * m.qty) AS qty_used,
       COUNT(*) AS txn_count
FROM matltran_mst AS m
WHERE m.site_ref = @Site
  AND m.trans_date >= @From
  AND m.trans_date <  DATEADD(day, 1, @To)
  AND m.item LIKE @Item
  AND (@Whse = N'' OR m.whse = @Whse)
  AND m.trans_type IN ('I')
GROUP BY m.item, DATEFROMPARTS(YEAR(m.trans_date), MONTH(m.trans_date), 1)
ORDER BY m.item, month_start;

-- Usage by item and code instead of month: group by m.item, m.trans_type.

DECLARE @Site SiteType = N'MAIN';

SELECT m.trans_type, m.ref_type,
       COUNT(*) AS row_count,
       SUM(CASE WHEN m.qty < 0 THEN 1 ELSE 0 END) AS negative_qty_rows,
       MIN(m.trans_date) AS first_seen,
       MAX(m.trans_date) AS last_seen
FROM matltran_mst AS m
WHERE m.site_ref = @Site
  AND m.trans_date >= DATEADD(month, -12, GETDATE())
GROUP BY m.trans_type, m.ref_type
ORDER BY row_count DESC;

-- Compare a few rows per code with the Material Transactions form to name each code
-- before you put it in the usage list.