CODEWITHSUNDEEP
sqlsql-server
rafacardosoc
rafacardosoc

Reputation: 251

Query to check number of records created in a month.

My table creates a new record with timestamp daily when an integration is successful. I am trying to create a query that would check (preferably automated) the number of days in a month vs number of records in the table within a time frame.

For example, January has 31 days, so i would like to know how many days in january my process was not successful. If the number of records is less than 31, than i know the job failed 31 - x times.

I tried the following but was not getting very far: 

SELECT COUNT (DISTINCT CompleteDate)
FROM table
WHERE CompleteDate BETWEEN '01/01/2015' AND '01/31/2015'

Every 7 days the system executes the job twice, so i get two records on the same day, but i am trying to determine the number of days that nothing happened (failures), so i assume some truncation of the date field is needed?!

Upvotes: 0

Views: 877

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT DATEDIFF(day, t.d, dateadd(month, 1, t.d)) - COUNT(DISTINCT CompleteDate)
FROM mytable
CROSS APPLY (SELECT CAST(YEAR(CompleteDate) AS VARCHAR(4)) +
                    RIGHT('0' + CAST(MONTH(CompleteDate) AS VARCHAR(2)), 2) + 
                    '01') t(d)
GROUP BY t.d

SQL Fiddle Demo

Explanation:

  1. The value CROSS APPLY-ied, i.e. t.d, is the ANSI string of the first day of the month of CompleteDate, e.g. '20150101' for 12/01/2015, or 18/01/2015.
  2. DATEDIFF uses the above mentioned value, i.e. t.d, in order to calculate the number of days of the month that CompleteDate belongs to.
  3. GROUP BY essentially groups by (Year, Month), hence COUNT(DISTINCT CompleteDate) returns the number of distinct records per month.
  4. The values returned by the query are the differences of [2] - 1, i.e. the number of failures per month, for each (Year, Month) of your initial data.

If you want to query a specific Year, Month then just simply add a WHERE clause to the above:

WHERE YEAR(CompleteDate) = 2015 AND MONTH(CompleteDate) = 1

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

Between is not safe here 

SELECT 31 - count(distinct(convert(date, CompleteDate)))
FROM table
WHERE CompleteDate >= '01/01/2015' AND CompleteDate < '02/01/2015'

Upvotes: 0

jpw
jpw

Reputation: 44891

One way to do this is to use a calendar/date table as the main source of dates in the range and left join with that and count the number of null values.

In absence of a proper date table you can generate a range of dates using a number sequence like the one found in the master..spt_values table:

select count(*) failed 
from (
     select dateadd(day, number, '2015-01-01') date 
     from master..spt_values where type='P' and number < 365
) a
left join your_table b on a.date = b.CompleteDate
where b.CompleteDate is null
and a.date BETWEEN '01/01/2015' AND '01/31/2015'

Sample SQL Fiddle (with count grouped by month)

Upvotes: 1

Malk
Malk

Reputation: 11983

Assuming you have an Integers table*. This query will pull all dates where no record is found in the target table: 

declare @StartDate datetime = '01/01/2013',
        @EndDate datetime = '12/31/2013'

;with d as  (
    select *, date = dateadd(d, i - 1 , @StartDate) 
    from dbo.Integers
    where i <= datediff(d, @StartDate, @EndDate) + 1
)
select d.date 
from d
where not exists (
    select 1 from <target> t 
    where DATEADD(dd, DATEDIFF(dd, 0, t.<timestamp>), 0) = DATEADD(dd, DATEDIFF(dd, 0, d.date), 0)
)

Upvotes: 0

Related Questions