How to fill missing dates by groups in a table in sql
You can do it like this without loops
SELECT p.date, COALESCE(a.value, 0) value, p.grp_no
FROM
(
SELECT grp_no, date
FROM
(
SELECT grp_no, MIN(date) min_date, MAX(date) max_date
FROM tableA
GROUP BY grp_no
) q CROSS JOIN tableb b
WHERE b.date BETWEEN q.min_date AND q.max_date
) p LEFT JOIN TableA a
ON p.grp_no = a.grp_no
AND p.date = a.date
The innermost subquery grabs min and max dates per group. Then cross join with TableB
produces all possible dates within the min-max range per group. And finally outer select uses outer join with TableA
and fills value
column with 0
for dates that are missing in TableA
.
Output:
| DATE | VALUE | GRP_NO |
|------------|-------|--------|
| 2012-08-06 | 1 | 1 |
| 2012-08-07 | 0 | 1 |
| 2012-08-08 | 1 | 1 |
| 2012-08-09 | 0 | 1 |
| 2012-08-07 | 2 | 2 |
| 2012-08-08 | 1 | 2 |
| 2012-08-09 | 0 | 2 |
| 2012-08-10 | 0 | 2 |
| 2012-08-11 | 0 | 2 |
| 2012-08-12 | 3 | 2 |
Here is SQLFiddle demo
Query for how to add the missing dates in sql
Form a Date Calender with a start and end date range and perform a left join with your table to get the needed result.
e.g.
DECLARE @t TABLE(Dt Datetime, Value VARCHAR(20) NULL)
INSERT INTO @t VALUES
('05/28/2012',NULL),
('05/29/2012',NULL),
('05/30/2012',NULL),('05/30/2012','Break In'),('05/30/2012','Break Out'),
('05/31/2012',NULL),
('06/03/2012',NULL),('06/03/2012','Break In'),('06/03/2012','Break Out'),('06/03/2012','In Duty'),('06/03/2012','Out Duty'),
('06/04/2012',NULL),('06/04/2012','In Duty'),('06/04/2012','Out Duty'),
('06/05/2012',NULL),('06/05/2012','Break In'),('06/05/2012','Break Out'),
('06/06/2012',NULL),('06/06/2012','Break In'),('06/06/2012','Break Out'),('06/06/2012','In Duty'),('06/06/2012','Out Duty'),
('06/07/2012',NULL),('06/07/2012','In Duty'),('06/07/2012','Out Duty'),
('06/10/2012',NULL),('06/10/2012','Break Out'),('06/10/2012','In Duty'),('06/10/2012','Out Duty'),
('06/11/2012',NULL),('06/11/2012','In Duty'),('06/11/2012','Out Duty'),
('06/12/2012',NULL),
('06/13/2012',NULL),
('06/14/2012',NULL)
DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = '2012-05-28', @endDate = '2012-06-14' --yyyy-mm-dd
;WITH Calender AS (
SELECT @startDate AS CalanderDate
UNION ALL
SELECT CalanderDate + 1 FROM Calender
WHERE CalanderDate + 1 <= @endDate
)
SELECT
[Date] = Convert(VARCHAR(10),CalanderDate,101)
,Value
FROM Calender c
LEFT JOIN @t t
ON t.Dt = c.CalanderDate
Result
Date Value
05/28/2012 NULL
05/29/2012 NULL
05/30/2012 NULL
05/30/2012 Break In
05/30/2012 Break Out
05/31/2012 NULL
06/01/2012 NULL
06/02/2012 NULL
06/03/2012 NULL
06/03/2012 Break In
06/03/2012 Break Out
06/03/2012 In Duty
06/03/2012 Out Duty
06/04/2012 NULL
06/04/2012 In Duty
06/04/2012 Out Duty
06/05/2012 NULL
06/05/2012 Break In
06/05/2012 Break Out
06/06/2012 NULL
06/06/2012 Break In
06/06/2012 Break Out
06/06/2012 In Duty
06/06/2012 Out Duty
06/07/2012 NULL
06/07/2012 In Duty
06/07/2012 Out Duty
06/08/2012 NULL
06/09/2012 NULL
06/10/2012 NULL
06/10/2012 Break Out
06/10/2012 In Duty
06/10/2012 Out Duty
06/11/2012 NULL
06/11/2012 In Duty
06/11/2012 Out Duty
06/12/2012 NULL
06/13/2012 NULL
06/14/2012 NULL
Hope this helps
How to add missing dates when calculating count on a table
One option uses a recursive query to generate the dates. you can then cross join
that with the list of distinct items available in the table, and bring the table with a left join
. The last step is aggregation:
with cte as (
select min(convert(date, saletime)) as dt, max(convert(date, saletime)) as max_dt from mytable
union all
select dateadd(day, 1, dt), max_dt from cte where dt < max_dt
)
select c.dt, i.itemid, count(t.id) as sale_count
from cte c
cross join (select distinct itemid from mytable) i
left join mytable t
on t.itemid = i.itemid
and t.date >= c.dt
and t.date < dateadd(day, 1, c.dt)
group by c.dt, i.itemid
In a real life situation, you would probably have a separate referential table to store the items, that you would use instead of the select distinct
subquery.
How to fill in missing dates
Here is a query that would work. Start by cross joining all combinations of dates and users (add filters as needed), then left join the users table and calculate quota using the last_value() function (note that if you are using Snowflake, you must specify "rows between unbounded preceding and current row" as documented here):
with all_dates_users as (
--all combinations of dates and users
select date, user
from dates
cross join (select distinct user_email as user from users)
),
joined as (
--left join users table to the previous
select DU.date, DU.user, U.sent_at, U.user_email, U.score, U.quota
from all_dates_users DU
left join users U on U.sent_at = DU.date and U.user_email = DU.user
)
--calculate quota as previous quota using last_value() function
select date, user, nvl(score, 0) as score, last_value(quota) ignore nulls over (partition by user order by date desc rows between unbounded preceding and current row) as quota
from joined
order by date desc;
Hive SQL query to fill missing date values in table with nearest values
Get next date using lead() function, calculate difference in days, get string of spaces with length = diff in days, split, use posexplode to generate rows, use position to add to date to get missing dates:
with mytable as (--Demo dataset, use your table instead of this
select stack(10, --number of tuples
'Peter',float(50000),'2021-05-24',
'Peter',float(50035),'2021-05-25',
'Peter',float(50035),'2021-05-26',
'Peter',float(50610),'2021-05-28',
'Peter',float(51710),'2021-06-01',
'Peter',float(53028.1),'2021-06-02',
'Peter',float(53916.1),'2021-06-03',
'Mary',float(50000),'2021-05-24',
'Mary',float(50035),'2021-05-25',
'Mary',float(53028.1),'2021-05-30'
) as (account_name,available_balance,Date_of_balance)
) --use your table instead of this CTE
select account_name, available_balance, date_add(Date_of_balance,e.i) as Date_of_balance
from
( --Get next_date to generate date range
select account_name,available_balance,Date_of_balance,
lead(Date_of_balance,1, Date_of_balance) over (partition by account_name order by Date_of_balance) next_date
from mytable d --use your table
) s lateral view outer posexplode(split(space(datediff(next_date,Date_of_balance)-1),'')) e as i,x --generate rows
order by account_name desc, Date_of_balance --this is to have order of rows like in your Converted Table
Result:
account_name available_balance date_of_balance
Peter 50000 2021-05-24
Peter 50035 2021-05-25
Peter 50035 2021-05-26
Peter 50035 2021-05-27
Peter 50610 2021-05-28
Peter 50610 2021-05-29
Peter 50610 2021-05-30
Peter 50610 2021-05-31
Peter 51710 2021-06-01
Peter 53028.1 2021-06-02
Peter 53916.1 2021-06-03
Mary 50000 2021-05-24
Mary 50035 2021-05-25
Mary 50035 2021-05-26
Mary 50035 2021-05-27
Mary 50035 2021-05-28
Mary 50035 2021-05-29
Mary 53028.1 2021-05-30
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
Related Topics
Summarize the List into a Comma-Separated String
SQL Server: How to Group Multiple Row Values into Separate Columns
How to Use User Defined Table Type Inside Another User Defined Table Type in SQL
F# Type Provider for SQL in a Class
Interview - Detect/Remove Duplicate Entries
Multiple Rows into a Single Row and Combine Column SQL
Query JSONb Column Containing Array of JSON Objects
The Difference Between Comma and Join in SQL
Stop Access from Using Wrong Identity When Appending to Linked Table on SQL Server
SQL Access How to Return Between Dates
Amazon Redshift - Lateral Column Alias Reference
Calculate the Sum of the Column Which Has Time Datatype:
Cs50 Pset 7 13.Sql, I Can't Solve It, Nested SQLite3 Database
Setting Up a Development Environment to Learn Pl/Sql
Getting the Floor Value of a Number in SQLite
How to Add Sequenced Number Based on Sorted Value in Query in Access