Changes between Version 1 and Version 2 of НапредниИзвештаи


Ignore:
Timestamp:
01/30/25 14:30:52 (3 weeks ago)
Author:
221550
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • НапредниИзвештаи

    v1 v2  
    8686ORDER BY Month DESC;
    8787}}}
    88 
    89 === Извештај за резервни делови со ниско залишно ниво ===
    90 ===== Делови со помалку од 10 единици во магацин и просечна месечна потрошувачка =====
    91 
    92 {{{#!sql
    93 WITH MonthlyUsage AS (
    94     SELECT
    95         si.Id AS PartId,
    96         AVG(sa.UnitsUsed) AS AvgMonthlyUsage
    97     FROM ServiceItems si
    98     JOIN ServiceAssignments sa ON si.Id = sa.ServiceItemId
    99     JOIN Services s ON sa.ServiceId = s.Id
    100     WHERE s.DateOfService >= CURRENT_DATE - INTERVAL '6 months'
    101     GROUP BY si.Id
    102 )
    103 SELECT
    104     si.Id,
    105     si.Description,
    106     COALESCE(mu.AvgMonthlyUsage, 0) AS AvgMonthlyUsage,
    107     CASE
    108         WHEN COALESCE(mu.AvgMonthlyUsage, 0) > 0 THEN 'Order ' || CEIL(mu.AvgMonthlyUsage * 2) || ' units'
    109         ELSE 'No usage data'
    110     END AS Recommendation
    111 FROM ServiceItems si
    112 LEFT JOIN MonthlyUsage mu ON si.Id = mu.PartId
    113 WHERE si.Id NOT IN (
    114     SELECT DISTINCT ServiceItemId
    115     FROM ServiceAssignments
    116     JOIN Services ON ServiceAssignments.ServiceId = Services.Id
    117     WHERE Services.DateOfService >= CURRENT_DATE - INTERVAL '30 days'
    118 )
    119 ORDER BY AvgMonthlyUsage DESC;
    120 }}}