How to list items per month in SQL Server

Question

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.

Answer 1

Try grouping the data from your select:

    SELECT  YEAR(DataReferencia)
           ,MONTH(DataReferencia)
           ,count(*) from Tabela
  GROUP BY  MONTH(DataReferencia)
           ,year(DataReferencia)