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 | | }}} |