Create table by transforming values from string patterns
-
Hi All
I'm hoping someone can help me with a job I'm trying to complete but I have to confess I'm not confident I can articulate! It is in two parts, manipulating a text string to derive a date, and secondly needing a routine to cycle through theses strings and creating rows in a new table...here goes;
I have a sql table with 3 fields, an ID, StartDate, and Weekpattern.
something like (date formats may be different)
'1001','03/01/2011','11 111 1'
'1002','03/01/2011','11 1 1 1'
I need a procedure to create a new table which reads this week pattern and where the text value is 1 create a new row. The new row will have the original ID, and a single date, this date field is derived by looking at the length of the week pattern and by determining the position of the '1' being looked at and referencing it with the start date (each new 1 adds one week to the start date, NULLS mean nothing that week). This will be repeated for the next 1 in the sequence and a new row created etc.
e.g. the first row in the above example becomes
1001, 03/01/2011
1001, 10/01/2011
1001, 24/01/2011
1001, 31/01/2011
1001, 07/02/2011
1001, 21/02/2011
as you can see the nulls in the string haven't created new rows
Please tell me this makes some sense and one of you SQL whizzes knows how to do this 😀
I've tried reading about transformation packages, and programmable arrays etc but my knowledge is minimal of anything other than the most basic of SQL queries.
And my apologies to the American readership for my date formats 😉
Cheers
Les
-
WOW! That is a crazy thing to deal with. Just for the record you can't have "nulls" inside of a string. You are talking about spaces and not nulls. VERY different animals. That column of week pattern has to be one of the strangest things I have ever seen and I would strongly recommend making that more normalized data soon (if possible) or you are going to be fighting that for the life of the system. There may be a more set based way to solve but you really don't have anything in your data that resembles sets so I came up with a painful RBAR method that will work. I make no promises about the performance (except that it WILL be very slow on large datasets). You should test this on your actual data to see how horribly slow it will be before you try this in production. And of course you should read through this and understand what it is doing because it will be you at 3am that has to support it. Maybe somebody else will hop on here that can come up with a way to use a tally table for parsing this but I couldn't figure it out.
--setup data
create table #MyCrazyDateThing
(
ID int,
StartDate datetime,
WeekPattern varchar(25)
)
set dateformat dmy
insert #MyCrazyDateThing select 1001, '3/1/2011', '11 111 1'
insert #MyCrazyDateThing select 1002, '3/1/2011', '11 1 1 1'
--step 1 create new table to hold Crazy Data and Business Requirements
create table #MyCrazyOutput
(
ID int,
StartDate datetime
)
--step 2 begin completely ridiculous parsing of horrible data
declare @Len int
declare @CurrentID int
declare @CurrentStartDate datetime
declare @CurrentWeekPattern varchar(25)
declare @CurrentDate datetime
declare @CurPos int = 1
declare WeekList cursor for
select ID, StartDate, WeekPattern from #MyCrazyDateThing
open WeekList
fetch next from WeekList into @CurrentID, @CurrentStartDate, @CurrentWeekPattern
while @@FETCH_STATUS = 0 begin
select @Len = DATALENGTH(@CurrentWeekPattern)
set @CurrentDate = @CurrentStartDate
while @CurPos <= @Len begin
if (select isnumeric(SUBSTRING(@CurrentWeekPattern, @CurPos, 1))) = 1
begin
Insert #MyCrazyOutput
select @CurrentID, DATEADD(ww, case when @CurPos = 1 then 0 else cast(SUBSTRING(@CurrentWeekPattern, @CurPos, 1) as int) end, @CurrentDate)
set @CurrentDate = DATEADD(ww, case when @CurPos = 1 then 0 else cast(SUBSTRING(@CurrentWeekPattern, @CurPos, 1) as int) end, @CurrentDate)
end
else
set @CurrentDate = DATEADD(ww, 1, @CurrentDate)
set @CurPos = @CurPos + 1
end
set @CurPos = 1
fetch next from WeekList into @CurrentID, @CurrentStartDate, @CurrentWeekPattern
end
close WeekList
deallocate WeekList
select * from #MyCrazyOutput
drop table #MyCrazyDateThing
drop table #MyCrazyOutput
_______________________________________________________________
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/ -
Another way, this one using a tally table
SELECT t.ID,DATEADD(wk,n.N-1,t.StartDate) AS NewStartDate
FROM #MyCrazyDateThing t
INNER JOIN dbo.Tally n ON n.N BETWEEN 1 AND LEN(t.WeekPattern)
AND SUBSTRING(t.WeekPattern,n.N,1)='1'
See here for information on building a tally table
http://www.sqlservercentral.com/articles/T-SQL/62867/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I knew somebody with more active brain cells than I can put together today would hop in. Thanks!! Definitely use the tally approach. It is WAY better than the garbage I came up with. :blush:
You can read up on the tally table here[/url].
_______________________________________________________________
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/ -
Sean Lange (8/10/2011)
I knew somebody with more active brain cells than I can put together today would hop in. Thanks!! Definitely use the tally approach. It is WAY better than the garbage I came up with. :blush:Ta for the feedback.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thank you very much Gentlemen I really appreciate your efforts.
Mark, I'll chase down the article on creating a Tally table (and keep fingers crossed it is easy enough for an idiot like me to follow!).
Sean, about the format of this weekpattern, you are absolutely right! I'm actually moving FROM a system which has each 'event' with its own row rather than this weekpattern method and hence the question - I decided I'd have no chance of reporting on the data unless I could convert it into something I'm more familiar with.
Hopefully will get a chance to do this tomorrow and report back 😎
Thanks again
Les
-
Mark-101232 (8/10/2011)
Another way, this one using a tally tableSELECT t.ID,DATEADD(wk,n.N-1,t.StartDate) AS NewStartDate
FROM #MyCrazyDateThing t
INNER JOIN dbo.Tally n ON n.N BETWEEN 1 AND LEN(t.WeekPattern)
AND SUBSTRING(t.WeekPattern,n.N,1)='1'
See here for information on building a tally table
Nicely done, Mark.
--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) -
Chaps, that worked beautifully, the tally table seems ingenious in it's simplicity - there will be other uses for that I'm sure!
I did think of one small addition - but I'm just being cheeky since I may be able to work it out if I browse the forum a little and it's not crucial (so feel free to ignore) - but the new table might also benefit for having two other dates, the first day of week monday, and first day of week sunday relative to the Newstartdate 😀
Once again, thank you.
Les
-
l.danks (8/11/2011)
I'm just being cheeky since I may be able to work it out if I browse the forum a little and it's not crucial (so feel free to ignore) - but the new table might also benefit for having two other dates, the first day of week monday, and first day of week sunday relative to the NewstartdateNot cheeky at all.
It's not uncommon to have some sort of "date" table with things like week starts, month starts, month numbers, holidays, etc all encoded. Many of those you can calculate (using datepart(), datename(), dateadd(), datediff()) but it becomes trickier when you have to deal like things with financial or rating periods. So having a table with all of that stuff pre-calculated can save a lot of time.
Also, for some reason, date arithmetic seems to be one of the craziest things I've seen done (even though SQL Server provides some awesome functions for doing it). So have a date table also eliminates a lot of crazy code.
Just don't call it "date"... that's now a SQL type in SQL Server (a handy one too!)
-
You won't believe how complicated (or maybe bloated) this neat little query has become - which leads me to ask further questions, particularly to help performance..
I've found the dateadd method to find the First Day of Week (of the above returned NewStartdate) - which is kind of wrapped within it (I'm not at work at the moment but I'll update this post with actual code tomorrow) - is this an economic way of doing this OR would I be better off adding another join, from the NewStartDate to a pre-populated Calendar table? Perhaps it would be better to ignore Start of Week altogether and just make a join to the view on the fly at runtime?
I've also added union all and repeated the same select statements above but looking for different characters (rather than just 1s) - I'd always thought of unions for getting data from different tables rather than combining multiple selects with slightly different clauses - does this seem an ok approach?
And finally..
This query, for now, is running in a non-indexed View - and I can't help but think it would be quicker if I had a stored procedure to create and update a new table, indexed, with this data (which will end up at about half a million rows during a year) - but I'm not certain how to create a table from multiple selects combined by union statements.
You can see my little query has become a bit of a project. And even though I'm learning a lot searching forums etc - It makes much more sense when you guys have already shown me the way 😛
Anywho, I'll post my query in the morning and hopefully someone can tell me how to make a table from it and/or optimise it a little.
Les
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply