Show All Dates Data Between Two Dates; If No Row Exists for Particular Date Then Show Zero in All Columns

SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

;with d(date) as (
  select cast('10/01/2012' as datetime)
  union all
  select date+1
  from d
  where date < '10/15/2012'
  )
select t.ID, d.date CDate, isnull(t.val, 0) val
from d
left join temp t
       on t.CDate = d.date
order by d.date
OPTION (MAXRECURSION 0) -- use this if your dates are >99 days apart

You need to make up the dates, so I've use a recursive common table expression here.
SQL Fiddle

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a nonnegative
integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is
not specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query
execution, the query is ended and an error is returned.

SQL - SHOW ALL DATES between two dates

Similar to @DhruvJoshi's answer but using a recursive CTE to generate the dates instead:

DECLARE @MinDate DATE = '20170424',
        @MaxDate DATE = '20170430';

WITH allDates AS
(
    SELECT @MinDate AS dates

    UNION ALL

    SELECT DATEADD(DAY, 1, ad.[dates] )
    FROM allDates AS ad
    WHERE ad.[dates] < @MaxDate
)

SELECT 
        ISNULL([MessageType].[Name],0) AS [Channel],
        dates AS [Time],
        COUNT([MessageType].[Name]) AS [Count]
FROM 
(
   SELECT dates
   FROM allDates
) AS T
LEFT JOIN 
@table1 ON T.dates=CONVERT(VARCHAR(11), @table1.[OccuredAtUtc], 106)
        LEFT JOIN @table2 ON ... = ...
GROUP BY dates,
         [MessageType].[Name]
ORDER BY [Time] ASC

Show all dates between, even if no result

You can build out an automated result set using MySQL variables for all the dates you want.

select
      AllDaysYouWant.MyJoinDate,
      count( U.User_ID ) as NumberJoined
   from
      ( select
              @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
           from
              ( select @curDate := '2012-11-21' ) sqlvars,
              Users
           limit 18 ) AllDaysYouWant
      LEFT JOIN Users U
         on AllDaysYouWant.MyJoinDate = U.User_JoinDate
   group by
      AllDaysYouWant.MyJoinDate

The inner query, I am just joining to the users table with no key, so its just used to cycle through X number of records to represent the day span you want... This could be 30, 100, whatever.... Just as long a the table (in this case users), has as many records as you are expecting.

THEN, that result of nothing but days is joined to the users table, but this time, based on the JOIN_DATE of the user. The simple COUNT() should get you what you want.

The "AllDaysYouWant" is the alias assigned to the internal first part query of

  ( select
          @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
       from
          ( select @curDate := '2012-11-21' ) sqlvars,
          Users
       limit 18 ) AllDaysYouWant

This basically states... From the users table (but could be any), give me 18 rows of data (via limit, but could be almost any number of records, but you only need from Nov 22 to Dec 6, which is only 14 days, but I did 18 just for principle it could be almost anything. Above the Users table is (select @curDate := '2012-11-21' ) sqlvars. Any select statement within a query that is wrapped in parentheses as a table source must be given an alias and since it is just a variable I'll be using, don't care what its name is. So, this query starts the variable at Nov 21 and the Select @curDate := Date_Add...blah blah states to take the current value of @curDate, add 1 day to it (now becomes Nov 22) and store it in the returned row "MyJoinDate". So now, this inner query creates your table of just dates going from Nov 22 forward 18 days worth of data and has the alias "AllDaysYouWant" for the rest of the query to reference.

I've adjusted the query which was probably what you encountered, to alias.field everything for clarification...

How to show a row for the dates not in records of a table as zero

The general way to deal with this type of problem is to use something called a calendar table. This calendar table contains all the dates which you want to appear in your report. We can create a crude one by using a subquery:

SELECT
    t1.date,
    COUNT(DISTINCT t2.file_col) AS d_cnt,
    COUNT(t2.file_col) AS cnt
FROM
(
    SELECT '2018-06-01' AS date UNION ALL
    SELECT '2018-06-02' UNION ALL
    ...
) t1
LEFT JOIN tab1 t2
    ON t1.date = t2.date_col
WHERE
    t1.date BETWEEN 'date1' and 'date2'
GROUP BY
    t1.date;

Critical here is that we left join the calendar table to your table containing the actual data, but we count a column in your data table. This means that zero would be reported for any day not having matching data.

Get data between two dates in laravel, if data does not exist it should return 0 as count

Wrote something fast, should print the wanted result

  $period = CarbonPeriod::create('2018-06-14', '2018-06-20');
    foreach ($period as $date)
    {
       $arr[] = DB::table('table_name')->selectRaw('date, count(user_id)')->where('date', $date)->get()->toArray();
    }
    dd($arr);

Fill in missing dates with 0 (zero) in Pandas

You can do it like this:

(I did it with a smaller timeframe so change the date so that it fits you.)

import pandas as pd

x = {"date":["3/1/16","5/1/16","5/1/16"],
    "score":[4,5,6]}

df = pd.DataFrame.from_dict(x)
df["date"] = pd.to_datetime(df["date"], format='%d/%m/%y')
df.set_index("date",inplace=True)

dtr =pd.date_range('01.01.2016', '01.10.2016', freq='D')
s = pd.Series(index=dtr)
df = pd.concat([df,s[~s.index.isin(df.index)]]).sort_index()
df = df.drop([0],axis=1).fillna(0)

print(df)

Output

            score
2016-01-01    0.0
2016-01-02    0.0
2016-01-03    4.0
2016-01-04    0.0
2016-01-05    5.0
2016-01-05    6.0
2016-01-06    0.0
2016-01-07    0.0
2016-01-08    0.0
2016-01-09    0.0
2016-01-10    0.0

With file

Because you ask in the comment here an example with file:

df = pd.read_csv('myfile.csv', index_col=0)
df.index = pd.to_datetime(df.index, format='%d/%m/%y')

dtr =pd.date_range('01.01.2016', '01.10.2016', freq='D')
s = pd.Series(index=dtr)
df = pd.concat([df,s[~s.index.isin(df.index)]]).sort_index()
df = df.drop([0],axis=1).fillna(0)

df.to_csv('missingDateCorrected.csv', encoding ='utf-8', index =True)

---

Related Topics