0

I'm new here, and i have a situation. I need to alter one report that i created a few time ago, that shows the planning of maintenance. But not all months/weeks have plan defined, and i want to show all months/weeks even in that situation. The table where i go get the data, have a column Month and onother named Week, and the matrix column group is on Month, and the second group is on Week.

I show you a image of what i have done, and the result.

enter image description here

And here is the query:

SELECT [dbo].[Equip_PIMP].[ID_Equip_PIMP]
          ,[dbo].[Contrato].[ID_Contrato] AS [Contrato_Nr]
          ,[dbo].[Cliente].[Nome] AS [NomeCliente]
          ,[dbo].[Instituicao].[Nome] AS [NomeInstituicao]
          ,[dbo].[Servico].[Nome] AS [NomeServico]
          ,[dbo].[Equip_Marca].[Nome] AS [NomeMarca]
          ,[dbo].[Modelos].[Nome] AS [NomeModelo]
          ,CASE WHEN @DIA_OU_X = 1 THEN CAST([dbo].[Equip_PIMP].[Dia] AS VARCHAR(2))
                WHEN @DIA_OU_X = 0 THEN 'X'
           END AS [VALOR_CEL]
          ,[dbo].[Equip_PIMP].[Data_Plano]
          ,[dbo].[Equip_PIMP].[Data_Execucao]
          ,[dbo].[Rotina].[Nome] AS [NomeRotina]
          ,[dbo].[Rotina].[Tipo] AS [TipoRotina]
          ,[dbo].[Equip_Categoria].[Nome] AS [NomeCategoria] 
          ,CASE [dbo].[Equip_PIMP].[Mes] 
                WHEN 1 THEN 'JANEIRO' 
                WHEN 2 THEN 'FEVEREIRO' 
                WHEN 3 THEN 'MARÇO' 
                WHEN 4 THEN 'ABRIL' 
                WHEN 5 THEN 'MAIO' 
                WHEN 6 THEN 'JUNHO' 
                WHEN 7 THEN 'JULHO' 
                WHEN 8 THEN 'AGOSTO' 
                WHEN 9 THEN 'SETEMBRO' 
                WHEN 10 THEN 'OUTUBRO' 
                WHEN 11 THEN 'NOVEMBRO' 
                WHEN 12 THEN 'DEZEMBRO' 
           END AS [Mes_Nome] -- This is the MONTHNAME
          ,[dbo].[Equipamento].[ID_Equipamento]
          ,[dbo].[Equipamento].[Nome]
          ,[dbo].[Equipamento].[Marca]
          ,[dbo].[Equipamento].[Modelo]
          ,[dbo].[Equipamento].[Categoria]
          ,[dbo].[Equipamento].[Num_Serie]
          ,[dbo].[Equipamento].[Num_Inventario]
          ,[dbo].[Equipamento].[Num_Equipamento]
          ,[dbo].[Equip_PIMP].[Dia]
          ,[dbo].[Equip_PIMP].[Semana] -- This is the WEEKNUMBER
          ,[dbo].[Equip_PIMP].[Mes] -- This is the MONTHNUMBER
          ,[dbo].[Equip_PIMP].[Trimestre]
          ,[dbo].[Equip_PIMP].[Semestre]
          ,[dbo].[Equip_PIMP].[Ano]
FROM ...
0

At a high level:

Create a temporary table (a subquery or CTE could be used as well, it's up to you) with the week numbers you want to show, and their corresponding month numbers.

Then modify your query as follows:

SELECT b.ID_Equip_PIMP]
      ,b.[Contrato_Nr]
      ,b.[NomeCliente]
      ,b.[NomeInstituicao]
      ,b.[NomeServico]
      ,b.[NomeMarca]
      ,b.[NomeModelo]
      ,b.[VALOR_CEL]
      ,b.[Data_Plano]
      ,b.[Data_Execucao]
      ,b.[NomeRotina]
      ,b.[TipoRotina]
      ,b.[NomeCategoria]
      ,CASE a.[Mes] 
            WHEN 1 THEN 'JANEIRO' 
            WHEN 2 THEN 'FEVEREIRO' 
            WHEN 3 THEN 'MARÇO' 
            WHEN 4 THEN 'ABRIL' 
            WHEN 5 THEN 'MAIO' 
            WHEN 6 THEN 'JUNHO' 
            WHEN 7 THEN 'JULHO' 
            WHEN 8 THEN 'AGOSTO' 
            WHEN 9 THEN 'SETEMBRO' 
            WHEN 10 THEN 'OUTUBRO' 
            WHEN 11 THEN 'NOVEMBRO' 
            WHEN 12 THEN 'DEZEMBRO' 
       END AS [Mes_Nome]
      ,b.[ID_Equipamento]
      ,b.[Nome]
      ,b.[Marca]
      ,b.[Modelo]
      ,b.[Categoria]
      ,b.[Num_Serie]
      ,b.[Num_Inventario]
      ,b.[Num_Equipamento]
      ,b.[Dia]
      ,a.[Semana]
      ,a.[Mes]
      ,b.[Trimestre]
      ,b.[Semestre]
      ,b.[Ano]
  FROM #temp_table a
         LEFT OUTER JOIN (
                          /*  Place entire current query here, except ORDER BY  */
                         ) b ON (a.Mes = b.Mes AND a.Semana = b.Semana)
 ORDER BY /* place current query's ORDER BY here */
;

The temp table will ensure that you have at least one row for every week; however, if there's no actual data for that week, all the related values except the week and month will be NULL.

You may have to make adjustments on the reporting side, depending on how that handles NULL values.

  • GREAT! THANK YOU! I created a real table with all the dates (Year, Month, Week) and used it, replacing the #temp. How do i mark this as THE ASWER? – Nuno Rato Jul 7 '17 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.