SQL missed day count with rank() or other variants

Question

Running SQL Server 2008R2 here and I have an issue where my logic isn't quite kicking in yet, lol

ok, this is what I have:

+-----------+------------+------------------+-------+----------+----------+
| DAILYDAYS | MISSED_DAY | MISSED_DAY_COUNT | COUNT |  START   |   END    |
+-----------+------------+------------------+-------+----------+----------+
|  20140114 | (null)     | (null)           |     0 | 20140114 | 20140122 |
|  20140115 | (null)     | (null)           |     1 | 20140114 | 20140122 |
|  20140116 | (null)     | (null)           |     2 | 20140114 | 20140122 |
|  20140117 | (null)     | (null)           |     3 | 20140114 | 20140122 |
|  20140118 | (null)     | (null)           |     4 | 20140114 | 20140122 |
|  20140119 | (null)     | (null)           |     5 | 20140114 | 20140122 |
|  20140120 | 20140120   | 1                |     6 | 20140114 | 20140122 |
|  20140121 | (null)     | (null)           |     7 | 20140114 | 20140122 |
|  20140122 | (null)     | (null)           |     8 | 20140114 | 20140122 |
+-----------+------------+------------------+-------+----------+----------+

This is where I need to be:

+-----------+------------+------------------+-------+----------+----------+
| DAILYDAYS | MISSED_DAY | MISSED_DAY_COUNT | COUNT |  START   |   END    |
+-----------+------------+------------------+-------+----------+----------+
|  20140114 | (null)     | (null)           |     0 | 20140114 | 20140122 |
|  20140115 | (null)     | (null)           |     1 | 20140114 | 20140122 |
|  20140116 | (null)     | (null)           |     2 | 20140114 | 20140122 |
|  20140117 | (null)     | (null)           |     3 | 20140114 | 20140122 |
|  20140118 | (null)     | (null)           |     4 | 20140114 | 20140122 |
|  20140119 | (null)     | (null)           |     5 | 20140114 | 20140122 |
|  20140120 | 20140120   | 1                |     6 | 20140114 | 20140122 |
|  20140121 | (null)     | 2                |     7 | 20140114 | 20140122 |
|  20140122 | (null)     | 3                |     8 | 20140114 | 20140122 |
+-----------+------------+------------------+-------+----------+----------+

I have created a SQL Fiddle for you guys with a solution that I have tried with a left join but I failed, hence my asking here :

http://sqlfiddle.com/#!3/043de/7

Problem:

I have two tables: 1 that contains a table with rows for each day of the period and another table that contains only the date of "missed days"

I need to count the number of missed days and keep a running count in the table above based on the start and end [dates]

In my example, my 1st table contains rows for each day from 2014-01-14 through 2014-01-22 and another table has a missed day on 2014-01-20

So, i need to have a rank() or a dense_rank() or any other variants in order to get to the 1,2,3 counts...

Please note that I cannot use a CTE in this situation as my full table is over 800,000 lines, so that wouldn't be efficient IMO

Hoping some one can assist

Thanks :)

Edit: i forgot to mention that repeating is not necessary, its just that the only way i had thought of having a count of 1,2,3 with rank() is to have the value appear all the time in order to do a "partition by" inside the rank() function

i've updated the table above, is there a way to count the number of missed days until the end date you can think of ?

I think perhaps you'll have to [emulate `LAST_VALUE(... IGNORE NULLS)` with a self-join](http://stackoverflow.com/a/9919704/132382), and then RANK() those records. — pilcrow, Jun 05 '14 at 04:48
so you keep on repeating a missed day for every dailyday until there is a missed day ? This table makes no sense to me. — Erran Morad, Jun 05 '14 at 04:48
I can't understand the logic. You should add an example with 2 missed days. But to get results as in your question just change condition of JOIN in your query to `left join tbl_2 t2 on t1.[dailydays] >= t2.[missed_day]` — valex, Jun 05 '14 at 07:13

JumpingJezza · Accepted Answer · 2014-06-06T03:00:25.083

I scrapped the left join and replaced it with a union.

select 
  [dailydays]
  ,null as [missed_day]
  ,null as [missed_day_count] 
  ,[count]
  ,[start]
  ,[end]
from tbl_1
where dailydays < (select min(missed_day) from tbl_2)
union 
select 
   t1.[dailydays]
  ,t2.[missed_day]
  ,(t1.[count] - (select min([count]) from tbl_1 where [dailydays] = (select min([missed_day]) from tbl_2)) + 1)
  ,t1.[count]
  ,t1.[start]
  ,t1.[end]
from tbl_1 t1
left join tbl_2 t2 on t1.[dailydays] = t2.[missed_day]
where t1.dailydays >= (select min(missed_day) from tbl_2)
order by [dailydays]

Edit: or alternatively a complicated case statement:

select 
   t1.[dailydays]
  ,t2.[missed_day]
  , case when (t1.[count] - (select min([count]) from tbl_1 where [dailydays] = (select min([missed_day]) from tbl_2)) + 1) < 0 then 0 else
    (t1.[count] - (select min([count]) from tbl_1 where [dailydays] = (select min([missed_day]) from tbl_2)) + 1) end as [missed_day_count]
  ,t1.[count]
  ,t1.[start]
  ,t1.[end]
from tbl_1 t1
left join tbl_2 t2 on t1.[dailydays] = t2.[missed_day]
order by [dailydays]

Hi, i forgot to mention that repeating is not necessary, its just that the only way i had thought of having a count of 1,2,3 with rank() is to have the value appear all the time in order to do a "partition by" inside the rank() function — iamshazi, Jun 05 '14 at 14:44
is there a way to count without repeating the missed_day that you can think of ? so it counts from the missed day to the end day... ? — iamshazi, Jun 05 '14 at 14:45
I've added the left join on the missed days part of the union then so it only shows the day where it is missed. — JumpingJezza, Jun 06 '14 at 02:36

SQL missed day count with rank() or other variants

1 Answers1