1
I need to be listed assets that have something scheduled for a period, in case they need to bring me by month, I’ll leave an example below:
I have here a report where I bring an asset that returns to me everything, but I need it to return only what is expected per month, below the query.
SELECT DISTINCT V_AssetParentAssetCL.asset, AssetWorkProcedure.equipment, Asset.equipmentType, AssetWorkProcedure.workProcedure, AssetWorkProcedure.intervalType,AssetWorkProcedure.dateInterval, AssetWorkProcedure.nextWODate, CONVERT(VARCHAR,AssetWorkProcedure.dateInterval) + CONVERT(VARCHAR(1),(CASE WHEN AssetWorkProcedure.intervalType = '2' THEN 'M' ELSE 'D' END)) as period,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-01-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-01' OR initDate < '" + year +"-01-01') THEN 'X' ELSE NULL END as JAN,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-02-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-02' OR initDate < '" + year +"-02-01') THEN 'X' ELSE NULL END as FEV,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-03-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-03' OR initDate < '" + year +"-03-01') THEN 'X' ELSE NULL END as MAR,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-04-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-04' OR initDate < '" + year +"-04-01') THEN 'X' ELSE NULL END as ABR,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-05-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-05' OR initDate < '" + year +"-05-01') THEN 'X' ELSE NULL END as MAI,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-06-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-06' OR initDate < '" + year +"-06-01') THEN 'X' ELSE NULL END as JUN,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-07-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-07' OR initDate < '" + year +"-07-01') THEN 'X' ELSE NULL END as JUL,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-08-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-08' OR initDate < '" + year +"-08-01') THEN 'X' ELSE NULL END as AGO,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-09-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-09' OR initDate < '" + year +"-09-01') THEN 'X' ELSE NULL END as [SET],
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-10-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-10' OR initDate < '" + year +"-10-01') THEN 'X' ELSE NULL END as [OUT],
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-11-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-11' OR initDate < '" + year +"-11-01') THEN 'X' ELSE NULL END as NOV,
CASE WHEN (intervalType = '1' OR DATEDIFF(MONTH,initDate,'" + month +"-12-01')%dateInterval = 0) AND (CONVERT(VARCHAR(7),initDate,121) = '" + year +"-12' OR initDate < '" + year +"-12-01') THEN 'X' ELSE NULL END as DEZ
from AssetWorkProcedure
LEFT JOIN V_AssetParentAssetCL ON AssetWorkProcedure.asset = V_AssetParentAssetCL.asset
LEFT JOIN Asset ON AssetWorkProcedure.asset = Asset.asset
LEFT JOIN WorkOrder ON WorkOrder.asset = Asset.asset
How do I return this asset only what is planned per month? For example, in the month of December 2019 the exchange of an equipment is scheduled, or the month of May is expected to change activity.
Add in the text of your problem the table description (columns etc). For example, explain about intervalType, Month, initDate, dateInterval columns.
– José Diz