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 12:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
can you convert that into a CTE or insert statemetns, the way i did?

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 #1540393
Posted Tuesday, February 11, 2014 12:48 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 4,356, Visits: 6,191
LOVE the pic Lowell!! Your code rocks too, btw.

Oh, and I would like to request that the OP makes whomever designed that system to drop and give me 20!!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1540404
Posted Tuesday, February 11, 2014 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 21, Visits: 115
Like this ?

WITH CTE(reference,StartDate,Pattern,PatternLength) AS 
(
SELECT 'TestRef-AA',convert(datetime,'01/01/2013 10:45'),' 1111 111 11','15'
SELECT 'TestRef-AA',convert(datetime,'10/11/2013 09:00'),'11 11 111 ','12'
SELECT 'TestRef-AA',convert(datetime,'09/09/2013 13:00'),'11111 11 ','9'
SELECT 'TestRef-AA',convert(datetime,'02/08/2013 12:00'),'1 11 1 ','11'
)

Post #1540425
Posted Tuesday, February 11, 2014 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
good start Knives;
so here's my same query, with your updated data in it;

so if you run this and look at the data, does it produce the desired results?

code]
;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,PatternLength) AS
(
SELECT 'TestRef-AA',convert(datetime,'01/01/2013 10:45'),' 1111 111 11','15' UNION ALL
SELECT 'TestRef-BB',convert(datetime,'10/11/2013 09:00'),'11 11 111 ','12' UNION ALL
SELECT 'TestRef-CC',convert(datetime,'09/09/2013 13:00'),'11111 11 ','9' UNION ALL
SELECT 'TestRef-DD',convert(datetime,'02/08/2013 12:00'),'1 11 1 ','11'
)
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'
order by reference, N

[/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 #1540448
Posted Tuesday, February 11, 2014 2:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 21, Visits: 115

Thanks Lowell, I'll have a look first thing.The more I think about it, the more I think it'll work. Thanks, your a legend
Post #1540451
Posted Thursday, February 13, 2014 3:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 21, Visits: 115
Hi lowell

I've tested the code and it worked beautifully. I need a little help though, I got the logic wrong when I explained the issue originally. There should never be any instance of the pattern not starting with a 1;however, the very first row generated needs to be the very first instance of the start date, not the start date with 7 adds added. I have tried to get around this by slightly altering the case statement to replace the first row date with the actual start date but then the second row date that is generated doesn't use the replace start date from the first row but the original, meaning that it's out by a week.

Any ideas?

Here's the code I've changed

;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)

,

TestCTE(reference,StartDate,Pattern,PatternLength) AS

(

SELECT 'VideoGames-S1',convert(datetime,'01/01/2013 10:45'),'11 111 111111  1111','18' --Course 13NAPUSD-B2

)

 

SELECT

                                'StartTime' =

                                CASE

                                WHEN Tally.N = 1 THEN [startdate]

                                WHEN SUBSTRING([pattern],N,1) = '1'  THEN DATEADD(wk,Tally.N,[startdate]) ELSE NULL 

                                END,

                                TestStartDate_DATEADD = DATEADD(wk,Tally.N,[startdate])

                                ,*

                                , SUBSTRING([pattern],N,1)

                               

FROM

                                TestCTE testcte

CROSS JOIN Tally tally

 

WHERE

                                N <= LEN([pattern])

--AND SUBSTRING([pattern],N,1) = '1'

 

ORDER BY

                                reference, N


Post #1541094
Posted Thursday, February 13, 2014 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
knives the fix is rediculously easy;
the Tally table needs to start at zero instead of one.

so this tiny subsection changes:
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM THOUSANDS)
--change to
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 FROM THOUSANDS)




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 #1541147
Posted Thursday, February 13, 2014 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 21, Visits: 115
Hi lowell

I've managed to sort it now, figured it out about 2 mins after posting. Having a blond day today.

In the THEN clause I just -7 after the calculation. This moves all predicted dates back 7 days.

Sorry for not updating post, only just had a spare minute.

Thanks for the help, it's appreciated
Post #1541163
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse