Tengo esta tabla de base de datos de Sql Server llamada productPrices:
shopId int price decimal(18, 2) dateFound datetimeQuiero hacer una consulta sql formateada para usar en un gráfico de líneas chart.js. El gráfico de líneas toma los siguientes parámetros:
Una matriz de valores X, que en este contexto será la fecha/día (de menor a mayor) Cada ID de tienda contiene una matriz de precios.
Hice una versión codificada de cómo se deben mostrar los datos en el gráfico de líneas (usando solo los datos agregados manualmente)
https://codepen.io/nickbuus/pen/OJxEGqK
El problema es que si no se agregó precio para la tienda en un día en particular, entonces un valor de datos para la instancia 0 aún debe estar presente, ya que la longitud de las matrices de precios debe coincidir con la matriz de valores X.
¿Cómo podría hacer una consulta que llene 0 cuando el ID de tienda en particular no tiene un valor en esa fecha en particular?
¿Cuál es la mejor manera de formatear los datos devueltos de la consulta sql cuando se debe usar para la estructura que usa el gráfico de líneas chart.js?
Idealmente, lo que debería estar haciendo aquí es crear una tabla de calendario. No voy a cubrir cómo crear una tabla de calendario aquí, ya que una búsqueda en su motor de búsqueda favorito de algo como "Calendar Table SQL Server" le brindará una gran cantidad de recursos y brindará excelentes explicaciones y sus casos de uso.
Una vez que tenga una tabla de Calendar , debe CROSS JOIN a la tabla de Shops ; que supongo que también tienes. Luego puede simplemente LEFT JOIN a su tabla de Prices .
Entonces, una consulta parametrizada podría verse así:
SELECT S.ShopID, COUNT(P.Price) AS Prices, C.CalendarDate AS Datefound FROM dbo.Calendar C CROSS JOIN dbo.Shops S LEFT JOIN dbo.Prices P ON C.CalendarDate = P.DateFound --Though DateFound is a datetime, I assume it's time portion is 00:00:00.000 AND S.ShopId = P.ShopID WHERE C.CalendarDate >= @StartDate AND C.CalendarDate < DATEADD(DAY, 1, @EndDate) GROUP BY S.ShopID, C.CalendarDate; Si no puede, por alguna razón, crear una tabla de Calendario y no tiene (y no puede crear) una tabla de Tienda (sin embargo, le sugiero encarecidamente que cree una), entonces deberá usar un conteo en línea para crear su calendario y DISTINCT para obtener las ID de la tienda.
Una consulta parametrizada se vería así:
WITH N AS( SELECT N FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)), Tally AS( SELECT 0 AS I UNION ALL SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I FROM N N1, N N2, N N3), --1,000 rows. Add more cross joins for more rows Calendar AS( SELECT DATEADD(DAY, TI, @StartDate) AS CalendarDate FROM Tally T), Shops AS( SELECT DISTINCT P.ShopID FROM dbo.Prices P) SELECT S.ShopID, COUNT(P.Price) AS Prices, C.CalendarDate AS Datefound FROM dbo.Calendar C CROSS JOIN dbo.Shops S LEFT JOIN dbo.Prices P ON C.CalendarDate = P.DateFound --Though DateFound is a datetime, I assume it's time portion is 00:00:00.000 AND S.ShopId = P.ShopID GROUP BY S.ShopID, C.CalendarDate;