How to Get Total Count Value Each Day Upto 5 Days

SQL Server: Get Count of past 7 days for each day

If 2012+ You can use the Window functions with the preceding clause

Declare @YourTable table (updateDate date,Type varchar(25))
Insert Into @YourTable values
('2016-05-31','Thing1'),
('2016-05-31','Thing2'),
('2016-05-31','Thing3'),
('2016-05-30','Thing1'),
('2016-05-29','Thing2'),
('2016-05-28','Thing1'), 
('2016-05-28','Thing3'), 
('2016-05-27','Thing1'),
('2016-05-26','Thing1')

Select *,ThingCount=sum(1) over(Partition By Type order by updateDate rows between 7 preceding and current row) 
 From  @YourTable

Returns

enter image description here

SQL Count for each date

You can use:

Select
     count(created_date) as counted_leads,
     created_date as count_date
from
     table
group by
     created_date

Count for each day - the number of days until the closer workday

Seems like you just need to put your data into groups, and then ROW_NUMBER:

create table #tmp ( [date] date, is_holiday int )
insert into #tmp ( date, is_holiday )
VALUES ('2008-01-05', 1),
       ('2008-01-06', 1),
       ('2008-01-07', 1),
       ('2008-01-08', 1),
       ('2008-01-09', 0),
       ('2008-01-10', 0),
       ('2008-01-11', 0),
       ('2008-01-12', 1),
       ('2008-01-13', 1),
       ('2008-01-14', 0),
       ('2008-01-15', 0),
       ('2008-01-16', 0),
       ('2008-01-17', 0);
GO
WITH CTE AS(
    SELECT [date],
           is_holiday,
           COUNT(CASE is_holiday WHEN 0 THEN 1 END) OVER (ORDER BY [date] ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS Grp
    FROM #tmp t)
SELECT [date],
       is_holiday,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY [date] DESC) AS desirableresult
FROM CTE
ORDER BY [date];

GO
DROP TABLE #tmp;

Counting appointments for each day using MYSQL

First: Consolidate range checking

First of all your two range where conditions can be replaced by a single one. And it also seems that you're only counting appointments that either completely overlap target date range or are completely contained within. Partially overlapping ones aren't included. Hence your question about appointments that end right on the range starting date.

To make where clause easily understandable I'll simplify it by using:

two variables to define target range:
- rangeStart (in your case 1st Nov 2012)
- rangeEnd (I'll rather assume to 1st Dec 2012 00:00:00.00000)
won't be converting datetime to dates only (using date function) the way that you did, but you can easily do that.

With these in mind your where clause can be greatly simplified and covers all appointments for given range:

...
where (c.StartingDate < rangeEnd) and (c.EndingDate >= rangeStart)
...

This will search for all appointments that fall in target range and will cover all these appointment cases:

                           start          end
target range               |==============|

partial front       |---------|
partial back                            |---------|
total overlap          |---------------------|
total containment               |-----|

Partial front/back may also barely touch your target range (what you've been after).

Second: Resolving the problem

Why you're missing the first record? Simply because of your having clause that only collects those groups that have more than 1 appointment starting on a given day: 15th Nov has two, but 14th has only one and is therefore excluded because Count = 1 and is not > 1.

To answer your second question what am I missing is: you're not missing anything, actually you have too much in your statement and needs to simplified.

Try this statement instead that should return exactly what you're after:

select count(c.GUID) as Count,
       date(c.StartingDate) as Datum
from t_calendar c
where (c.GUID = 'blabla') and
      (c.StartingDate < str_to_date('2012-12-01', '%Y-%m-%d') and
      (c.EndingDate >= str_to_date('2012-11-01', '%Y-%m-%d'))
group by date(c.StartingDate)

I used str_to_date function to make string to date conversion more safe.

I'm not really sure why you included having in your statement, because it's not really needed. Unless your actual statement is more complex and you only included part that's most relevant. In that case you'll likely have to change it to:

having Count > 0

Getting appointment count per day in any given date range

There are likely other ways as well but the most common way would be using a numbers or ?calendar* table that gives you the ability to break a range into individual points - days. They you have to join your appointments to this numbers table and provide results.

I've created a SQLFiddle that does the trick. Here's what it does...

Suppose you have numbers table Num with numbers from 0 to x. And appointments table Cal with your records. Following script created these two tables and populates some data. Numbers are only up to 100 which is enough for 3 months worth of data.

-- appointments
create table Cal (
  Id int not null auto_increment primary key,
  StartDate datetime not null,
  EndDate datetime not null
);

-- create appointments
insert Cal (StartDate, EndDate)
values
  ('2012-10-15 08:00:00', '2012-10-20 16:00:00'),
  ('2012-10-25 08:00:00', '2012-11-01 03:00:00'),
  ('2012-11-01 12:00:00', '2012-11-01 15:00:00'),
  ('2012-11-15 10:00:00', '2012-11-16 10:00:00'),
  ('2012-11-20 08:00:00', '2012-11-30 08:00:00'),
  ('2012-11-30 22:00:00', '2012-12-05 00:00:00'),
  ('2012-12-01 05:00:00', '2012-12-10 12:00:00');

-- numbers table
create table Nums (
  Id int not null primary key
);

-- add 100 numbers
insert into Nums
select a.a + (10 * b.a)
from (select 0 as a union all
      select 1 union all
      select 2 union all
      select 3 union all
      select 4 union all
      select 5 union all
      select 6 union all
      select 7 union all
      select 8 union all
      select 9) as a,
     (select 0 as a union all
      select 1 union all
      select 2 union all
      select 3 union all
      select 4 union all
      select 5 union all
      select 6 union all
      select 7 union all
      select 8 union all
      select 9) as b

Now what you have to do now is

Select a range of days which you do by selecting numbers from Num table and convert them to dates.
Then join your appointments to those dates so that those appointments that fall on particular day are joined to that particular day
Then just group all these appointments per each day and get results

Here's the code that does this:

-- just in case so comparisons don't trip over
set names 'latin1' collate latin1_general_ci;

-- start and end target date range
set @s := str_to_date('2012-11-01', '%Y-%m-%d');
set @e := str_to_date('2012-12-01', '%Y-%m-%d');

-- get appointment count per day within target range of days
select adddate(@s, n.Id) as Day, count(c.Id) as Appointments
from Nums n
  left join Cal c
  on ((date(c.StartDate) <= adddate(@s, n.Id)) and (date(c.EndDate) >= adddate(@s, n.Id)))
where adddate(@s, n.Id) < @e
group by Day;

And this is the result of this rather simple select statement:

|        DAY | APPOINTMENTS |
-----------------------------
| 2012-11-01 |            2 |
| 2012-11-02 |            0 |
| 2012-11-03 |            0 |
| 2012-11-04 |            0 |
| 2012-11-05 |            0 |
| 2012-11-06 |            0 |
| 2012-11-07 |            0 |
| 2012-11-08 |            0 |
| 2012-11-09 |            0 |
| 2012-11-10 |            0 |
| 2012-11-11 |            0 |
| 2012-11-12 |            0 |
| 2012-11-13 |            0 |
| 2012-11-14 |            0 |
| 2012-11-15 |            1 |
| 2012-11-16 |            1 |
| 2012-11-17 |            0 |
| 2012-11-18 |            0 |
| 2012-11-19 |            0 |
| 2012-11-20 |            1 |
| 2012-11-21 |            1 |
| 2012-11-22 |            1 |
| 2012-11-23 |            1 |
| 2012-11-24 |            1 |
| 2012-11-25 |            1 |
| 2012-11-26 |            1 |
| 2012-11-27 |            1 |
| 2012-11-28 |            1 |
| 2012-11-29 |            1 |
| 2012-11-30 |            2 |

Grouping by day then count result for each day

The slightly tricky bit is to only count a customer once for a given day and type, even if they have multiple records for that day:

select 
   visitDate, 
   sum(case when customerType = 'A' then 1 else 0 end) as TypeA,
   sum(case when customerType = 'B' then 1 else 0 end) as TypeB,
   count(*) as Total
from (
    select distinct 
        customer, 
        cast(visitdate as date) as visitdate, 
        customertype from activity
    ) x
group by 
    visitdate

Example SQLFiddle

Aggregate per day counting different IDs in R

With the current function, we can split the 'date' by 'ID' column, apply the function, and rbind the list output to a single data.frame with ID as another column

lst1 <- lapply(split(df$date, df$ID), M)
out <- do.call(rbind, Map(cbind, ID = names(lst1), lst1))
row.names(out) <- NULL

-output

> str(out)
'data.frame':   124 obs. of  3 variables:
 $ ID       : chr  "1" "1" "1" "1" ...
 $ day      : Date, format: "2016-01-01" "2016-01-02" "2016-01-03" "2016-01-04" ...
 $ Admission: int  1 0 0 0 1 0 1 0 0 0 ...
> head(out)
  ID        day Admission
1  1 2016-01-01         1
2  1 2016-01-02         0
3  1 2016-01-03         0
4  1 2016-01-04         0
5  1 2016-01-05         1
6  1 2016-01-06         0

Or using tidyverse, do a group by operation

library(dplyr)
library(tidyr)
df %>%
  group_by(ID) %>% 
  summarise(out = M(date), .groups = 'drop') %>%
  unpack(out)