Transform Data / Duplicate Records on the Fly
-
We're building an ASP.NET page to display timeline data for multiple records. Each record has a start date and an end date. Due to lack of space on the page, the time line has been compressed so that the dates will be grouped by Month. The end result will be that records will be displayed under the Month in which they occur with a time line showing start date and end date. The real challenge is that we need a record to repeat if it overlaps the end of the month.
So is it possible to write a SQL Statement that will take a record that overlaps a month, truncate it and then duplicate it. If the original record has a Start and End Date of 8/27 and 9/10, the result set should have two records. One will a Start/End of 8/27 - 8/31 with the second have a Start/End of 9/1 - 9/10.
In the same cases below, Test3 and Test6 should be repeated. Test3 because it overlaps 1 month. Test6 because it overlaps 2 months.
I can think of one approach in ASP.NET via the page, but I'm really curious if its possible to return the records as needed out of SQL Server. Not to mention that the approach seems a bit bulky.
Maybe a table-value function that handles duplicating the record if needed? (Hmmm....Maybe this isn't as hard as it sounds.)
This is a link to a mockup showing the idea behind the final product.
USE [Sandbox]
GO
/****** Object: Table [dbo].[EventDates] Script Date: 09/08/2010 21:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventDates](
[EventNumber] [varchar](8) NOT NULL,
[NameLong] [varchar](50) NOT NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[Code]
INSERT INTO [EventDates]
([EventNumber],[NameLong],[StartDate],[EndDate])
SELECT '0001', 'Test1','8/15/2010','8/25/2010'
GO
INSERT INTO [EventDates]
([EventNumber],[NameLong],[StartDate],[EndDate])
SELECT '0001', 'Test2','8/27/2010','8/31/2010'
GO
INSERT INTO [EventDates]
([EventNumber],[NameLong],[StartDate],[EndDate])
SELECT '0001', 'Test3','8/27/2010','9/10/2010'
GO
INSERT INTO [EventDates]
([EventNumber],[NameLong],[StartDate],[EndDate])
SELECT '0001', 'Test4','9/1/2010','9/7/2010'
GO
INSERT INTO [EventDates]
([EventNumber],[NameLong],[StartDate],[EndDate])
SELECT '0001', 'Test5','9/15/2010','9/25/2010'
GO
INSERT INTO [EventDates]
([EventNumber],[NameLong],[StartDate],[EndDate])
SELECT '0001', 'Test6','7/15/2010','9/25/2010'
GO
[/code]
-
Its now a moot point as I've realized the better way to approach it on the ASP.NET side. However, I'm still curious about it for academic reasons.
-
The best way is to use a Tally or Date table and join to it where the date's between your date ranges to expand the date ranges into individual days. Then you can just group by the month - Could you give the output you'd expect for the data you've provided above and I'll have a go?
-
HowardW (9/9/2010)
The best way is to use a Tally or Date table and join to it where the date's between your date ranges to expand the date ranges into individual days. Then you can just group by the month - Could you give the output you'd expect for the data you've provided above and I'll have a go?Actually, expanding to individual days would cause a lot of overhead. There's a better way but you're definitely on the right track.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
david.c.holley (9/9/2010)
Its now a moot point as I've realized the better way to approach it on the ASP.NET side. However, I'm still curious about it for academic reasons.I'd love to see that approach... can you post it, please? I'll post a T-SQL/Tally solution in a couple of minutes either way...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's an easy way to do it in T-SQL. You could also pivot the result dynamically if you absolutely needed to. If you don't already have one or don't know how it works, see the following article about the Tally table.
http://www.sqlservercentral.com/articles/T-SQL/62867/
If you need to do a dynamic CROSS TAB, please see the following article...
http://www.sqlservercentral.com/articles/Crosstab/65048/
Here's the solution to your immediate problem. Yes... it does use a Tally table. You also need to change the table name for events because I use Temp Tables to keep such demos safe for everyone.
--===== Create some test data. This is NOT a part of the solution.IF OBJECT_ID('TempDB..#EventDates','U') IS NOT NULL
DROP TABLE #EventDates
;
CREATE TABLE #EventDates
(
EventNumber VARCHAR(8) NOT NULL,
NameLong VARCHAR(50) NOT NULL,
StartDate DATETIME NULL,
EndDate DATETIME NULL
)
;
INSERT INTO #EventDates
(EventNumber,NameLong,StartDate,EndDate)
SELECT '0001', 'Test1','8/15/2010','8/25/2010' UNION ALL
SELECT '0001', 'Test2','8/27/2010','8/31/2010' UNION ALL
SELECT '0001', 'Test3','8/27/2010','9/10/2010' UNION ALL
SELECT '0001', 'Test4','9/1/2010' ,'9/7/2010' UNION ALL
SELECT '0001', 'Test5','9/15/2010','9/25/2010' UNION ALL
SELECT '0001', 'Test6','7/15/2010','9/25/2010' UNION ALL
SELECT '0001', 'XtremeTest','06/15/2010','06/20/2012'
;
--===== Solve the problem using the Pseudo Cursor magic of the Tally Table and
-- a CROSS JOIN that looks like an INNER JOIN to fool DBA's
SELECT e.EventNumber,
e.NameLong,
OriginalStartDate = e.StartDate,
OriginalEndDate = e.EndDate,
PeriodStartDate = CASE
WHEN DATEADD(mm,DATEDIFF(mm,0,e.StartDate)+(t.N-1),0) <= e.StartDate
THEN StartDate
ELSE DATEADD(mm,DATEDIFF(mm,0,e.StartDate)+(t.N-1),0)
END,
PeriodEndDate = CASE
WHEN DATEADD(DAY,-1,DATEADD(mm,DATEDIFF(mm,0,e.StartDate)+t.N,0)) > e.EndDate
THEN EndDate
ELSE DATEADD(DAY,-1,DATEADD(mm,DATEDIFF(mm,0,StartDate)+t.N,0))
END
FROM #EventDates e
INNER JOIN dbo.Tally t
ON t.N BETWEEN 1 AND DATEDIFF(mm, e.StartDate, e.EndDate)+1
ORDER BY e.EventNumber, e.NameLong, PeriodStartDate, t.N
;
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/9/2010)
Actually, expanding to individual days would cause a lot of overhead. There's a better way but you're definitely on the right track.
Good point - didn't consider just expanding months rather than days. Good solution.
-
The underlying goal is NOT to expand the number of days - taking a start date of 9/1 with an end date of 9/15 and creating records for 9/2 - 9/14. That's being handled graphically via ASP.NET. The challenge is that whilte ASP.NET page is already grouping the records by month, a record that overlaps more than 1 month needs to be duplicated so that it also appears under the following month.
The following record should be split into two. So that the ASP.NET page can place the same Event under both months.
Original
Event 1 9/15/2010 10/10/2010
Result
Event 1 9/15/2010 9/30/2010
Event 1 10/1/2010 10/10/2010
Display (Actual display is via blocks filled in under the approprate day of the month. See the original post for an image.)
September
Event 1 9/15/2010 9/30/2010
October
Event 1 10/1/2010 10/10/2010
So if I have a record with a Start Date/End Date of 9/20-10/15. It needs to be duplicated with the Start and End Dates massaged so that the ASP.NET will render it under September and October.
Despite the fact that I really should work on some Grad School stuff tonight, I'm now intellectually curious about this.
I'm thinking a table-valued function that takes the Start and End Dates as a parameter, inserts records into a table in memory and then spits out the result as a table.
http://s285.photobucket.com/albums/ll48/chyron7/Mockups/?action=view¤t=timelinemockup.jpg
-
HowardW (9/9/2010)
Jeff Moden (9/9/2010)
Actually, expanding to individual days would cause a lot of overhead. There's a better way but you're definitely on the right track.
Good point - didn't consider just expanding months rather than days. Good solution.
Thanks for the feedback, Howard.
I'm the same way... I tend to think in "days" and my code suffers from it sometimes. I got lucky this time. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
david.c.holley (9/9/2010)
The underlying goal is NOT to expand the number of days - taking a start date of 9/1 with an end date of 9/15 and creating records for 9/2 - 9/14. That's being handled graphically via ASP.NET. The challenge is that whilte ASP.NET page is already grouping the records by month, a record that overlaps more than 1 month needs to be duplicated so that it also appears under the following month.The following record should be split into two. So that the ASP.NET page can place the same Event under both months.
Original
Event 1 9/15/2010 10/10/2010
Result
Event 1 9/15/2010 9/30/2010
Event 1 10/1/2010 10/10/2010
Display (Actual display is via blocks filled in under the approprate day of the month. See the original post for an image.)
September
Event 1 9/15/2010 9/30/2010
October
Event 1 10/1/2010 10/10/2010
So if I have a record with a Start Date/End Date of 9/20-10/15. It needs to be duplicated with the Start and End Dates massaged so that the ASP.NET will render it under September and October.
Despite the fact that I really should work on some Grad School stuff tonight, I'm now intellectually curious about this.
I'm thinking a table-valued function that takes the Start and End Dates as a parameter, inserts records into a table in memory and then spits out the result as a table.
Heh... it's obvious you've not run my code. My code DOESN'T expand all the days. It does precisely as you asked. Run it and see. If the start date and end date were 9/13 and 11/14, only 3 rows would be returned 9/13-30, 10/1-10/31, and 11/1-11/14.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/9/2010)
david.c.holley (9/9/2010)
The underlying goal is NOT to expand the number of days - taking a start date of 9/1 with an end date of 9/15 and creating records for 9/2 - 9/14. That's being handled graphically via ASP.NET. The challenge is that whilte ASP.NET page is already grouping the records by month, a record that overlaps more than 1 month needs to be duplicated so that it also appears under the following month.The following record should be split into two. So that the ASP.NET page can place the same Event under both months.
Original
Event 1 9/15/2010 10/10/2010
Result
Event 1 9/15/2010 9/30/2010
Event 1 10/1/2010 10/10/2010
Display (Actual display is via blocks filled in under the approprate day of the month. See the original post for an image.)
September
Event 1 9/15/2010 9/30/2010
October
Event 1 10/1/2010 10/10/2010
So if I have a record with a Start Date/End Date of 9/20-10/15. It needs to be duplicated with the Start and End Dates massaged so that the ASP.NET will render it under September and October.
Despite the fact that I really should work on some Grad School stuff tonight, I'm now intellectually curious about this.
I'm thinking a table-valued function that takes the Start and End Dates as a parameter, inserts records into a table in memory and then spits out the result as a table.
Heh... it's obvious you've not run my code. My code DOESN'T expand all the days. It does precisely as you asked. Run it and see. If the start date and end date were 9/13 and 11/14, only 3 rows would be returned 9/13-30, 10/1-10/31, and 11/1-11/14.
Darn, I was trying to keep it a secret that I didn't run your sample. Actually, I think that I missed it. At any rate, while I will certainly try it and shamelessly use it in my nefarious plans, I think that I'm going to play around myself to see what I can come up with purely for academic reasons. My curiosity is related to repeatedly preforming an action without using a cursor when the number of iterations can not be predicted.
-
I just ran across your article on Tally Tables and I don't know if I'm more surprised over the technique or that I nearly, mostly, sorta comprehend it. Its the sort of weird twisted why the hell would you ever do that, let alone come up that, idea that I'm known for. I am impressed.
-
david.c.holley (9/9/2010)
I just ran across your article on Tally Tables and I don't know if I'm more surprised over the technique or that I nearly, mostly, sorta comprehend it. Its the sort of weird twisted why the hell would you ever do that, let alone come up that, idea that I'm known for. I am impressed.http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/quote%5D
Heh... yep... I agree. "Wierd twisted why the hell would you ever do that" keeps the cursors away. Once you get used to it (ie: permanently "twisted" into the set-based way of thinking in columns), it won't seem so wierd anymore.
Thanks for the feedback. If you have any more questions about the Tally table, I might just be twisted enough to answer them for you. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I just came to the stunning realization that I could greatly streamline the overall performance by using the tally table to determine the color of the individual cells instead of having the code-behind do it in a loop.
The intent is to have something like this as the final results.
Currently, there's a loop in the code-behind that creates the individual cells and if the index of the cell falls within the date range, the cell color is changed. If the datasource were to provide the color of the cell, the cells could exist as a part of the markup with the background-color of the cell bound to the column in the underlying datasource as in
<td id="TD_1" style="background-color:<%#Eval("TD_1_BackColor")%></td>
<td id="TD_2" style="background-color:<%#Eval("TD_2_BackColor")%></td>
<td id="TD_3" style="background-color:<%#Eval("TD_3_BackColor")%></td>
This should speed up processing the page and make it much, much easier to maintain it as the code-behind is really ugly at this point.
-
david.c.holley (9/19/2010)
I just came to the stunning realization that I could greatly streamline the overall performance by using the tally table to determine the color of the individual cells instead of having the code-behind do it in a loop.Very cool realization. Do you need help with it or do you have it for now?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply