SQL VIEW Query

Question

Good morning.

I need help solving a query question in the view. I need help creating this view.

Follows the table:

    CREATE TABLE [dbo].[Controle](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DataHora] [datetime] NOT NULL,
    [Local] [varchar](30) NOT NULL,
    [PessoaID] [int] NOT NULL,
    [PessoaNome] [varchar](50) NOT NULL,
    [PessoaDocNumero] [varchar](15) NOT NULL,
    [PessoaGrupo] [varchar](30) NULL,
    [Outros1] [varchar](30) NULL,
    [Outros2] [varchar](30) NULL,
 CONSTRAINT [PK_Controle] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Follow the Insert:

INSERT INTO Controle (DataHora,Local,PessoaID,PessoaNome,PessoaDocNumero,PessoaGrupo,Outros1,Outros2)VALUES(GETDATE(),'TERREO',1,'MARIA','123456789','VISITANTE','XPTO1', 'XPTO2')
    INSERT INTO Controle (DataHora,Local,PessoaID,PessoaNome,PessoaDocNumero,PessoaGrupo,Outros1,Outros2)VALUES(GETDATE(),'TERREO',1,'MARIA','123456789','VISITANTE','XPTO1', 'XPTO2')
    INSERT INTO Controle (DataHora,Local,PessoaID,PessoaNome,PessoaDocNumero,PessoaGrupo,Outros1,Outros2)VALUES(GETDATE(),'TERREO',1,'MARIA','123456789','VISITANTE','XPTO1', 'XPTO2')
    INSERT INTO Controle (DataHora,Local,PessoaID,PessoaNome,PessoaDocNumero,PessoaGrupo,Outros1,Outros2)VALUES(GETDATE(),'TERREO',1,'JOAO','32333232','FUNCIONARIO','XPTO1', 'XPTO2')
    INSERT INTO Controle (DataHora,Local,PessoaID,PessoaNome,PessoaDocNumero,PessoaGrupo,Outros1,Outros2)VALUES(GETDATE(),'TERREO',1,'JOAO','32333232','FUNCIONARIO','XPTO1', 'XPTO2')
    INSERT INTO Controle (DataHora,Local,PessoaID,PessoaNome,PessoaDocNumero,PessoaGrupo,Outros1,Outros2)VALUES(GETDATE(),'TERREO',1,'JOAO','32333232','FUNCIONARIO','XPTO1', 'XPTO2')

Objective: I need to know the amount of records per Persongroup in the month and year to be defined and in the query below I have the expected result. So far so good.

SELECT PessoaGrupo, 'Dia ' + '' + Convert(VARCHAR,day(DataHora)) as Dia, COUNT(*) as Valor
    FROM Controle 
    WHERE ID > 0        
    and month(DataHora) = 05
    and year(DataHora) = 2021
    GROUP BY PessoaGrupo, day(DataHora)

return:

Campo       Dia     valor
FUNCIONARIO Dia 12  3
VISITANTE   Dia 12  3

But how I use VIEW is where I’m in trouble. How do I return only the 3 columns as per the query and be able to filter through any field of the Control table?

I need help creating this view so I can filter it as follows:

SELECT * FROM View_Controle
WHERE PessoaGrupo = 'VISITANTE' 
AND month(DataHora) = 05
AND year(DataHora) = 2021

retorno:

    Campo       Dia     valor
    FUNCIONARIO Dia 12  3
    VISITANTE   Dia 12  3

Can someone help me with creating this view?

Grateful

Teles

Answer 1

Purpose: I need to know the amount of records per person month and year to be defined and in the query below I have the result expected. So far all ok.

Since in its consultation, it has already achieved the expected result, for the creation of the view you need to add in select the fields that will be used in the clause where since the query referencing this view considers it as a table.
I added the fields month(DataHora) mes and year(DataHora) ano, placing these fields also in the group by. You could just put the field DataHora as a field and to do the operations with this field as a reference, but I advise to simplify the fields immediately in order to facilitate the future consultation, thus dispensing with operations of transformation of results on the view.

create view View_Controle as
SELECT month(DataHora) mes, year(DataHora) ano, PessoaGrupo, 'Dia ' + '' + Convert(VARCHAR,day(DataHora)) as Dia, COUNT(*) as Valor
    FROM Controle 
    WHERE ID > 0        
    GROUP BY PessoaGrupo,  day(DataHora), month(DataHora), year(DataHora)

When making the consultation at view would look like this:

Select PessoaGrupo, Dia, Valor
  from View_Controle
  where mes = 5
    and ano = 2021