CODEWITHSUNDEEP
sqlsql-server
Ivan Cézanne
Ivan Cézanne

Reputation: 78

Get "zero" for a count at dates without records

So I'm counting activity records from users in my system. I get the activity counter for each day in a certain month and year, just like the query that follows

SELECT CONVERT(date, VIS_DATETIME) AS DATETIME, COUNT(*) AS ACTIVITY
FROM ACTIVITY
WHERE DATEPART(year, VIS_DATETIME) = 2016 AND
      DATEPART(month, VIS_DATETIME) = 3
GROUP BY CONVERT(date, VIS_DATETIME)
ORDER BY CONVERT(date, VIS_DATETIME)

The question is, if, let's say, March 28th doesn't have any activity, it won't be even listed. But, for my charts API, I need to get that listed and with a 0 for the counter.

Obviously, accepting suggestions!

Upvotes: 2

Views: 5120

Answers (4)

Ben wood
Ben wood

Reputation: 21

Similar to Arun's answer this will create a temp Table you can join on and save you creating a permanent table in database. 

DECLARE @@startDate DATETIME = '2016-03-01';
DECLARE @@endDate DATETIME = '2016-03-31';

DECLARE @@tempCalendar TABLE (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Year] INT NOT NULL,
    [Month] INT NOT NULL,
    [Day] INT NOT NULL
);

DECLARE @@dateCount DATETIME = @@startDate;

WHILE (@@dateCount <= @@endDate)
    BEGIN
        INSERT INTO @@tempCalendar 
        VALUES (
            DATEPART(YEAR, @@dateCount)
            , DATEPART(MONTH, @@dateCount)
            , DATEPART(DAY, @@dateCount)
        );

        SET @@dateCount = DATEADD(DAY, 1, @@dateCount);
    END


SELECT c.[Year]
    , c.[Month]
    , c.[Day]
    , COUNT(a.Id) AS ACTIVITY

FROM @@tempCalendar c
    LEFT OUTER JOIN ACTIVITY a ON c.[Year] = DATEPART(YEAR, a.VIS_DATETIME)
                                AND c.[Month] = DATEPART(MONTH, a.VIS_DATETIME)
                                AND c.[Day] = DATEPART(DAY, a.VIS_DATETIME)

WHERE DATEPART(YEAR, a.VIS_DATETIME) = DATEPART(YEAR, @@startDate) 
    AND DATEPART(MONTH, a.VIS_DATETIME) = DATEPART(MONTH, @@startDate) 

GROUP BY c.[Year], c.[Month], c.[Day]
ORDER BY c.[Year], c.[Month], c.[Day];

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Hmmm, if you want innovative. Here are two steps:

  • Add a flag to the table about whether the activity is valid
  • Add a job to SQL Server agent

How do these work?

alter table activity add column ActivityIsValid smallint default 1;

That way, new rows go in as a valid activity, not invalid.

Then schedule a SQL Server agent job to run the following code once per day:

insert into activity(vis_datetime, ActivityIsValid)
    values(cast(getdate() as date), 0);

Then you can run your query as:

SELECT CONVERT(date, VIS_DATETIME) AS DATETIME, 
       SUM(CASE WHEN ActivityIsValid = 1 THEN 1 ELSE 0 END) AS ACTIVITY
FROM ACTIVITY
WHERE VIS_DATETIME >= '2016-03-01' AND
      VIS_DATETIME < '2016-04-01        
GROUP BY CONVERT(date, VIS_DATETIME)
ORDER BY CONVERT(date, VIS_DATETIME);

Of course, this only works moving forward. You can manually insert the extra rows in the historical data.

Also note the change to the WHERE clause. By doing direct date comparisons, the SQL engine can make use of an index.

Upvotes: 0

Cs 8128
Cs 8128

Reputation: 113

Declare @DayOfMonth TinyInt Set @DayOfMonth = 1
Declare @Month TinyInt Set @Month = 1
Declare @Year Integer Set @Year = 2016
Declare @startDate datetime 
Declare @endDate datetime 
-- ------------------------------------
Select  @startDate = DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0)))

select @endDate  = dateadd(month,1,@startDate)

;with dateRange as
(
  select dt = dateadd(dd, 0, @startDate)
  where dateadd(dd, 0, @startDate) < @endDate
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) < @endDate
)
select *
from dateRange

The above query gives all the dates in the month,you can left join it with your aggregated query to get the entries with zero count.

Upvotes: 4

wvdz
wvdz

Reputation: 16641

Create a table that contains all dates. Then do a left join with the Activity table. Group on the date, and do a COUNT on Activity.id. The left join ensures that all dates from the date table are included in the result set, even if they are not matched in the join clause.

Upvotes: 4

Related Questions