March 14, 2014 at 12:34 pm
I've run into a brain teaser (at least to me) and I'm hoping for some forum help.
Simply put, I'm trying to modify a date range, but it's much more complicated.
Let's say I have existing date ranges of:
BlockIDStartDateEndDateActive
182013-12-31 00:00:002013-12-31 00:00:001
192014-01-01 00:00:002014-01-23 00:00:001
202014-01-24 00:00:002014-01-25 00:00:001
212014-01-26 00:00:002014-02-04 00:00:001
222014-02-05 00:00:002014-02-13 00:00:001
232014-02-14 00:00:002014-02-15 00:00:001
I've created code that will do the following (depending on the inputs):
1) Create new start and end dates
2) Inactivate obsolete date ranges
March 14, 2014 at 12:38 pm
Mark Derryberry (3/14/2014)
I've run into a brain teaser (at least to me) and I'm hoping for some forum help.Simply put, I'm trying to modify a date range, but it's much more complicated.
Let's say I have existing date ranges of:
BlockIDStartDateEndDateActive
182013-12-31 00:00:002013-12-31 00:00:001
192014-01-01 00:00:002014-01-23 00:00:001
202014-01-24 00:00:002014-01-25 00:00:001
212014-01-26 00:00:002014-02-04 00:00:001
222014-02-05 00:00:002014-02-13 00:00:001
232014-02-14 00:00:002014-02-15 00:00:001
I've created code that will do the following (depending on the inputs):
1) Create new start and end dates
2) Inactivate obsolete date ranges
Can you post ddl and consumable sample data? It would also help if you could explain what you want to do. Please take a few minutes and the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 1:17 pm
Sorry, I wasn't finished and didn't mean to hit post. I'll follow-up with the full question...
March 14, 2014 at 1:19 pm
Sorry if this layout is complicated. I'm a little naïve when it comes to posting code into forums.
Let's say I have existing date ranges of:
BlockID StartDate EndDate Active
18 2013-12-31 00:00:00 2013-12-31 00:00:00 1
19 2014-01-01 00:00:00 2014-01-23 00:00:00 1
20 2014-01-24 00:00:00 2014-01-25 00:00:00 1
21 2014-01-26 00:00:00 2014-02-04 00:00:00 1
22 2014-02-05 00:00:00 2014-02-13 00:00:00 1
23 2014-02-14 00:00:00 2014-02-15 00:00:00 1
I've created code that will do the following (depending on the inputs):
1) Create new start and end dates
2) Inactivate obsolete date ranges
It can successfully do that in the following situations (Using the date range of 1/26/2014 to 2/4/2014 as an example)
Note: the last row in each set is the newly created date range.
Begin date < BlockStartDate and stop date < BlockEndDate
BlockID BlockStartDate BlockEndDate NewStartDate NewEndDate NewActive
202014-01-24 00:00:00.0002014-01-25 00:00:00.0002014-01-24 00:00:00.0002014-01-24 00:00:00.0001
212014-01-26 00:00:00.0002014-02-04 00:00:00.0002014-02-03 00:00:00.0002014-02-04 00:00:00.0001
222014-01-25 00:00:00.0002014-02-02 00:00:00.0002014-01-25 00:00:00.0002014-02-02 00:00:00.0001
Begin date > BlockStartDate and stop date > BlockEndDate
BlockID BlockStartDate BlockEndDate NewStartDate NewEndDate NewActive
212014-01-26 00:00:00.0002014-02-04 00:00:00.0002014-01-26 00:00:00.0002014-01-26 00:00:00.0001
222014-02-05 00:00:00.0002014-02-13 00:00:00.0002014-02-08 00:00:00.0002014-02-13 00:00:00.0001
232014-01-27 00:00:00.0002014-02-07 00:00:00.0002014-01-27 00:00:00.0002014-02-07 00:00:00.0001
Begin date < BlockStartDate and stop date > BlockEndDate
BlockID BlockStartDate BlockEndDate NewStartDate NewEndDate NewActive
202014-01-24 00:00:00.0002014-01-25 00:00:00.0002014-01-24 00:00:00.0002014-01-24 00:00:00.0001
212014-01-26 00:00:00.0002014-02-04 00:00:00.000NULL 2014-02-04 00:00:00.0000
222014-02-05 00:00:00.0002014-02-13 00:00:00.0002014-02-08 00:00:00.0002014-02-13 00:00:00.0001
232014-01-25 00:00:00.0002014-02-07 00:00:00.0002014-01-25 00:00:00.0002014-02-07 00:00:00.0001
The problem is when the begin date > BlockStartDate and the stop date < BlockEndDate. The issue is because there should now be two date ranges (no counting the newly formed range).
BlockID BlockStartDate BlockEndDate NewStartDate NewEndDate NewActive
212014-01-26 00:00:002014-02-04 00:00:002014-01-26 00:00:002014-01-27 00:00:001
222014-01-28 00:00:002014-02-02 00:00:002014-01-28 00:00:002014-02-02 00:00:001
Using the above examples what I want to get is:
BlockID BlockStartDate BlockEndDate NewStartDate NewEndDate NewActive
212014-01-26 00:00:002014-02-04 00:00:002014-01-26 00:00:002014-01-27 00:00:001
222014-01-26 00:00:002014-02-04 00:00:002014-02-03 00:00:002014-02-04 00:00:001
232014-01-28 00:00:002014-02-02 00:00:002014-01-28 00:00:002014-02-02 00:00:001
Here is the table schema:
CREATE TABLE [dbo].[R2_AvailabilityBlock](
[AvailBlockID] [bigint] IDENTITY(1,1) NOT NULL,
[VendorProductID] [int] NOT NULL,
[BlockStartDate] [datetime] NOT NULL,
[BlockEndDate] [datetime] NOT NULL,
[RoomCount] [smallint] NOT NULL,
[Active] [bit] NOT NULL,
CONSTRAINT [PK_R2_AvailabilityBlock] PRIMARY KEY CLUSTERED
(
[AvailBlockID] 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
ALTER TABLE [dbo].[R2_AvailabilityBlock] WITH CHECK ADD CONSTRAINT [FK_R2_AvailabilityBlock_R2_VendorProduct] FOREIGN KEY([VendorProductID])
REFERENCES [dbo].[R2_VendorProduct] ([VendorProductID])
GO
ALTER TABLE [dbo].[R2_AvailabilityBlock] CHECK CONSTRAINT [FK_R2_AvailabilityBlock_R2_VendorProduct]
INSERT INTO R2_AvailabilityBlock VALUES (11, '2013-12-31 00:00:00.000', '2013-12-31 00:00:00.000', 30, 1)
INSERT INTO R2_AvailabilityBlock VALUES (11, '2014-01-01 00:00:00.000', '2014-01-23 00:00:00.000', 40, 1)
INSERT INTO R2_AvailabilityBlock VALUES (11, '2014-01-24 00:00:00.000', '2014-01-25 00:00:00.000', 0, 1)
INSERT INTO R2_AvailabilityBlock VALUES (11, '2014-01-26 00:00:00.000', '2014-02-04 00:00:00.000', 40, 1)
INSERT INTO R2_AvailabilityBlock VALUES (11, '2014-02-05 00:00:00.000', '2014-02-13 00:00:00.000', 20, 1)
INSERT INTO R2_AvailabilityBlock VALUES (11, '2014-02-14 00:00:00.000', '2014-02-15 00:00:00.000', 40, 1)
This is the code I'm using:
DROP TABLE #TEST
DECLARE
@VendorProductINT,
@StartDateDATETIME,
@EndDateDATETIME
SET @VendorProduct = 11
SET @StartDate = '1/28/2014'
SET @EndDate = '2/2/2014'
SELECT AB.AvailBlockID AS BlockID, BlockStartDate, BlockEndDate
, CASE WHEN @StartDate <= BlockStartDate AND @EndDate < BlockEndDate THEN DATEADD(dd, 1, @EndDate)
WHEN @StartDate > BlockStartDate THEN BlockStartDate
END AS NewStartDate,
CASE WHEN @StartDate = BlockStartDate AND @EndDate < BlockEndDate THEN BlockEndDate
WHEN @StartDate > BlockStartDate AND @EndDate >= BlockEndDate THEN DATEADD(dd, -1, @StartDate)
WHEN @StartDate >= BlockStartDate AND @EndDate < BlockEndDate THEN DATEADD(dd, -1, @StartDate)
WHEN @StartDate <= BlockStartDate THEN BlockEndDate
END AS NewEndDate,
CASE WHEN @StartDate <= BlockStartDate AND @EndDate >= BlockEndDate THEN 0 ELSE 1 END AS NewActive
INTO #TEST
FROM R2_AvailabilityBlock AB
WHERE AB.VendorProductID = @VendorProduct
AND ((@StartDate BETWEEN BlockStartDate AND BlockEndDate OR @EndDate BETWEEN BlockStartDate AND BlockEndDate)
OR (@StartDate < BlockStartDate AND @EndDate > BlockEndDate))
ORDER BY AB.AvailBlockID
INSERT INTO #TEST
VALUES (@StartDate, @EndDate, @StartDate, @EndDate, 1)
SELECT * FROM #TEST ORDER BY BlockID
So, after a small crash course in editing, here is the final result. It should be readable now. Also, I added the code to build the table being referenced in the query.
Keep in mind this is all test code so don't be concerned with its current formatting.
I hope this makes sense to everyone.
March 14, 2014 at 1:47 pm
Mark Derryberry (3/14/2014)
Sorry if this layout is complicated. I'm a little naïve when it comes to posting code into forums.
Not to worry, we were all first timers at one point. 😉
The best possible format for posting data is insert statements instead of just typing it in. As you have discovered it does not keep your layout very well.
Also, your code references other tables that we don't have. We can help but without all the information it just isn't possible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 2:42 pm
I'll study up on how to use inserts and edit the post.
The referenced table is nothing more than a larger table (with a few more columns) of the first grid of data I provided. I'll provide a small example when I figure out how to use inserts.
Thanks for trying to review that gobbly-gook! 🙂
March 14, 2014 at 3:19 pm
Ok, I've edited the post so it's more readable and understandable. Also, I added the code to build the table being referenced in the query as I brushed up on forum etiquette. 🙂
March 18, 2014 at 11:01 am
I figured out how to get it to work myself. Probably not the most elegant, but it might help someone.
DECLARE
@VendorProductINT,
@StartDateDATETIME,
@EndDateDATETIME,
@RoomCountINT
-- We inactivate all current date ranges affected by the impending update
UPDATE R2_AvailabilityBlock
SET Active = 0
WHERE VendorProductID = @VendorProduct
AND ((@StartDate BETWEEN BlockStartDate AND BlockEndDate OR @EndDate BETWEEN BlockStartDate AND BlockEndDate)
OR (@StartDate < BlockStartDate AND @EndDate > BlockEndDate))
-- Now we insert the new date ranges that are still active
INSERT INTO R2_AvailabilityBlock
SELECT @VendorProduct,
CASE WHEN @StartDate <= BlockStartDate AND @EndDate < BlockEndDate THEN DATEADD(dd, 1, @EndDate)
WHEN @StartDate > BlockStartDate THEN BlockStartDate
END AS BlockStartDate
, CASE WHEN @StartDate = BlockStartDate AND @EndDate < BlockEndDate THEN BlockEndDate
WHEN @StartDate > BlockStartDate AND @EndDate >= BlockEndDate THEN DATEADD(dd, -1, @StartDate)
WHEN @StartDate >= BlockStartDate AND @EndDate < BlockEndDate THEN DATEADD(dd, -1, @StartDate)
WHEN @StartDate <= BlockStartDate THEN BlockEndDate
END AS BlockEndDate
, RoomCount
, CASE WHEN @StartDate <= BlockStartDate AND @EndDate >= BlockEndDate THEN 0 ELSE 1 END AS Active
FROM R2_AvailabilityBlock
WHERE VendorProductID = @VendorProduct
AND ((@StartDate BETWEEN BlockStartDate AND BlockEndDate OR @EndDate BETWEEN BlockStartDate AND BlockEndDate)
OR (@StartDate < BlockStartDate AND @EndDate > BlockEndDate))
AND CASE WHEN @StartDate <= BlockStartDate AND @EndDate >= BlockEndDate THEN 0 ELSE 1 END = 1
-- This is to insert date ranges where there are now two date ranges to replace one
-- ex: New start date is greater than existing start date and new end date is earlier than existing end date
INSERT INTO R2_AvailabilityBlock
SELECT @VendorProduct, DATEADD(dd, 1, @EndDate) AS NewStartDate, BlockEndDate AS NewEndDate, RoomCount, 1 AS NewActive
FROM R2_AvailabilityBlock
WHERE VendorProductId = @VendorProduct
AND @StartDate > BlockStartDate
AND @EndDate < BlockEndDate
-- Now we insert the new date range
INSERT INTO R2_AvailabilityBlock
VALUES (@VendorProduct, @StartDate, @EndDate, @RoomCount, 1)
March 19, 2014 at 7:04 pm
:hehe: I like brain teasers! 😛
Both yours and mine look a bit nasty but I think you can do this with a single MERGE statement:
DECLARE @VendorProductINT = 11
,@StartDateDATETIME = '1/25/2014'
,@EndDateDATETIME = '2/2/2014'
,@RoomCountINT = 15;
WITH DataToUpdate (VendorProduct, StartDate, EndDate, RoomCount, Active) AS
(
SELECT @VendorProduct, @StartDate, @EndDate, @RoomCount, 1
),
DeactivatedBlocks AS
(
SELECT AvailBlockID, VendorProductID, BlockStartDate, BlockEndDate, a.RoomCount
,Active=CASE WHEN b.VendorProduct IS NULL THEN a.Active ELSE 0 END
,StartDate, EndDate
FROM R2_AvailabilityBlock a
LEFT JOIN DataToUpdate b
ON a.VendorProductID = b.VendorProduct AND
(b.StartDate BETWEEN a.BlockStartDate AND a.BlockEndDate OR
b.EndDate BETWEEN a.BlockStartDate AND a.BlockEndDate)
),
FinalSourceData AS
(
SELECT AvailBlockID, VendorProductID, BlockStartDate, BlockEndDate, RoomCount, Active
FROM DeactivatedBlocks
UNION ALL
SELECT NULL, VendorProductID
,BlockStartDate=CASE WHEN rn1=1 THEN BlockStartDate ELSE EndDate + 1 END
,BlockEndDate=CASE WHEN rn2=1 THEN BlockEndDate ELSE StartDate-1 END
,RoomCount, Active
FROM
(
SELECT rn2=ROW_NUMBER() OVER (PARTITION BY VendorProductID ORDER BY BlockStartDate DESC)
,rn1=ROW_NUMBER() OVER (PARTITION BY VendorProductID ORDER BY BlockStartDate)
,BlockStartDate, BlockEndDate, StartDate, EndDate
,VendorProductID
,RoomCount, Active=1
FROM DeactivatedBlocks a
WHERE Active = 0
) a
WHERE 1 IN (rn1, rn2)
UNION ALL
SELECT NULL, VendorProduct, StartDate, EndDate, RoomCount, Active
FROM DataToUpdate
)
MERGE R2_AvailabilityBlock t
USING FinalSourceData s
ON s.AvailBlockID = t.AvailBlockID
WHEN MATCHED THEN
UPDATE SET Active = s.Active
WHEN NOT MATCHED THEN
INSERT (VendorProductID, BlockStartDate, BlockEndDate, RoomCount, Active)
VALUES (s.VendorProductID, s.BlockStartDate, s.BlockEndDate, s.RoomCount, s.Active);
Edit: Note that I changed your start date to overlap 2 blocks to make sure mine worked for that case. And if I were you, I'd test the crap out of mine. Not just to figure out how it works (you definitely will want to do that) but also to make sure it handles block overlaps properly (I did it slightly differently to you).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply