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

lookup table for minutes of a day Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 4:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
Does anyone know if a website or have a query that generates a table with a row for each minute of the day starting 2 years ago and going 10 years into future?

Need it to have values in one minute increments (1440 rows/minutes for each day):
2013-10-07 04:32
2013-10-07 04:33
2013-10-07 04:34
2013-10-07 04:35
2013-10-07 04:36
2013-10-07 04:37

Thanks!
Post #1502401
Posted Monday, October 7, 2013 5:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:22 PM
Points: 3,325, Visits: 7,174
Using a Tally Table (or in this case a CTE) it's really easy to do it.
Here's an example:
WITH e1(n) AS(
SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(n) AS(
SELECT e1.n FROM e1, e1 x
),
e4(n) AS(
SELECT e2.n FROM e2, e2 x
),
e8(n) AS(
SELECT e4.n FROM e4, e4 x
),
cteTally(n) AS(
SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM e8
),
Test(n) AS(
SELECT DATEADD( minute, n, DATEADD( YEAR, -2, GETDATE()))
FROM cteTally)
SELECT MIN(n), MAX(n)
FROM Test
WHERE n <= DATEADD( YEAR, 10, GETDATE())

It won't give you the exact table you need but at least you'll have to analyze it to use it. If you have questions, feel free to ask.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502417
Posted Monday, October 7, 2013 5:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
well, this gives me a table with one row containing two values:
2011-10-07 16:11:56.343 2023-10-04 16:14:56.343


I changed to Select *.

Thanks for the answer with the bonus mini - puzzle Luis. I thought I wouldn't be able to do it but I did!

WITH e1(min) AS(
SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(min) AS(
SELECT e1.min FROM e1, e1 x
),
e4(min) AS(
SELECT e2.min FROM e2, e2 x
),
e8(min) AS(
SELECT e4.min FROM e4, e4 x
),
cteTally(min) AS(
SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM e8
),
Test(min) AS(
SELECT DATEADD( minute, min, DATEADD( YEAR, -2, GETDATE()))
FROM cteTally)
SELECT *
FROM Test
WHERE min <= DATEADD( YEAR, 10, GETDATE())

Post #1502420
Posted Monday, October 7, 2013 5:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
for bonus bonus, can you show me how to get just the date and minutes? eg. 2011-10-20 21:45
Post #1502423
Posted Monday, October 7, 2013 5:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:22 PM
Points: 3,325, Visits: 7,174
If you wan't for display, you can convert to string using convert.
To remove seconds from the datetime, you can do something different.
Either you specify the exact time to start or you can calculate it.

SELECT DATEADD( MINUTE, DATEDIFF( MINUTE, 0, DATEADD( YEAR, -2, GETDATE())), 0)

By the way, you can remove a CTE, I just added to show the ranges without showing all the lines.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502425
Posted Monday, October 7, 2013 6:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
I am making a permanent table. But I need the datetime without the zeros on end.
just
2009-10-20 21:45

not
2009-10-20 21:45:00.000
Post #1502431
Posted Monday, October 7, 2013 11:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,609, Visits: 5,221
polkadot (10/7/2013)
I am making a permanent table. But I need the datetime without the zeros on end.
just
2009-10-20 21:45

not
2009-10-20 21:45:00.000


A DATETIME data type will always have those zeroes on the end.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1502464
Posted Tuesday, October 8, 2013 12:54 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
Hi,

You can try with help of replace and convert functions

REPLACE(CONVERT(CHAR(16),DATEADD( MINUTE, DATEDIFF( MINUTE, 0, DATEADD( YEAR, -2, GETDATE())), 0), 126), 'T', ' ')

Post #1502474
Posted Tuesday, October 8, 2013 2:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
parulprabu, Thanks for the help.

Post #1502496
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse