January 28, 2015 at 1:57 pm
I have not used the Lead/Lag functions before, but I would like to use it for a client who wishes to maintain a calendar of start and end dates where the End Date ends one second before the next start date.
I think lag and lead are capable of doing this (any examples are welcome and appreciated).
My question is, if the client were to update the start date of a row, would the previous end date be updated automatically?
January 28, 2015 at 3:23 pm
There's a fundamental flaw in thinking this out on the part of the client. There's a whole lot that can happen in the 1 second that ends up missing by using this method.
I strongly recommend that the EndDate be the next start date and that you use temporal criteria based on a "closed" (inclusive) StartDate and an "open" (exclusive) EndDate. That would allow criteria like...
WHERE @somedatetime >= StartDate AND @somedatetime < EndDate
... which is also SARGABLE (will do a proper Index Seek) and guaranteed to not miss any time at all. It would also make it MUCH easier to "auto-magically" update the previous or next row depending on if a StartDate or EndDate were updated.
If you want help there, post the CREATE TABLE statement along with the keys, constraints, and any indexes there are on the table so that one or more of us can help you with a coded solution. If you can't post the actual CREATE TABLE, then make one up with the pertinent columns in it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2015 at 3:30 pm
I understand what you are saying. Do you feel the same way when it is more exact, such as millisecond (2000-06-11 23:59:59.990)?
Basically I want to make it so that they cannot screw up start and and end dates by leaving gaps between the previous end date and the new start date as they maintain the calendar.
January 28, 2015 at 3:35 pm
Here is the table I am using.
GO
/****** Object: Table [dbo].[tbl_Bucket] Script Date: 28/01/2015 22:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Bucket](
[BucketID] [int] IDENTITY(1,1) NOT NULL,
[BucketTypeID] [int] NOT NULL,
[BucketDescription] [varchar](50) NOT NULL,
[BucketStartDate] [datetime] NOT NULL,
[BucketEndDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_Bucket] PRIMARY KEY NONCLUSTERED
(
[BucketID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [CK_tbl_Bucket_BucketTypeID_BucketDescription] UNIQUE NONCLUSTERED
(
[BucketTypeID] ASC,
[BucketDescription] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Bucket] WITH NOCHECK ADD CONSTRAINT [tbl_BucketType_tbl_Bucket_FK1] FOREIGN KEY([BucketTypeID])
REFERENCES [dbo].[tbl_BucketType] ([BucketTypeID])
GO
ALTER TABLE [dbo].[tbl_Bucket] CHECK CONSTRAINT [tbl_BucketType_tbl_Bucket_FK1]
GO
January 28, 2015 at 3:44 pm
bjames60689 (1/28/2015)
I understand what you are saying. Do you feel the same way when it is more exact, such as millisecond (2000-06-11 23:59:59.990)?Basically I want to make it so that they cannot screw up start and and end dates by leaving gaps between the previous end date and the new start date as they maintain the calendar.
You don't want to allow gaps but you are creating a system that HAS gaps!! Makes no sense. You will also have potential rounding issues if you go with your decimal fractions of seconds.
Do what Jeff said, and move on to actual application building.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2015 at 3:52 pm
bjames60689 (1/28/2015)
I understand what you are saying. Do you feel the same way when it is more exact, such as millisecond (2000-06-11 23:59:59.990)?Basically I want to make it so that they cannot screw up start and and end dates by leaving gaps between the previous end date and the new start date as they maintain the calendar.
If the BucketEndDate is not the BucketStartDate of the temporally next row, then it's a mistake in my opinion even if you get down to the 100-nanosecond range available in the "newer" datatypes. There's no need for it and having even the tiniest gap can (will?) complicate your life and the code immensely.
To automate such changes, I'd use a well written trigger to automate the cascading update. Looking at your indexes, I don't see any on the BucketStartDate or BucketEndDate and you cannot rely on the BucketID because there could be gaps. You'll need to test what indexes would benefit the trigger. I suppose you could use something like Lead/Lag/etc here but haven't played with it well enough to known.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2015 at 3:54 pm
I agree with everything said. Unfortunately I am working within a system that i do not have control of changing.
The reason for the post was to find out more about how Lead and Lag functions work and if they recalculate when a row is updated.
January 28, 2015 at 4:32 pm
bjames60689 (1/28/2015)
I agree with everything said. Unfortunately I am working within a system that i do not have control of changing.The reason for the post was to find out more about how Lead and Lag functions work and if they recalculate when a row is updated.
Understood and I know this isn't on you. If the client needs more information on why it's such a bad thing to have such gaps, let me know and I'll be happy to provide more detail. If it were me (and I know it's not), I'd press on them a bit more to do it right.
Lead/Lag won't do the update themselves. You could use it to find the previous/next row to update, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2015 at 4:38 pm
Thank You for all the help.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply