How to generate row between dates range in sql if for date don't have a record?

Question

I'm not very familiar with SQL Server.

I have 3 tables, which relate to each other as one to many. So for one History, there are many Applications, and one Application can have many ApplicationAttributes.

I need to generate additional rows between dates range if for dates isn't exist any rows in database. If for given Imei already exist record, then need to take enrollTotalToday from previous row. For Id need to take last Id and just increment it. If for given dates ranges didn't exist previous rows, then just need to paste 0.

Please can anybody help me?

Histories table:

Id  Imei                  CreationDate   DeviceId
--------------------------------------------------
1   ProductionDevice299   2018-11-04     1
7   ProductionDevice299   2018-11-07     1

Applications table:

Id  Name              DeviceHistoryId
--------------------------------------
1   Enrollment.cone   1
2   DPC_OWNERS        1
3   OTHER_APPS        1
6   Enrollment.emp    7
7   DPC_OWNERS        7

ApplicationAttributes table:

Id  Key             Value   DeviceApplicationId
------------------------------------------------
1   EnrolledTotal   2       1
2   LoginsTotal     5       2
3   OtherAttribt1   8       3
4   OtherAttribt2   12      3
5   OtherAttribt3   17      3
6   EnrolledTotal   21      6
7   LoginsTotal     25      7

Expected result for range(2018-11-02, 2018-11-09):

Id  Imei                CreationDate DeviceId EnrollTotalToday EnrollTotalYesterday
8   ProductionDevice299 2018-11-02   1        0                0
9   ProductionDevice299 2018-11-03   1        0                0
1   ProductionDevice299 2018-11-04   1        2                0
10  ProductionDevice299 2018-11-05   1        2                0
11  ProductionDevice299 2018-11-06   1        2                0
7   ProductionDevice299 2018-11-07   1        21               2
12  ProductionDevice299 2018-11-08   1        21               0
13  ProductionDevice299 2018-11-09   1        21               0

My current SQL query:

WITH [CTE] AS 
(
    SELECT 
        [Extent1].[Id] AS [id], 
        [Extent1].[Imei] AS [imei], 
        CAST([Extent1].[CreationDate] AS DATE) AS [lastSeenOnline],  
        [Extent1].[DeviceId] AS [deviceId],
        SUM(CAST([Extent4].[Value] AS BIGINT)) as [enrollTotalToday],
        LAG(SUM(CAST([Extent4].[Value] AS BIGINT)), 1, 0) OVER (PARTITION BY [Extent1].[Imei] ORDER BY [Extent1].[CreationDate]) AS [enrollTotalYesterday]
    FROM 
        [DeviceManagement].[dbo].[DeviceHistory] AS [Extent1]
    INNER JOIN 
        (SELECT 
             [Imei], MAX([CreationDate]) AS MaxDate
         FROM 
             [dbo].[DeviceHistory]
         GROUP BY 
             [Imei], CAST([CreationDate] AS DATE)) [Extent2] ON [Extent1].[Imei] = [Extent2].[Imei] AND [Extent1].[CreationDate] = [Extent2].MaxDate
    INNER JOIN
        [DeviceManagement].[dbo].[DeviceApplication] AS [Extent3] ON [Extent3].DeviceHistoryId = [Extent1].id 
    INNER JOIN
        [DeviceManagement].[dbo].[DeviceApplicationAttribute] [Extent4] ON [Extent4].DeviceApplicationId = [Extent3].id AND [Extent4].[Key] = 'EnrolledTotal'
    GROUP BY 
        [Extent1].Id, [Extent1].Imei, [Extent1].CreationDate, [Extent1].DeviceId
) 
SELECT * 
FROM [CTE]
WHERE ([CTE].[lastSeenOnline] >= Convert(datetime, '2018-11-02' )) 
  AND ([CTE].[lastSeenOnline] <= Convert(datetime, '2018-11-09' ))

Take a look at this similar question for how to generate a series of dates between 2 given dates: https://stackoverflow.com/q/28400012/361842 — JohnLBevan, Nov 10 '18 at 17:04
@JohnLBevan in this example he want to insert into database some values. In my case I need to generate values on fly without inserting in database. — BorHunter, Nov 10 '18 at 17:10
@BorHunter well, really, the difference would be to comment out the `insert into` in between the CTE and the `select`. — Aaron Bertrand, Nov 11 '18 at 13:26

score 0 · Answer 1 · answered Nov 10 '18 at 17:39

The best solution in this case is to use calendar table with all dates that you need. Then to select from this table and join the other tables to get the data for the corresponding date.

If you can create this table, you can generate one on the fly. Just select from a table with enough rows to cover the number of days in the desired range (you can combine multiple tables to increase the number of rows if necessary), calculate ROW_NUMBER() and add as many days to your start date (or to the day before to be precise). You need to user TOP clause to select as many rows as days you have in your range. Something like this:

declare @FromDate date = '20180101', @ToDate DATE = '20180228', @Duration int 

set @Duration = DATEDIFF(day, @FromDate, @ToDate)

select top (DATEDIFF(day, @FromDate, @ToDate) + 1) DATEADD(DAY, ROW_NUMBER() over(order by message_id) - 1, @FromDate) as DateFromRange
from sys.messages

To use this technique in your query, you can construct derived table and select from it in your query.

select 
    DatesRange.DateFromRange
    , SalesPerDate.TotalSales
from (select top (DATEDIFF(day, '20180101', '20180228') + 1) DATEADD(DAY, ROW_NUMBER() over(order by message_id) - 1, '20180101') as DateFromRange from sys.messages) DatesRange
outer apply (select sum(SalesAmount) as TotalSales from Sales where SalesDate = DatesRange.DateFromRange) SalesPerDate

From your query it looks like you are using Entity Framework. Eventually you can generate this range in your app code too, and then join the query result to it. For generating a range you can use Enumerable.Range method, or to do something like that:

Enumerable.Range(0, 1 + end.Subtract(start).Days)
      .Select(offset => start.AddDays(offset))
      .ToArray();

as described in this question.

thank you for answer, but this is not exactly what I need. BTW: it's not an option to generate this rows using C# code, because of I will need to convert IQueryable type to IEnumerable, which will load all data and occur performance issues. — BorHunter, Nov 11 '18 at 08:23

Aaron Bertrand · Answer 2 · 2018-11-10T21:10:19.463

First, become comfortable with a generic way to generate the range of days:

DECLARE @start date = '20181102', @end date = '20181109';

;WITH n(n) AS 
(
  SELECT 0 
  UNION ALL SELECT n+1 
  FROM n 
  WHERE n < DATEDIFF(DAY, @start, @end)
)
SELECT d = DATEADD(DAY, n, @start) FROM n;

Results:

d
==========
2018-11-02
2018-11-03
2018-11-04
2018-11-05
2018-11-06
2018-11-07
2018-11-08
2018-11-09

Currently your query just produces the two rows with dates that exist, but you need to manufacture rows for the 6 other rows that don't, and you do that by using the above as the base and then left joining against it. Given these tables and data (thank you for sample data, but this is a much better form for showing us):

CREATE TABLE #DeviceHistory
  (Id int, Imei varchar(64), CreationDate date, DeviceId int);

INSERT #DeviceHistory VALUES
(1,'ProductionDevice299','2018-11-04',1),
(7,'ProductionDevice299','2018-11-07',1);

CREATE TABLE #Applications
  (Id int, Name varchar(64), DeviceHistoryId int);

INSERT #Applications VALUES
(1, 'Enrollment.cone',1),
(2, 'DPC_OWNERS'     ,1),
(3, 'OTHER_APPS'     ,1),
(6, 'Enrollment.emp' ,7),
(7, 'DPC_OWNERS'     ,7);

CREATE TABLE #ApplicationAttributes
  (Id int, [Key] varchar(64), Value int, DeviceApplicationId int);

INSERT #ApplicationAttributes VALUES
(1, 'EnrolledTotal', 2 ,1),
(2, 'LoginsTotal'  , 5 ,2),
(3, 'OtherAttribt1', 8 ,3),
(4, 'OtherAttribt2', 12,3),
(5, 'OtherAttribt3', 17,3),
(6, 'EnrolledTotal', 21,6),
(7, 'LoginsTotal'  , 25,7);

Applying the date range to this as follows:

DECLARE @start date = '20181102', @end date = '20181109';

;WITH n(n) AS 
(
  SELECT 0 UNION ALL SELECT n+1 FROM n WHERE n < DATEDIFF(DAY, @start, @end)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @start) FROM n
), 
[CTE] AS (
SELECT 
    [Extent1].[Id] AS [id], 
    [Extent1].[Imei] AS [imei], 
    CAST([Extent1].[CreationDate] AS DATE) AS [lastSeenOnline],  
    [Extent1].[DeviceId] AS [deviceId],
    SUM(CAST([Extent4].[Value] AS BIGINT)) as [enrollTotalToday],

    LAG(SUM(CAST([Extent4].[Value] AS BIGINT)), 1, 0) OVER 
    (PARTITION BY [Extent1].[Imei] ORDER BY [Extent1].[CreationDate]) 
     AS [enrollTotalYesterday]
    FROM #DeviceHistory AS [Extent1]
    INNER JOIN (
        SELECT [Imei], max([CreationDate]) as MaxDate
        FROM #DeviceHistory
        GROUP BY [Imei], CAST([CreationDate] AS DATE)
    ) [Extent2] on [Extent1].[Imei] = [Extent2].[Imei] 
      and [Extent1].[CreationDate] = [Extent2].MaxDate
    INNER JOIN
    #Applications AS [Extent3]
    ON [Extent3].DeviceHistoryId = [Extent1].id 
        INNER JOIN
        #ApplicationAttributes [Extent4]
        ON [Extent4].DeviceApplicationId = [Extent3].id 
        AND [Extent4].[Key] = 'EnrolledTotal'
    GROUP BY [Extent1].Id, [Extent1].Imei, [Extent1].CreationDate, [Extent1].DeviceId
) 
SELECT 
  CTE.id,
  CreationDate = d.d,
  CTE.imei,
  CTE.deviceId,
  CTE.enrollTotalToday,
  CTE.enrollTotalYesterday 
FROM d 
LEFT OUTER JOIN CTE 
ON CTE.LastSeenOnline = d.d
ORDER BY d.d;

Gets you this, which admittedly isn't quite what you're after:

I'm not quite sure exactly what other data might be in these tables and what relationships you're going to use to determine that the missing rows should be populated with this imei and deviceId, or where those ids come from (they're not in your sample data). In this case the Applications table only has a single imei name, and all the data falls within the date range, but I can't imagine your real table is that simple. Other things also don't make sense to me, like why the enrollment for November 4th carries forward to the 5th, but the 7th doesn't carry to the 8th? You might have constructed the desired results because of things you know about the data or business rules you're applying in your head, but readers don't pick up on those, and there might not be a straightforward way to have SQL Server apply them, either.

As an aside, try to avoid using reserved keywords for column names (like Key).

Wouldn't it be better to use an already pre-populated [calendar table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/)? — Zohar Peled, Nov 11 '18 at 07:09
@AaronBertrand Thank you for explanation. According to your questions: this generation needfor data grid in web client. Situation is follow: we have some histories for some devices for example daily, but not each day device send info to server. That's why we need to show for days, when device didn't send info some generated data. I can do this using C# code, but this will take a time in comparison to sql request, which will return all data itself. — BorHunter, Nov 11 '18 at 08:17
@AaronBertrand that's why I need additional information, which should set in generated rows. — BorHunter, Nov 11 '18 at 08:18
@ZoharPeled Maybe? With 8 rows (or even 100 rows), it’s unlikely to have any impact. Also (1) most people don’t already have a calendar table (or even a numbers table), (2) setting one up adds a lot of potentially useless bulk to the answer, and (3) after much time here this proposal is usually met with “But I don’t want to create a table” or even “I don’t have permission to create a table.” — Aaron Bertrand, Nov 11 '18 at 13:01
@BorHunter if you already have the logic to populate the missing data in C#, just do it there. I feel like the time that takes should be negligible in any case, but it probably won’t be *less* time than adding similar logic in the query (even if we can figure out how to do that without turning this into an expensive cursor). — Aaron Bertrand, Nov 11 '18 at 13:04
@AaronBertrand "But I don’t want to create a table" LOL I've seen this kind of comment before... — Zohar Peled, Nov 11 '18 at 13:10

How to generate row between dates range in sql if for date don't have a record?

2 Answers2