Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Function: Cursor or Tally Table; which to use, HELP Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 21, Visits: 107
Hello

I have been given a task which I’m having some trouble which getting my head around the logic for it. I’m unsure if I can use a cursor or a tally table to get the results I need.
Basically I need to create a table function that will take three parameters; Reference, Start Date and, Pattern.
The data currently looks like this

reference	            startdate	         pattern
TestRef-AA 09/09/2013 10:45 1111111 1111111 111 111 111111 1111

And need to look like this

reference	           startdate	                            pattern
TestRef-AA 09/09/2013 10:45 1111111 1111111 111 111 111111 1111
TestRef-AA 16/09/2013 10:45 1111111 1111111 111 111 111111 1111
TestRef-AA 23/09/2013 10:45 1111111 1111111 111 111 111111 1111

This data is on a single row, I need to create multiple rows from this one row. The function needs to loop through the pattern column and create a new line with 7 days added to the previous line start date whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row.

I would do this in an array in VB; however, I don’t believe you can create an array in SQL Server so I think I’ll need a cursor or a tally table? Can anyone help with this, even if it’s pointing me in the right direction? I don’t have a clue where to start


Thanks a lot


Post #1540260
Posted Tuesday, February 11, 2014 9:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
yep , a tally table, combined with a dateadd seems to be what you are after.

I just put together an example, but you'd use your own tally table, and extend out and limit the results as required.

;WITH MyCTE([reference],[startdate],[pattern])
AS
(
SELECT 'TestRef-AA',convert(datetime,'09/09/2013 10:45'),'1111111 1111111 111 111 111111 1111'
)
SELECT DATEADD(wk,Tally.N,[startdate]) As CalcDate,*
FROM MyCTE
CROSS JOIN (SELECT 0 As N UNION ALL SELECT 1 UNION ALL SELECT 2) Tally



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540269
Posted Tuesday, February 11, 2014 9:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 4,342, Visits: 6,147
Lowell, you are a better man than I this morning (or perhaps my caffeine hasn't fully kicked in yet)! Without sample output I can't make heads or tails of what the OP actually wants here.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1540304
Posted Tuesday, February 11, 2014 10:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
well i've only done one piece of it...adding the date;

i have a simple macro i use to dump typical copy/paste data and turn it into a CTE, so coming up with SOMETHING feels better than skipping the question altogether. i try not to pad my post count asking for DDL and sample data too often


the second part i glossed over:
there's a requirement for " whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row."
can be done, but need more than one sample row, and need to know the length of string, whether the preceeding whitespace was relevant, and some more details, but this might get the ball rolling.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540308
Posted Tuesday, February 11, 2014 10:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
took me a bit for this one; this seems to do the whole shebang
;WITH 
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM THOUSANDS)
,
MyCTE ([reference],[startdate],[pattern])
AS (SELECT 'TestRef-AA',convert(datetime,'09/09/2013 10:45'),'1111111 1111111 111 111 111111 1111' )

SELECT CASE WHEN SUBSTRING([pattern],N,1) = '1'
THEN DATEADD(wk,Tally.N,[startdate])
ELSE NULL
END As CalcDate,*,
SUBSTRING([pattern],N,1)
FROM MyCTE
CROSS JOIN Tally
WHERE N <= LEN([pattern])
--AND SUBSTRING([pattern],N,1) = '1'





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540327
Posted Tuesday, February 11, 2014 10:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 1,075, Visits: 6,439
Lowell (2/11/2014)
well i've only done one piece of it...adding the date;

i have a simple macro i use to dump typical copy/paste data and turn it into a CTE, so coming up with SOMETHING feels better than skipping the question altogether. i try not to pad my post count asking for DDL and sample data too often


the second part i glossed over:
there's a requirement for " whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row."
can be done, but need more than one sample row, and need to know the length of string, whether the preceeding whitespace was relevant, and some more details, but this might get the ball rolling.


Brilliant! Thanks for posting this, Lowell. I can see it becoming famous



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1540330
Posted Tuesday, February 11, 2014 11:12 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 4,130, Visits: 3,157
ChrisM@home (2/11/2014)

Brilliant! Thanks for posting this, Lowell. I can see it becoming famous

Agreed. That is truly beautiful.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1540360
Posted Tuesday, February 11, 2014 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 21, Visits: 107
Lowell, JamesM said it, that's a brilliant solution, now that I've seen it, I don't know why I didn't think of it lol

The code you posted I think will do it, but there is one thing I don't think it will do. Its my fault I didn't put the requirement properly. Although you hit the point on the head when asking about white space.

The pattern column does have proceeding white space in it, basically the start date is the first date the event is running, the week pattern then holds the remaining weeks that it runs. The first 3 entries in the pattern column can be white space but the fourth can be a 1, for instance. The code would then need to add a 4 weeks to the last rows not 1.

Any ideas?

Let me know if you need any more information

I really appreciate the help, thanks
Post #1540372
Posted Tuesday, February 11, 2014 11:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 12,903, Visits: 31,966
Knives85 (2/11/2014)
Lowell, JamesM said it, that's a brilliant solution, now that I've seen it, I don't know why I didn't think of it lol

The code you posted I think will do it, but there is one thing I don't think it will do. Its my fault I didn't put the requirement properly. Although you hit the point on the head when asking about white space.

The pattern column does have proceeding white space in it, basically the start date is the first date the event is running, the week pattern then holds the remaining weeks that it runs. The first 3 entries in the pattern column can be white space but the fourth can be a 1, for instance. The code would then need to add a 4 weeks to the last rows not 1.

Any ideas?

Let me know if you need any more information

I really appreciate the help, thanks


can you modify the example i posted with more data, including the preceeding spaces, then?
if you run it without any other changes than the data modification, can you say what is wrong (well what needs to be changed; i wouldn't post wrong code! )


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540377
Posted Tuesday, February 11, 2014 12:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 21, Visits: 107
I didn't mean the code was wrong, just that I didnt post the requirements correctly lol

I have altered the example data I originally posted. The first one has 3 prceeding white space then a 1.

Ref	                      Date	         pattern	              PatternLength
TestRef-AA 1/1/2013 1111111 1111111 20
TestRef-AB 10/11/2013 1 111 1 9
TestRef-AC 3/4/2014 111 111 111 21
TestRef-AD 9/9/2013 11 11 1 19

The correct output would look like this for the example would be

Ref	                     Date	         pattern	                PatternLength
TestRef-AA 1/1/2013 1111111 1111111 20
TestRef-AA 29/01/2013 1111111 1111111 20
TestRef-AA 5/2/2013 1111111 1111111 20
TestRef-AA 12/2/2014 1111111 1111111 20
TestRef-AA 19/02/2013 1111111 1111111 20

Let me know if that's correct or enough

Thanks again
Post #1540391
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse