I need to return a query for a line chart that has user selected bounds based on a datetime where the user selects the start and end date. However, the data is often sparse and throws off the line chart’s display when there is no values close to the start and end of the selected bounds.
For example, if I want to select all the data for 2012 but the first record doesn’t appear until March, then my line chart starts its axis at March.
So basically I need to add the immediately previous and following records to the return set sorted by datetime.
Right now, I’m just using UNION with subselects to make it work, but it seems like this would be a common thing that may be built into SQL?
Here’s an example of the working query, how can I improve on it?
-- SQL Server example
-- Compare between start and end dates
-- Union with previous and next records for bounds
SELECT
change_date as event_date,
value_column
FROM
testing.dbo.change_log
WHERE change_date BETWEEN '2012-01-01 00:00:00:000' AND '2013-01-01 00:00:00:000'
UNION ALL
SELECT * FROM (
SELECT TOP 1 '2013-01-01 00:00:00:000' as event_date, value_column FROM testing.dbo.change_log WHERE change_date > '2013-01-01 00:00:00:000' ORDER BY change_date ASC
) AS next_rec
UNION ALL
SELECT * FROM (
SELECT TOP 1 '2012-01-01 00:00:00:000' as event_date, value_column FROM testing.dbo.change_log WHERE change_date < '2012-01-01 00:00:00:000' ORDER BY change_date DESC
) AS prev_rec
ORDER BY change_date;