Fill Missing Dates In a Date-Sequenced in SQL using Tally Table
You could do this using a Tally Table.
Basically, you use the Tally Table to generate sequence of dates from @startDate to @endDate and CROSS JOIN it to DISTINCT Item to generate all Date-Item combination. Then, the result will be LEFT-JOINed to tblSales to achieve the desired output.
SQL Fiddle
DECLARE
@startDate DATE = '20140101',
@endDate DATE = '20140105';
WITH E1(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM E4
)
,CteAllDates(Item, dt) AS(
SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
FROM Tally
CROSS JOIN(
SELECT DISTINCT Item
FROM tblSales
WHERE [Date] BETWEEN @startDate AND @endDate
) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
ON ts.Item = d.Item
AND ts.Date = d.dt
WHERE
ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt
Here is an alternative. Instead of the cascading CTEs, use sys.columns to generate the Tally Table.:
DECLARE
@startDate DATE = '20140101',
@endDate DATE = '20140105';
WITH Tally(N) AS(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
FROM Tally
CROSS JOIN(
SELECT DISTINCT Item
FROM tblSales
WHERE [Date] BETWEEN @startDate AND @endDate
) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
ON ts.Item = d.Item
AND ts.Date = d.dt
WHERE
ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt
Result
| Item | dt | Sales |
|---------|------------|--------|
| tenant1 | 2014-01-01 | 100 |
| tenant1 | 2014-01-02 | 100 |
| tenant1 | 2014-01-03 | 100 |
| tenant1 | 2014-01-04 | NULL |
| tenant1 | 2014-01-05 | 100 |
| tenant2 | 2014-01-01 | 100 |
| tenant2 | 2014-01-02 | NULL |
| tenant2 | 2014-01-03 | NULL |
| tenant2 | 2014-01-04 | 100 |
| tenant2 | 2014-01-05 | NULL |
| tenant3 | 2014-01-01 | 100 |
| tenant3 | 2014-01-02 | NULL |
| tenant3 | 2014-01-03 | 100 |
| tenant3 | 2014-01-04 | NULL |
| tenant3 | 2014-01-05 | 100 |
Tally Table to insert missing dates between two dates? SQL
Another option: (updated [again])
Create and populate sample table (Please save us this step in your next question)
DECLARE @T as TABLE
(
Staff_ID int,
[Date] date,
[Year] int,
Mon int,
[Day] int,
First_Name varchar(10),
Last_Name varchar(10),
Section varchar(10),
Time_Worked datetime
)
INSERT INTO @T VALUES
(1001, '2016-06-01', 2016, 6, 1, 'Bill', 'Price', 'Level 1', '2016-06-01 8:30:00.000'),
(1001, '2016-06-05', 2016, 6, 5, 'Bill', 'Price', 'Level 1', '2016-06-05 8:30:00.000'),
(1001, '2016-06-09', 2016, 6, 9, 'Bill', 'Price', 'Level 1', '2016-06-09 8:30:00.000'),
(1001, '2016-07-05', 2016, 7, 5, 'Bill', 'Price', 'Level 2', '2016-06-12 8:30:00.000'), -- Different month
(1002, '2016-06-01', 2016, 6, 1, 'Mary', 'Somers', 'Level 1', '2016-06-01 8:30:00.000'),
(1002, '2016-06-05', 2016, 6, 5, 'Mary', 'Somers', 'Level 1', '2016-06-05 8:30:00.000'),
(1002, '2016-06-08', 2016, 6, 8, 'Mary', 'Somers', 'Level 1', '2016-06-08 8:30:00.000'),
(1003, '2016-06-03', 2016, 6, 3, 'Mark', 'Jones', 'Level 1', '2016-06-03 8:30:00.000'),
(1003, '2016-06-04', 2016, 6, 4, 'Mark', 'Jones', 'Level 1', '2016-06-05 8:30:00.000')
Declare and populate @StartDate and @EndDate:
DECLARE @StartDate datetime = '2016-06-01',
@EndDate datetime = '2016-08-01'
The INSERT...SELECT statement: (I've chosen to use a cte so I wouldn't have to write the dateadd(DD,ID-1,@StartDate) so many times)
;WITH Calendar AS
(
SELECT dateadd(DD,ID-1,@StartDate) as [Date]
FROM dbo.Tally
WHERE dateadd(DD,ID-1,@StartDate) < @EndDate
)
INSERT INTO @T (Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section)
SELECT DISTINCT Staff_ID, C.[Date], Year(C.[Date]), MONTH(C.[Date]), DAY(C.[Date]), First_Name, Last_Name, Section
FROM @T T
CROSS APPLY
(
SELECT Cal.[Date]
FROM Calendar Cal
WHERE MONTH(Cal.[Date]) = MONTH(T.[Date])
AND YEAR(Cal.[Date]) = YEAR(T.[Date])
AND NOT EXISTS
(
SELECT 1
FROM @T T2
WHERE T.Staff_ID = T2.Staff_ID
AND T2.[Date] = Cal.[Date]
)
) C
Verify inserts:
SELECT Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section, Time_Worked
FROM @T
ORDER BY Staff_ID, [Date]
Results:
Staff_ID Date Year Mon Day First_Name Last_Name Section Time_Worked
----------- ---------- ----------- ----------- ----------- ---------- ---------- ---------- -----------------------
1001 2016-06-01 2016 6 1 Bill Price Level 1 2016-06-01 08:30:00.000
1001 2016-06-02 2016 6 2 Bill Price Level 1 NULL
1001 2016-06-03 2016 6 3 Bill Price Level 1 NULL
1001 2016-06-04 2016 6 4 Bill Price Level 1 NULL
1001 2016-06-05 2016 6 5 Bill Price Level 1 2016-06-05 08:30:00.000
1001 2016-06-06 2016 6 6 Bill Price Level 1 NULL
1001 2016-06-07 2016 6 7 Bill Price Level 1 NULL
1001 2016-06-08 2016 6 8 Bill Price Level 1 NULL
1001 2016-06-09 2016 6 9 Bill Price Level 1 2016-06-09 08:30:00.000
1001 2016-06-10 2016 6 10 Bill Price Level 1 NULL
1001 2016-06-11 2016 6 11 Bill Price Level 1 NULL
1001 2016-06-12 2016 6 12 Bill Price Level 1 NULL
1001 2016-06-13 2016 6 13 Bill Price Level 1 NULL
1001 2016-06-14 2016 6 14 Bill Price Level 1 NULL
1001 2016-06-15 2016 6 15 Bill Price Level 1 NULL
1001 2016-06-16 2016 6 16 Bill Price Level 1 NULL
1001 2016-06-17 2016 6 17 Bill Price Level 1 NULL
1001 2016-06-18 2016 6 18 Bill Price Level 1 NULL
1001 2016-06-19 2016 6 19 Bill Price Level 1 NULL
1001 2016-06-20 2016 6 20 Bill Price Level 1 NULL
1001 2016-06-21 2016 6 21 Bill Price Level 1 NULL
1001 2016-06-22 2016 6 22 Bill Price Level 1 NULL
1001 2016-06-23 2016 6 23 Bill Price Level 1 NULL
1001 2016-06-24 2016 6 24 Bill Price Level 1 NULL
1001 2016-06-25 2016 6 25 Bill Price Level 1 NULL
1001 2016-06-26 2016 6 26 Bill Price Level 1 NULL
1001 2016-06-27 2016 6 27 Bill Price Level 1 NULL
1001 2016-06-28 2016 6 28 Bill Price Level 1 NULL
1001 2016-06-29 2016 6 29 Bill Price Level 1 NULL
1001 2016-06-30 2016 6 30 Bill Price Level 1 NULL
1001 2016-07-01 2016 7 1 Bill Price Level 2 NULL
1001 2016-07-02 2016 7 2 Bill Price Level 2 NULL
1001 2016-07-03 2016 7 3 Bill Price Level 2 NULL
1001 2016-07-04 2016 7 4 Bill Price Level 2 NULL
1001 2016-07-05 2016 7 5 Bill Price Level 2 2016-06-12 08:30:00.000
1001 2016-07-06 2016 7 6 Bill Price Level 2 NULL
1001 2016-07-07 2016 7 7 Bill Price Level 2 NULL
1001 2016-07-08 2016 7 8 Bill Price Level 2 NULL
1001 2016-07-09 2016 7 9 Bill Price Level 2 NULL
1001 2016-07-10 2016 7 10 Bill Price Level 2 NULL
1001 2016-07-11 2016 7 11 Bill Price Level 2 NULL
1001 2016-07-12 2016 7 12 Bill Price Level 2 NULL
1001 2016-07-13 2016 7 13 Bill Price Level 2 NULL
1001 2016-07-14 2016 7 14 Bill Price Level 2 NULL
1001 2016-07-15 2016 7 15 Bill Price Level 2 NULL
1001 2016-07-16 2016 7 16 Bill Price Level 2 NULL
1001 2016-07-17 2016 7 17 Bill Price Level 2 NULL
1001 2016-07-18 2016 7 18 Bill Price Level 2 NULL
1001 2016-07-19 2016 7 19 Bill Price Level 2 NULL
1001 2016-07-20 2016 7 20 Bill Price Level 2 NULL
1001 2016-07-21 2016 7 21 Bill Price Level 2 NULL
1001 2016-07-22 2016 7 22 Bill Price Level 2 NULL
1001 2016-07-23 2016 7 23 Bill Price Level 2 NULL
1001 2016-07-24 2016 7 24 Bill Price Level 2 NULL
1001 2016-07-25 2016 7 25 Bill Price Level 2 NULL
1001 2016-07-26 2016 7 26 Bill Price Level 2 NULL
1001 2016-07-27 2016 7 27 Bill Price Level 2 NULL
1001 2016-07-28 2016 7 28 Bill Price Level 2 NULL
1001 2016-07-29 2016 7 29 Bill Price Level 2 NULL
1001 2016-07-30 2016 7 30 Bill Price Level 2 NULL
1001 2016-07-31 2016 7 31 Bill Price Level 2 NULL
1002 2016-06-01 2016 6 1 Mary Somers Level 1 2016-06-01 08:30:00.000
1002 2016-06-02 2016 6 2 Mary Somers Level 1 NULL
1002 2016-06-03 2016 6 3 Mary Somers Level 1 NULL
1002 2016-06-04 2016 6 4 Mary Somers Level 1 NULL
1002 2016-06-05 2016 6 5 Mary Somers Level 1 2016-06-05 08:30:00.000
1002 2016-06-06 2016 6 6 Mary Somers Level 1 NULL
1002 2016-06-07 2016 6 7 Mary Somers Level 1 NULL
1002 2016-06-08 2016 6 8 Mary Somers Level 1 2016-06-08 08:30:00.000
1002 2016-06-09 2016 6 9 Mary Somers Level 1 NULL
1002 2016-06-10 2016 6 10 Mary Somers Level 1 NULL
1002 2016-06-11 2016 6 11 Mary Somers Level 1 NULL
1002 2016-06-12 2016 6 12 Mary Somers Level 1 NULL
1002 2016-06-13 2016 6 13 Mary Somers Level 1 NULL
1002 2016-06-14 2016 6 14 Mary Somers Level 1 NULL
1002 2016-06-15 2016 6 15 Mary Somers Level 1 NULL
1002 2016-06-16 2016 6 16 Mary Somers Level 1 NULL
1002 2016-06-17 2016 6 17 Mary Somers Level 1 NULL
1002 2016-06-18 2016 6 18 Mary Somers Level 1 NULL
1002 2016-06-19 2016 6 19 Mary Somers Level 1 NULL
1002 2016-06-20 2016 6 20 Mary Somers Level 1 NULL
1002 2016-06-21 2016 6 21 Mary Somers Level 1 NULL
1002 2016-06-22 2016 6 22 Mary Somers Level 1 NULL
1002 2016-06-23 2016 6 23 Mary Somers Level 1 NULL
1002 2016-06-24 2016 6 24 Mary Somers Level 1 NULL
1002 2016-06-25 2016 6 25 Mary Somers Level 1 NULL
1002 2016-06-26 2016 6 26 Mary Somers Level 1 NULL
1002 2016-06-27 2016 6 27 Mary Somers Level 1 NULL
1002 2016-06-28 2016 6 28 Mary Somers Level 1 NULL
1002 2016-06-29 2016 6 29 Mary Somers Level 1 NULL
1002 2016-06-30 2016 6 30 Mary Somers Level 1 NULL
1003 2016-06-01 2016 6 1 Mark Jones Level 1 NULL
1003 2016-06-02 2016 6 2 Mark Jones Level 1 NULL
1003 2016-06-03 2016 6 3 Mark Jones Level 1 2016-06-03 08:30:00.000
1003 2016-06-04 2016 6 4 Mark Jones Level 1 2016-06-05 08:30:00.000
1003 2016-06-05 2016 6 5 Mark Jones Level 1 NULL
1003 2016-06-06 2016 6 6 Mark Jones Level 1 NULL
1003 2016-06-07 2016 6 7 Mark Jones Level 1 NULL
1003 2016-06-08 2016 6 8 Mark Jones Level 1 NULL
1003 2016-06-09 2016 6 9 Mark Jones Level 1 NULL
1003 2016-06-10 2016 6 10 Mark Jones Level 1 NULL
1003 2016-06-11 2016 6 11 Mark Jones Level 1 NULL
1003 2016-06-12 2016 6 12 Mark Jones Level 1 NULL
1003 2016-06-13 2016 6 13 Mark Jones Level 1 NULL
1003 2016-06-14 2016 6 14 Mark Jones Level 1 NULL
1003 2016-06-15 2016 6 15 Mark Jones Level 1 NULL
1003 2016-06-16 2016 6 16 Mark Jones Level 1 NULL
1003 2016-06-17 2016 6 17 Mark Jones Level 1 NULL
1003 2016-06-18 2016 6 18 Mark Jones Level 1 NULL
1003 2016-06-19 2016 6 19 Mark Jones Level 1 NULL
1003 2016-06-20 2016 6 20 Mark Jones Level 1 NULL
1003 2016-06-21 2016 6 21 Mark Jones Level 1 NULL
1003 2016-06-22 2016 6 22 Mark Jones Level 1 NULL
1003 2016-06-23 2016 6 23 Mark Jones Level 1 NULL
1003 2016-06-24 2016 6 24 Mark Jones Level 1 NULL
1003 2016-06-25 2016 6 25 Mark Jones Level 1 NULL
1003 2016-06-26 2016 6 26 Mark Jones Level 1 NULL
1003 2016-06-27 2016 6 27 Mark Jones Level 1 NULL
1003 2016-06-28 2016 6 28 Mark Jones Level 1 NULL
1003 2016-06-29 2016 6 29 Mark Jones Level 1 NULL
1003 2016-06-30 2016 6 30 Mark Jones Level 1 NULL
Find missing date ranges between two dates
A note, I am assuming here that the expected results for your final expected results is wrong here as it doesn't match the other 2. The last and first rows in the expected results for the last set both have a value for ID that isn't 0, but no explanation of why they do is given. I therefore assume the value should be 0 like the row in the "middle".
To do this, I use a Tally to get all the dates between the date range you need; the Tally is limited to 1,000 rows, a little shy of 3 years, but you can cross join to N more if you need more rows. I then use that tally to create an inline calendar table. Next I LEFT JOIN that calendar to your data, and use a gaps and island method to put the values into groups. Finally I then aggregate on those groups, getting the MIN and MAX date in each one:
USE Sandbox;
GO
CREATE TABLE dbo.YourTable (ID int,
FromDate date,
ToDate date);
INSERT INTO dbo.YourTable
VALUES(1,'20210505','20210510'),
(2,'20210517','20210525');
GO
DECLARE @StartDate date = '20210501',
@EndDate date = '20210528';
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), --1000 days
Dates AS(
SELECT DATEADD(DAY, T.I, @StartDate) AS [Date],
T.I
FROM Tally T),
Grps AS(
SELECT D.[Date],
YT.ID,
D.I - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY D.[Date]) AS Grp
FROM Dates D
LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.FromDate AND D.[Date] <= YT.ToDate)
SELECT ISNULL(MAX(G.ID),0) AS ID,
MIN(G.[Date]) AS FromDate,
MAX(G.[Date]) AS ToDate
FROM Grps G
GROUP BY G.Grp
ORDER BY FromDate ASC;
GO
DROP TABLE dbo.YourTable;
db<>fiddle
Fill in missing dates in SQL Table and take last market value
One big problem is that you actually want two different things on each date:
- The "instant" value of the row (fee, income, etc).
- The ongoing value of a column (market value).
Now that we know what we're looking for, we can construct our statement.
First, I'm going to assume that you have both a calendar table and an account table (or would be only interested in one account, and don't need the extra join). We'll need to deal with the calendar data a bit, but accounts should be fine as-is. These form the initial basis of the query:
SELECT Account.account,
-- instantaneous columns
-- ongoing columns
Calendar.calendarDate
FROM (SELECT MAX(calendarDate) AS calendarDate
FROM Calendar
GROUP BY calendarYear, calendarMonth) Calendar
CROSS JOIN Account
This gives us a list of all accounts with all dates. You can add restrictions as necessary - you probably have dates in the future, after all - but the important part is getting the max date of each month. (Personally, I probably would have gone for the first day of the month because it's far easier to index that, but this works) The resulting Calendar query table is likely to be pulled into memory - it's very small (12 rows a year!).
Next comes getting the "instantaneous" row. Now that we have our "base" data, a simple join suffices:
COALESCE(MarketData.netFlow, 0) AS netFlow, COALESCE(MarketData.feeAmount, 0) AS feeAmount,
COALESCE(MarketData.income, 0) AS income, COALESCE(MarketData.TWR, 0) AS TWR,
......
LEFT JOIN MarketData
ON MarketData.marketDate = Calendar.calendarDate
AND MarketData.account = Account.account
... so if we have a row there, then display it. When we don't have a row, the value is 0.
And lastly, we need the "ongoing" value. This we have to collect separately. Now, normally you want to use something like LAG(marketValue)... unfortunately, the join to our "base" tables gives us a bunch of rows where marketValue is null, so the windowing would return that instead of our "previous" value. We need to create a range-query table.
A range query table is where you have an upper and lower bound for a given key. In the case of dates (like all positive-range key values), this is lower-bound inclusive (>=) and upper-bound exclusive (<). Essentially, our upper-bound here is the first instant we have a new market value (the old one is superseded). This we can use LEAD(...) to get:
MarketValue.marketValue,
........
JOIN (SELECT account, marketValue,
marketDate AS valueStartDate,
LEAD(marketDate, 1, '99991231') OVER (PARTITION BY account ORDER BY marketDate) AS valueEndDate
FROM MarketData) MarketValue
ON Calendar.calendarDate >= MarketValue.valueStartDate
AND Calendar.calendarDate < MarketValue.valueEndDate
AND MarketValue.Account = Account.account
Our MarketValue inline query returns a table that looks something like this:
33L951572 | 375645.74 | 2004-03-31 | 2004-12-31
... that we can join to for each row. Note how the join condition is constructed - this makes it so that there isn't a conflict between "old" and "new" marketValues. On the last row, because LEAD(...) would return a null value, we return the "next" day; because (again) we use an exclusive upper-bound, this makes our last entry the last joinable row.
Putting it all together gives this:
SELECT Account.account,
COALESCE(MarketData.netFlow, 0) AS netFlow, COALESCE(MarketData.feeAmount, 0) AS feeAmount,
COALESCE(MarketData.income, 0) AS income, COALESCE(MarketData.TWR, 0) AS TWR,
MarketValue.marketValue,
Calendar.calendarDate
FROM (SELECT MAX(calendarDate) AS calendarDate
FROM Calendar
GROUP BY calendarYear, calendarMonth) Calendar
CROSS JOIN Account
LEFT JOIN MarketData
ON MarketData.marketDate = Calendar.calendarDate
AND MarketData.account = Account.account
JOIN (SELECT account, marketValue,
marketDate AS valueStartDate,
LEAD(marketDate, 1, DATEADD(day, 1, marketDate)) OVER (PARTITION BY account ORDER BY marketDate) AS valueEndDate
FROM MarketData) MarketValue
ON Calendar.calendarDate >= MarketValue.valueStartDate
AND Calendar.calendarDate < MarketValue.valueEndDate
AND MarketValue.Account = Account.account
ORDER BY Account.account, Calendar.calendarDate
SQL Fiddle Example
(don't forget the outer ORDER BY, or rows may appear where you least expect them!)
Modifying the query
For each additional criteria to partition, or "repeat" by, there are a few simple steps to take.
First, you need to add the "base" reference, to ensure all rows are present:
-- I'm assuming you have a code reference table.
-- Otherwise, create it like I did for the account table
CROSS JOIN AssetClass
- Step 1b - use this base reference for the columns in the
SELECT, and probably theORDER BYas well.
Second, you need to add the extra key value to both "child" table join conditions:
-- Because asset-class - 'Cash', etc - are _dependent_ values,
-- we only need the code key in this case
AND MarketData.assetClassCode = AssetClass.assetClassCode
Lastly, you need to add the relevant column to the partitioning:
... OVER (PARTITION BY account, assetClassCode ORDER BY marketDate) ...
Resulting in:
SELECT Account.account,
COALESCE(MarketData.netFlow, 0) AS netFlow, COALESCE(MarketData.feeAmount, 0) AS feeAmount,
COALESCE(MarketData.income, 0) AS income, COALESCE(MarketData.TWR, 0) AS TWR,
AssetClass.assetClassCode, AssetClass.assetClass,
MarketValue.marketValue,
Calendar.calendarDate
FROM (SELECT MAX(calendarDate) AS calendarDate
FROM Calendar
GROUP BY calendarYear, calendarMonth) Calendar
CROSS JOIN Account
CROSS JOIN AssetClass
LEFT JOIN MarketData
ON MarketData.account = Account.account
AND MarketData.assetClassCode = AssetClass.assetClassCode
AND MarketData.marketDate = Calendar.calendarDate
JOIN (SELECT account, marketValue,
marketDate AS valueStartDate,
LEAD(marketDate, 1, DATEADD(day, 1, marketDate)) OVER (PARTITION BY account, assetClassCode ORDER BY marketDate) AS valueEndDate
FROM MarketData) MarketValue
ON MarketValue.Account = Account.account
AND MarketValue.assetClassCode = AssetClass.assetClassCode
AND Calendar.calendarDate >= MarketValue.valueStartDate
AND Calendar.calendarDate < MarketValue.valueEndDate
ORDER BY Account.account, Calendar.calendarDate, AssetClass.assetClassCode
SQL Fiddle Example
(Note that I've adjusted the ordering of the conditions in the JOIN and LEFT JOIN, to better reflect the "primary" keys used: account and asset class code)
How to get missing dates with 0 value in SQL Server?
To see a particular value, the value must come from a row. So to see dates that don't exist on your login table, you must generated them as rows somewhere.
You can use a simple recursive CTE to generate 1 row per day between a particular interval, then use a LEFT JOIN to join logins that match on that particular day. The ones that don't match will still be displayed, since we are using LEFT JOIN.
DECLARE @GeneratingDateFrom DATE = DATEADD(DAY, -7, GETDATE())
DECLARE @GeneratingDateTo DATE = GETDATE()
;WITH GeneratedDates AS
(
SELECT
GeneratedDate = @GeneratingDateFrom
UNION ALL
SELECT
GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
FROM
GeneratedDates AS G
WHERE
DATEADD(DAY, 1, G.GeneratedDate) < @GeneratingDateTo
)
SELECT
G.GeneratedDate,
count(distinct L.LoginID) as UserCount
FROM
GeneratedDates AS G
LEFT JOIN [Login] AS L ON G.GeneratedDate = CONVERT(date, L.LoginTime)
GROUP BY
G.GeneratedDate
ORDER BY
G.GeneratedDate desc
find the missing entries for the working days and fill the row with the values from the closest date
For these types of query you gain significant performance benefits from creating a calendar table containing every date you'll ever need to test. (If you're familiar with the term "dimension tables", this is just one such table to enumerate every date of interest.)
Also, the query as a whole can become significantly simpler.
SELECT
cal.calendar_date AS data_date,
CASE WHEN prev_data.gap <= next_data.gap
THEN prev_data.data_value
ELSE COALESCE(next_data.data_value, prev_data.data_value)
END
AS data_value
FROM
calendar AS cal
OUTER APPLY
(
SELECT TOP(1)
data_date,
data_value,
DATEDIFF(DAY, data_date, cal.calendar_date) AS gap
FROM
data_table
WHERE
data_date <= cal.calendar_date
ORDER BY
data_date DESC
)
prev_data
OUTER APPLY
(
SELECT TOP(1)
data_date,
data_value,
DATEDIFF(DAY, cal.calendar_date, data_date) AS gap
FROM
data_table
WHERE
data_date > cal.calendar_date
ORDER BY
data_date ASC
)
next_data
WHERE
cal.calendar_date BETWEEN '2015-01-01' AND '2015-12-31'
;
EDIT Reply to your comment with a different requirement
To always get "the value above" is easier, and to insert those values in to a table is easy enough...
INSERT INTO
data_table
SELECT
cal.calendar_date,
prev_data.data_value
FROM
calendar AS cal
CROSS APPLY
(
SELECT TOP(1)
data_date,
data_value
FROM
data_table
WHERE
data_date <= cal.calendar_date
ORDER BY
data_date DESC
)
prev_data
WHERE
cal.calendar_date BETWEEN '2015-01-01' AND '2015-12-31'
AND cal.calendar_date <> prev_data.data_date
;
Note: You could add WHERE prev_data.gap > 0 to the bigger query above to only get dates that don't already have data.
Fill missing months on a date query
I would use a recursive CTE to produce the dates for the year:
with dates as (
select datefromparts(year(getdate()), 1, 1) as yyyymm
union all
select dateadd(month, 1, yyyymm)
from dates
where yyyymm < getdate()
)
select d.yyyymm, t1.partner, count(t1.issues)
from dates d left join
temp1 t1
Related Topics
How to Substitute a String If Record Is Null in T-Sql
How to Count the Number of Words in a String in Oracle
Ms Access: Setting Table Column Caption or Description in Ddl
Complex Sort of Field "String - Number - String"
Linq Orderby. Does It Always Return the Same Ordered List
Trouble Making a Running Sum in Access Query
Handling Unicode Characters That Aren't Displayed Correctly in SQL Query
How to Display Number Value in Words
Oracle - Select Count on a Subquery
SQL Server:Return Column Names Based on a Record's Value
Different Ways to Alias a Column
Hamming Weight/Population Count in T-Sql
St_Hexagongrid Geom Vector to Find All Points
Timezone Date Format in Oracle
Calculate the Last Day of the Prior Quarter