Jan 08 2020 05:45 AM
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!
Jan 09 2020 12:15 PM
SolutionHi @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
Jan 16 2020 08:07 PM
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
Jan 09 2020 12:15 PM
SolutionHi @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