SQL Grouping by name, date and quantity by month

Asked

Viewed 12,716 times

4

I am not able to group this result per month ("PP.PAPELETA_DATA" field). Type the quantity (Count(PD.Personal) as Total) separated by month! Could someone give me a hand? Usage SQL SERVER 2012.

SELECT 
PE2.PessoaNome,
count(PD.PessoaId) as Total
FROM PAPELETA PP
INNER JOIN PESSOASDETALHE PD ON PP.PAPELETA_PESS = PD.PessoaId
INNER JOIN PESSOAS PE ON PD.AnalistaId = PE.PessoaId
INNER JOIN PESSOAS PE2 ON PP.PAPELETA_PESS = PE2.PessoaId
GROUP BY PE2.PessoaNome

inserir a descrição da imagem aqui

sql sql-server sql-server-2012

  • Will this report always show a period of 3 months? Or will the interval be informed by the Customer, thus generating N columns? If you need New columns, you will need to beat the date before grouping, and then use a cursor to mount a query to be executed by sp_executesql.

  • How is the structure of your table?

3 answers

2


You can use the internal function MONTH, which returns which month of the given parameter, which must be a team, date, smalldatetime, datetime, datetime2 or datetimeoffset, as stated in the function documentation. Together with the MONTH, you can also use the CASE and SUM to check if the date is a specific month, if it is you sum one more, otherwise disregard, something like this:

SELECT 
    PE2.PessoaNome,
    SUM(CASE WHEN (MONTH(PP.PAPELETA_DATA) = 1) THEN 1 ELSE 0 END) Qtd_Jan,
    SUM(CASE WHEN (MONTH(PP.PAPELETA_DATA) = 2) THEN 1 ELSE 0 END) Qtd_Fev,
    SUM(CASE WHEN (MONTH(PP.PAPELETA_DATA) = 3) THEN 1 ELSE 0 END) Qtd_Mar,
FROM 
    PAPELETA PP
    INNER JOIN PESSOASDETALHE PD ON PP.PAPELETA_PESS = PD.PessoaId
    INNER JOIN PESSOAS PE ON PD.AnalistaId = PE.PessoaId
    INNER JOIN PESSOAS PE2 ON PP.PAPELETA_PESS = PE2.PessoaId
GROUP BY 
    PE2.PessoaNome

Note: I don’t have SQL SERVER to test at the moment, but the logic is this, if you have any problems, leave a comment on the answer I fix as soon as possible.

  • Your consultation may add months of different years, which may be unwanted.

  • 1

    Why downvote this answer? It presents an elegant solution to the question. To solve the problem of the year, simply include it as one more column and group it or restrict it in the Where clause. There is only one syntax problem, since you should close CASE with END.

  • Really @gmsantos, but my answer was just a starting point, and in the question it is not clear that it is necessary to consider the year, so "may be unwanted" is something relative.

1

Hello, Rafael,

You need to group by date as well.

Remember to delete PAPELETA_DATA date days, otherwise you will have data grouped by days. This is why you use functions MONTH and YEAR. This is more or less how your query:

SELECT PE2.PessoaNome       
     , 'Qtd-' + CONVERT(CHAR(2), MONTH(PP.PAPELETA_DATA)) + '-' + CONVERT(CHAR(4),     YEAR(PP.PAPELETA_DATA)) AS Periodo
     , COUNT(PD.PessoaId) AS Total
  FROM PAPELETA            PP
 INNER JOIN PESSOASDETALHE PD ON PP.PAPELETA_PESS   = PD.PessoaId
 INNER JOIN PESSOAS        PE ON PD.AnalistaId      = PE.PessoaId
 INNER JOIN PESSOAS       PE2 ON PP.PAPELETA_PESS   = PE2.PessoaId
 GROUP BY PE2.PessoaNome
        , MONTH(PP.PAPELETA_DATA)
        , YEAR(PP.PAPELETA_DATA)

-1

SELECT MONTH(PP.PAPELETA_DATA) as mes, count(*) as Qtd FROM PAPELETA PP GROUP BY (MONTH(PP.PAPELETA_DATA))

Browser other questions tagged sql sql-server sql-server-2012

You are not signed in. Login or sign up in order to post.