promedio - Grupo SQL por día, muestra pedidos para cada día

promedio en sql ejemplo

Question

Tengo una tabla SQL 2005, llamémosles Pedidos, en el formato:

OrderID, OrderDate,  OrderAmount
1,       25/11/2008, 10
2,       25/11/2008, 2
3,       30/1002008, 5

Luego, debo presentar una tabla de informes que muestre el monto ordenado cada día de los últimos 7 días:

Day,        OrderCount, OrderAmount
25/11/2008, 2,          12
26/11/2008, 0,          0 
27/11/2008, 0,          0 
28/11/2008, 0,          0
29/11/2008, 0,          0
30/11/2008, 1,          5

La consulta SQL que normalmente produciría esto:

select count(*), sum(OrderAmount)
    from Orders
    where OrderDate>getdate()-7
    group by datepart(day,OrderDate)

Tiene un problema porque saltará los días en que no hay pedidos:

Day,        OrderCount, OrderAmount
25/11/2008, 2,          12
30/11/2008, 1,          5

Normalmente arreglaría esto usando una tabla de conteo y una combinación externa contra filas, pero realmente estoy buscando una solución más simple o más eficiente para esto. Parece un requisito tan común para una consulta de informe que alguna solución elegante ya debería estar disponible para esto.

Entonces: 1. ¿Se puede obtener este resultado de una consulta simple sin usar tablas de conteo?

y 2. Si no, ¿podemos crear esta tabla de recuento (confiablemente) sobre la marcha (puedo crear una tabla de conteo usando CTE pero la pila de recursión me limita a 100 filas)?

Answer 1

Dependiendo de cómo SQL Server maneje las tablas temporales, puede organizar más o menos fácilmente la creación de una tabla temporal y llenarla con las 7 (¿o fueron las 8?) Fechas que le interesan. Puede usar eso como su tabla de recuento. No hay una manera más limpia que yo sepa; solo puede seleccionar los datos que existen en una tabla o que pueden derivarse de los datos que existen en una tabla o conjunto de tablas. Si hay fechas no representadas en la tabla Pedidos, no puede seleccionar esas fechas en la tabla Pedidos.

Answer 2

SQL no está "omitiendo" fechas ... porque las consultas se ejecutan contra datos que están realmente en la tabla. Entonces, si no tiene un DATE en la tabla para el 14 de enero, ¿por qué SQL le mostraría un resultado?

Lo que debes hacer es crear una tabla temporal y ÚNETE a ella.

CREATE TABLE #MyDates ( TargetDate DATETIME )
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 0, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 1, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 2, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 3, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 4, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 5, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 6, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 7, 101))

SELECT CONVERT(VARCHAR, TargetDate, 101) AS Date, COUNT(*) AS OrderCount
FROM dbo.Orders INNER JOIN #MyDates ON Orders.Date = #MyDates.TargetDate
GROUP BY blah blah blah (you know the rest)

¡Aquí tienes!

Answer 3

Como también querrá usar esta tabla de fechas con frecuencia en otras consultas, le sugiero que la convierta en una tabla permanente y cree un trabajo para agregar las fechas del año nuevo una vez al año.

Answer 4

Tuve el mismo problema y así es como lo resolví:

SELECT datename(DW,nDays) TimelineDays, 
    Convert(varchar(10), nDays, 101) TimelineDate,
    ISNULL(SUM(Counter),0) Totals 
FROM (Select GETDATE() AS nDays
    union Select GETDATE()-1
    union Select GETDATE()-2
    union Select GETDATE()-3
    union Select GETDATE()-4
    union Select GETDATE()-5
    union Select GETDATE()-6) AS tDays

Left Join (Select * From tHistory Where Account = 1000) AS History
            on (DATEPART(year,nDays) + DATEPART(MONTH,nDays) + DATEPART(day,nDays)) = 
            (DATEPART(year,RecordDate) + DATEPART(MONTH,RecordDate) + DATEPART(day,RecordDate)) 
GROUP BY nDays
ORDER BY nDays DESC

La salida es:

TimelineDays,   TimelineDate,     Totals

Tuesday         10/26/2010        0
Monday          10/25/2010        6
Sunday          10/24/2010        3
Saturday        10/23/2010        2
Friday          10/22/2010        0
Thursday        10/21/2010        0
Wednesday       10/20/2010        0

Answer 5

CREATE PROCEDURE [dbo].[sp_Myforeach_Date]
    -- Add the parameters for the stored procedure here
    @SatrtDate as DateTime,
    @EndDate as dateTime,
    @DatePart as varchar(2),
    @OutPutFormat as int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    Declare @DateList Table
    (Date varchar(50))

    WHILE @SatrtDate<= @EndDate
    BEGIN
    INSERT @DateList (Date) values(Convert(varchar,@SatrtDate,@OutPutFormat))
    IF Upper(@DatePart)=''DD''
    SET @SatrtDate= DateAdd(dd,1,@SatrtDate)
    IF Upper(@DatePart)=''MM''
    SET @SatrtDate= DateAdd(mm,1,@SatrtDate)
    IF Upper(@DatePart)=''YY''
    SET @SatrtDate= DateAdd(yy,1,@SatrtDate)
    END 
    SELECT * FROM @DateList
END

Simplemente ponga este Código y llame al SP de esta manera

exec sp_Myforeach_Date @SatrtDate=''03 Jan 2010'',@EndDate=''03 Mar 2010'',@DatePart=''dd'',@OutPutFormat=106

Gracias * Suvabrata Roy ICRA Online Ltd. Kolkata *

Answer 6

Si desea ver el valor cero, ingrese la siguiente consulta:

select count(*), sum(OrderAmount)
from Orders
where OrderDate>getdate()-7
  and sum(OrderAmount) > 0 or sum(OrderAmount) = 0
group by datepart(day,OrderDate)