Counting Days

[Tim Hunter]

I have an SQL table that stores company incidents, so I have a Microsoft Flow that will send out an email with the count of days since the last incident. So basically, I want to know how many days it has been since the last row was entered into the SQL table. Is there a simple way to do that or is this a complex request? Thank you very much!

[#2 · Accepted solution]

Hi @Tim Hunter -- If your Incidents table contains a date field, you could use a query similar to the one below to calculate the number of days since the last incident was recorded.  Thanks.

DECLARE @LastIncidentDate date = (SELECT MAX(IncidentDate) AS LastIncident FROM Incidents)
DECLARE @CurrentDate date = (GETDATE())

SELECT DATEDIFF(day, @LastIncidentDate, @CurrentDate) AS DaysWithoutAnIncident

[#3]

@Tim Hunter 

If my understanding is correct, you would like to know how many days have incidents in your incident table because maybe multiple incidents might occurred at the same day, right? If so, please try this SQL statement as below:

Select count(distinct incidentdate) from incident (nolock)
Where Year(incidentdate) = 2019

You can change the “where” condition to fit your requirement. In addition, please be sure your incidentdate field is Date datatype, if it isn’t, covert it via CONVERT function. You can find out the CONVERT samples via google. Hope my comment is useful for you.

Regards, 

Sten