Home

Counting Days

%3CLINGO-SUB%20id%3D%22lingo-sub-1096557%22%20slang%3D%22en-US%22%3ECounting%20Days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1096557%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20SQL%20table%20that%20stores%20company%20incidents%2C%20so%20I%20have%20a%20Microsoft%20Flow%20that%20will%20send%20out%20an%20email%20with%20the%20count%20of%20days%20since%20the%20last%20incident.%20So%20basically%2C%20I%20want%20to%20know%20how%20many%20days%20it%20has%20been%20since%20the%20last%20row%20was%20entered%20into%20the%20SQL%20table.%20Is%20there%20a%20simple%20way%20to%20do%20that%20or%20is%20this%20a%20complex%20request%3F%20Thank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1096557%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ecount%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etable%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1099903%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1099903%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F238560%22%20target%3D%22_blank%22%3E%40Tim%20Hunter%3C%2FA%3E%26nbsp%3B--%20If%20your%20Incidents%20table%20contains%20a%20date%20field%2C%20you%20could%20use%20a%20query%20similar%20to%20the%20one%20below%20to%20calculate%20the%20number%20of%20days%20since%20the%20last%20incident%20was%20recorded.%26nbsp%3B%20Thanks.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EDECLARE%20%40LastIncidentDate%20date%20%3D%20(SELECT%20MAX(IncidentDate)%20AS%20LastIncident%20FROM%20Incidents)%0ADECLARE%20%40CurrentDate%20date%20%3D%20(GETDATE())%0A%0ASELECT%20DATEDIFF(day%2C%20%40LastIncidentDate%2C%20%40CurrentDate)%20AS%20DaysWithoutAnIncident%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1114116%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1114116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F238560%22%20target%3D%22_blank%22%3E%40Tim%20Hunter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20my%20understanding%20is%20correct%2C%20you%20would%20like%20to%20know%20how%20many%20days%20have%20incidents%20in%20your%20incident%20table%20because%20maybe%20multiple%20incidents%20might%20occurred%20at%20the%20same%20day%2C%20right%3F%20If%20so%2C%20please%20try%20this%20SQL%20statement%20as%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3ESelect%20count(distinct%20incidentdate)%20from%20incident%20(nolock)%0AWhere%20Year(incidentdate)%20%3D%202019%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20change%20the%20%E2%80%9Cwhere%E2%80%9D%20condition%20to%20fit%20your%20requirement.%20In%20addition%2C%20please%20be%20sure%20your%20incidentdate%20field%20is%20Date%20datatype%2C%20if%20it%20isn%E2%80%99t%2C%20covert%20it%20via%20CONVERT%20function.%20You%20can%20find%20out%20the%20CONVERT%20samples%20via%20google.%20Hope%20my%20comment%20is%20useful%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%26nbsp%3B%3C%2FP%3E%3CP%3ESten%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

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 Replies
Highlighted

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
Highlighted

@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

Related Conversations