If your priority is speed of selects, the following approach allows for extremely fast selects. Instead of storing a period, you want to store two events (period start and period end). Change column is 1 when the period begins, and is -1 when the period ends. If more than one event occurs on the same day, they must have different EventNumberPerDay. RunningTotal is the number of open peroids after the event has happened:
CREATE TABLE dbo.Events(
PeriodId INT NOT NULL,
Change SMALLINT NOT NULL,
ChangeDate DATE NOT NULL,
EventNumberPerDay INT NOT NULL,
RunningTotal INT NOT NULL);
GO
INSERT dbo.Events
( PeriodId ,
Change ,
ChangeDate ,
EventNumberPerDay,
RunningTotal
)
-- first period begins
VALUES ( 1 , 1, '20120801', 1, 1),
-- second period begins on the same day
(2, 1, '20120801', 2, 2),
-- third period begins
(3,1,'20120803',1, 3),
-- second period ends on the same day
(2,-1,'20120803',2, 2),
-- fourth period begins
(4,1,'20120804',1,3),
-- fourth period ends
(4,-1,'20120805',1,2),
-- first period ends
(1, -1, '20120808',1, 1),
-- third period ends
(3, -1, '20120809',1, 0);
GO
Also you need a calendar table:
CREATE TABLE dbo.Calendar([Date] DATE NOT NULL);
GO
INSERT INTO dbo.Calendar([Date])
VALUES('20120801'),
('20120802'),
('20120803'),
('20120804'),
('20120805'),
('20120806'),
('20120807'),
('20120808'),
('20120809'),
('20120810'),
('20120811');
Once that is accomplished, then your select is very simple and very fast:
SELECT [Date] ,
coalesce(RunningTotal, 0) AS NumOpenIntervals
FROM dbo.Calendar
OUTER APPLY ( SELECT TOP ( 1 )
RunningTotal
FROM dbo.Events
WHERE ChangeDate <= [Date]
ORDER BY ChangeDate DESC, EventNumberPerDay DESC
) AS t
ORDER BY [Date]
Of course, this query is only correct if the data in Events table is valid. We can use constraints to ensure 100% data integrity. I can explain how if you are interested.
Another alternative is to just load your raw data, your periods, into a client application - your problem is absolutely trivial in C++/C#/Java.
Yet another approach is to use an RDBMS with fast cursors such as Oracle - that will allow you to just write a simple cursor and enjoy good performance, but still not always as good as my first solution.
Assuming that your start date and end date are compared against DlvryDt in DeliveryOrder table, I suggest to use a very low date value for start date and a very high date value for end date in cases where these date variables are null. Please see following query.
SELECT do.OrdrNmbr AS 'Order Number',
mb.BOLNmbr AS 'BOL Number',
do.DlvryDt AS 'Deliver Date',
mh.Text3 AS 'Truck',
do.DlvrdQntty AS 'Gallons Delivered',
ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber
and do.DlvryDt >= ISNULL(@StartDate, '19000101')
and do.DlvryDt < ISNULL(dateadd(day, 1, @EndDate), '21000101');
Best Answer
I think you will be fine using
DATEPART
.Assume the following columns exist, and assume dates and times don't test for daylight saving time.
This will select all the UserIDs from a table where the time is between 1am and 5pm, since January 1st.
Notes:
The
LoginDate > '20160101'
will check for all logins after midnight on the 1st of January 2016.I'm using
>=
and<
instead ofBETWEEN
, because it's more accurate.