This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Daily Change Report Date Range

I'm currently trying to set up a report that goes out daily that summarizes the changes made over the past day. Ideally I'd like this report to go out each day at 5pm, and contain only the changes over the past 24 hours, so from 5pm the previous day, until 5pm the current day.

Within the setup for the change report, I can set it to "Show me changes over the past x days." When I try to set this to 1, rather than the past 24 hours, it seems to include any changes made at all the previous day, as well as anything made that day. This causes any changes to show up in the report two days in a row. I can set the value to 0, and it will include changes made only the current day, so from 12:00am until the report goes out at 5pm. This is better as information isn't duplicated on consecutive days. However, it also means any changes that may be made after hours between 5:00 and midnight will not be caught in either report.

Now, at this point, the best solution I've found is to run the report at 11:59 each night, set to the past 0 days. This includes all changes made that day, and nothing is duplicated. However, it also means that we don't receive the report until the next morning.

Is there a setting I'm missing somewhere that would give me finer grained control over the range included in the change report?

  • I think you'd have to do a custom SQL report with all sorts of fun date stuff...like:

    This SQL chunk gets rows where datetime field is between the last hour:15min - last hour:29 min.

    ( DateTime between
        dateadd (MI, 15, dateadd (hh, datepart(HOUR, getdate())-1, convert(datetime, floor( convert(float,getdate()) )) ))
        AND
        dateadd (MI, 29, dateadd (hh, datepart(hour, getdate())-1, convert(datetime, floor( convert(float,getdate()) )) ))
     )

    the convert section zero's out the time stamp for today's date.

    The middle dateadd section takes the current hour and subtracts one

    the beginning dateadd takes the current minute and assigns it 15

    search SQL help for day (which I think is d or dd). 

    This gives you yesterday @ 17:00
    select 
        dateadd(d, -1,
        dateadd (hh, 17,
            convert(datetime, floor( convert(float,getdate()) ) ))
        )

    This gives you today @ 17:00
    select 
        dateadd (hh, 17,
            convert(datetime, floor( convert(float,getdate()) ) ))

    Hope that helps

  • Thanks, I figured it would probably involve writing a custom query. Would it be possible to make this a feature request for future version, adding finer control to the dates and times used in change reports? I see it useful for a situation like this, but also in a troubleshooting situation. Say there was some odd behavior that suddenly popped up on the network, this could provide a quick way to see all the changes that occurred in the hour or two hours prior.