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' ))

Answer 1

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.

Answer 2

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).