CODEWITHSUNDEEP
sqlsql-servert-sql
Mjcookie92
Mjcookie92

Reputation: 43

adding a row for missing data

Between a date range 2017-02-01 - 2017-02-10, i'm calculating a running balance. I have days where we have missing data, how would I include these missing dates with the previous days balance ?

Example data:

enter image description here

we are missing data for 2017-02-04,2017-02-05 and 2017-02-06, how would i add a row in the query with the previous balance? The date range is a parameter, so could change....

Can i use something like the lag function?

Upvotes: 0

Views: 84

Answers (3)

oscarvalles
oscarvalles

Reputation: 101

Adding to the date range & CTE solutions, I have created Date Dimension tables in numerous databases where I just left join to them.

There are free scripts online to create date dimension tables for SQL Server. I highly recommend them. Plus, it makes aggregation by other time periods much more efficient (e.g. Quarter, Months, Year, etc....)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would be inclined to use a recursive CTE and then fill in the values. Here is one approach using outer apply:

with dates as (
      select mind as dte, mind, maxd
      from (select min(date) as mind, max(date) as maxd from t) t
      union all
      select dateadd(day, 1, dte), mind, maxd
      from dates
      where dte < maxd
     )
select d.dte, t.balance
from dates d outer apply
     (select top 1 t.*
      from t
      where t.date <= d.dte
      order by t.date desc
     ) t;

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can generate dates using tally table as below:

Declare @d1 date ='2017-02-01'
Declare @d2 date ='2017-02-10'

;with cte_dates as (
    Select top (datediff(D, @d1, @d2)+1) Dates = Dateadd(day, Row_Number() over (order by (Select NULL))-1, @d1) from
        master..spt_values s1, master..spt_values s2
)
Select * from cte_dates left join ....

And do left join to your table and get running total

Upvotes: 0

Related Questions