español - sql server documentation

Grupo de SQL Server por mes

Question

Tengo una tabla que tiene este esquema

ItemID    UserID    Year    IsPaid    PaymentDate  Amount
1         1         2009    0         2009-11-01  300
2         1         2009    0         2009-12-01  342
3         1         2010    0         2010-01-01  243
4         1         2010    0         2010-02-01  2543
5         1         2010    0         2010-03-01  475

Intento que funcione una consulta que muestra los totales de cada mes. Hasta ahora he probado DateDiff y selecciones anidadas, pero ninguno me da lo que quiero. Esto es lo más cercano que tengo, pienso:

DECLARE @start [datetime] = 2010/4/1;
SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4  AND UserID = 100) AS "Aug", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5  AND UserID = 100) AS "Sep", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6  AND UserID = 100) AS "Oct", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb", 
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar" 
FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY 
WHERE UserID = 16178 

Pero obtengo nulos cuando debería obtener valores. ¿Me estoy perdiendo de algo?

Answer 1

Ahora su consulta está analizando explícitamente solo los pagos para el año = 2010, sin embargo, creo que quería que su Jan / Feb / Mar represente realmente el 2009. De ser así, tendrá que ajustar esto un poco para ese caso. No siga consultando los valores de suma para cada columna, solo la condición de la diferencia de fecha en meses. Pon el resto en la cláusula WHERE.

SELECT 
      SUM( case when DateDiff(m, PaymentDate, @start) = 0 
           then Amount else 0 end ) AS "Apr",
      SUM( case when DateDiff(m, PaymentDate, @start) = 1 
           then Amount else 0 end ) AS "May",
      SUM( case when DateDiff(m, PaymentDate, @start) = 2 
           then Amount else 0 end ) AS "June",
      SUM( case when DateDiff(m, PaymentDate, @start) = 3 
           then Amount else 0 end ) AS "July",
      SUM( case when DateDiff(m, PaymentDate, @start) = 4 
           then Amount else 0 end ) AS "Aug",
      SUM( case when DateDiff(m, PaymentDate, @start) = 5 
           then Amount else 0 end ) AS "Sep",
      SUM( case when DateDiff(m, PaymentDate, @start) = 6 
           then Amount else 0 end ) AS "Oct",
      SUM( case when DateDiff(m, PaymentDate, @start) = 7 
           then Amount else 0 end ) AS "Nov",
      SUM( case when DateDiff(m, PaymentDate, @start) = 8 
           then Amount else 0 end ) AS "Dec",
      SUM( case when DateDiff(m, PaymentDate, @start) = 9 
           then Amount else 0 end ) AS "Jan",
      SUM( case when DateDiff(m, PaymentDate, @start) = 10 
           then Amount else 0 end ) AS "Feb",
      SUM( case when DateDiff(m, PaymentDate, @start) = 11 
           then Amount else 0 end ) AS "Mar"
   FROM 
      Payments I
         JOIN Live L
            on I.LiveID = L.Record_Key
   WHERE 
          Year = 2010 
      AND UserID = 100

Answer 2

Otro enfoque, que no implica agregar columnas al resultado, es simplemente poner a cero el componente del day de la fecha, por lo que 2016-07-13 y 2016-07-16 serían ambos 2016-07-01 , lo que haría ellos iguales por mes.

Si tiene un valor de date (no de datetime y datetime ), puede ponerlo en cero directamente:

SELECT
    DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] ),
    COUNT(*)
FROM
    [Table]
GROUP BY
    DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] )

Si tiene valores de datetime , deberá usar CONVERT para eliminar la porción de hora del día:

SELECT
    DATEADD( day, 1 - DATEPART( day, [Date] ),  CONVERT( date, [Date] ) ),
    COUNT(*)
FROM
    [Table]
GROUP BY
    DATEADD( day, 1 - DATEPART( day, [Date] ),  CONVERT( date, [Date] ) )

Answer 3

Prefiero combinar funciones DATEADD y DATEDIFF como esta:

GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0)

Juntas, estas dos funciones ponen a cero el componente de fecha más pequeño que la parte de fecha especificada (es decir, MONTH en este ejemplo).

Puede cambiar el bit de fecha y datepart a YEAR , WEEK , DAY , etc., que es muy útil.

Su consulta SQL original se vería así (no puedo probarlo ya que no tengo su conjunto de datos, pero debería ponerlo en el camino correcto).

DECLARE @start [datetime] = ''2010-04-01'';

SELECT
    ItemID,
    UserID,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],
    IsPaid,
    SUM(Amount)
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
AND PaymentDate > @start

Una cosa más: la columna Month se escribe como DateTime que también es una buena ventaja si necesita procesar esos datos o asignarle un objeto .NET, por ejemplo.

Answer 4

Restrinja la dimensión de NVARCHAR a 7, suministrada a CONVERT para mostrar solo "AAAA-MM"

SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)
ORDER BY [Month]

Answer 5

Si necesita hacer esto con frecuencia, probablemente agregue una columna calculada PaymentMonth a la tabla:

ALTER TABLE dbo.Payments ADD PaymentMonth AS MONTH(PaymentDate) PERSISTED

Se conserva y se almacena en la tabla, por lo que no hay realmente ningún rendimiento que lo solicite. Es un valor INT de 4 bytes, por lo que la sobrecarga de espacio también es mínima.

Una vez que tenga eso, podría simplificar su consulta para que sea algo así como:

SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 1 AND UserID = 100) AS ''Jan'',
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 2 AND UserID = 100) AS ''Feb'',
.... and so on .....
FROM LIVE L 
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY 
WHERE UserID = 16178 

Answer 6

DECLARE @start [datetime] = 2010/4/1;

Debiera ser...

DECLARE @start [datetime] = ''2010-04-01'';

El que tienes está dividiendo 2010 entre 4, luego con 1, luego convirtiendo a una fecha. Que es el día 57.5 desde 1900-01-01.

Pruebe SELECT @start después de su inicialización para verificar si esto es correcto.

Answer 7

SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]

También puedes probar:

SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month