CODEWITHSUNDEEP
sql-servercount
johnwonderbread
johnwonderbread

Reputation: 97

Count Days Where Record Not Recorded SQL

I am trying to write a query that does the following:

This is the sample data

sample data .

The final output should look like this: 

|  Key  |  Count of Missed Data  |
|  14   |          123           |
|  124  |          356           |

Where count of missed data is the number of days that a 'device id' did not record 'data' during the last 365 days.

**Note: there could be 500 'device id' per 'key'. I need to count each time one of these devices does not record 'data' on a calendar day during the course of a year and sum the total number of 'missed data' points grouped by key.

Please ask if you have any questions. Thank you for your assistance!

John

Per suggestions below, this is the code I am now running. Critiques?

Select 
a.descr AS 'Community',
a.meter_type AS 'Meter Type',
sum(a.misseddaysperdevice) as [Count of Missed Days]
From
    (
    Select 
        fmr.subdivisionkey, 
        sub.descr,
        meter_type,
        365-count(distinct(convert(varchar, fmr.read_date, 112))) as misseddaysperdevice
    From 
        FactMeterRead fmr
        INNER JOIN DimSubdivision sub on sub.subdivisionkey = fmr.subdivisionkey
    Where 
        fmr.read_date > getdate()-364
    Group By 
        fmr.SubdivisionKey, sub.descr, fmr.meter_num, meter_type
    ) a 
Group By 
    a.descr, meter_type
Order By 
    [Count of Missed Days] DESC

Upvotes: 1

Views: 243

Answers (3)

JBrooks
JBrooks

Reputation: 10013

I don't like the hard coding 365 days.... problems 1/4th of the time...

         declare @asOfDate date = getdate()
         declare @start date
         set @start = dateadd(year, -1,  @asOfDate)

         select sumDevice.[Key], sum(sumDevice.MissingDaysCount) as MissingDaysCount
         from ( 
            select mt.[Key], 
            mt.[device id],
            datediff(day, @start, @asOfDate) - isnull(count(distinct cast(mt.[Date] as date)), 0) 
                                             as [MissingDaysCount]
            from myTable mt
            where mt.[date] between @start and dateadd(day, 1, @asOfDate)
            group by mt.[key],
                     mt.[device id]) as SummaryKey
         group by sumDevice.[Key]   
         order by sumDevice.[Key]

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

Something like this should do it:

select key, 365 - count(distinct(cast(date as date))) as [Count of Missed Data]
from MyTable
where date > getdate() - 365
group by key

Edit: To sum the counts of missed days for all devices for a given key, try this:

select key, sum(MissedDaysPerDevice) as [Count of Missed Data]
from (
    select key, 365 - count(distinct(cast(date as date))) as MissedDaysPerDevice
    from MyTable
    where date > getdate() - 365
    group by key, device
) a
group by key

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Well, the number of days missed is the 365 - number of days present. That is easier to calculate, so:

select key, count(distinct cast(date as date)) as days_present,
       365 - count(distinct cast(date as date)) as days_absent
from t
where date >= dateadd(day, -364, cast(getdate() as date))
group by key;

Upvotes: 1

Related Questions