Calculating days elapsed between incidents
Something you might have noticed in the prior two exercises is that we don't always have incidents on every day of the week, so calling LAG() and LEAD() the "prior day" is a little misleading; it's really the "prior period." Someone in management noticed this as well and, at the end of July, wanted to know the number of days between incidents. To do this, we will calculate two values: the number of days since the prior incident and the number of days until the next incident.
Recall that DATEDIFF() gives the difference between two dates. We can combine this with LAG() and LEAD() to get our results.
This is a part of the course
“Time Series Analysis in SQL Server”
Exercise instructions
- Calculate the days since the last incident using a combination of
DATEDIFF()andLAG()orLEAD(). - Calculate the days until the next incident using a combination of
DATEDIFF()andLAG()orLEAD(). - NOTE: you will not need to use the
NumberOfIncidentscolumn in this exercise.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
ir.IncidentDate,
ir.IncidentTypeID,
-- Fill in the days since last incident
___(DAY, ___(ir.IncidentDate, 1) OVER (
PARTITION BY ir.IncidentTypeID
ORDER BY ir.IncidentDate
), ir.___) AS DaysSinceLastIncident,
-- Fill in the days until next incident
___(DAY, ir.___, ___(ir.IncidentDate, 1) OVER (
PARTITION BY ir.IncidentTypeID
ORDER BY ir.IncidentDate
)) AS DaysUntilNextIncident
FROM dbo.IncidentRollup ir
WHERE
ir.IncidentDate >= '2019-07-02'
AND ir.IncidentDate <= '2019-07-31'
AND ir.IncidentTypeID IN (1, 2)
ORDER BY
ir.IncidentTypeID,
ir.IncidentDate;This exercise is part of the course
Time Series Analysis in SQL Server
Explore ways to work with date and time data in SQL Server for time series analysis
In this chapter, we will learn how to use window functions to perform calculations over time, including calculating running totals and moving averages, calculating intervals, and finding the maximum levels of overlap.
Exercise 1: Using aggregation functions over windowsExercise 2: Contrasting ROW_NUMBER(), RANK(), and DENSE_RANK()Exercise 3: Aggregate window functionsExercise 4: Calculating running totals and moving averagesExercise 5: Running totals with SUM()Exercise 6: Investigating window framesExercise 7: Calculating moving averagesExercise 8: Working with LAG() and LEAD()Exercise 9: Seeing prior and future periodsExercise 10: Seeing the prior three periodsExercise 11: Calculating days elapsed between incidentsExercise 12: Finding maximum levels of overlapExercise 13: Analyze client data for potential fraudExercise 14: Build a stream of eventsExercise 15: Complete the fraud analysisExercise 16: Wrapping upWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.