how to generate dates from sql query in a set of 4

Question

Post reply

how to generate dates from sql query in a set of 4

Bhavesh-1094084

SSC Eights!

Points: 865
More actions
October 9, 2013 at 5:31 am

#278837

have following query in pl/sql which returns me 6000 dates
SELECT to_date('03-Nov-2009') + (LEVEL - 1) datecol FROM dual CONNECT BY LEVEL <= 6000
03/11/2009
04/11/2009 ...
I want to populate another column in the same query to show results as follows
03/11/2009 1
04/11/2009 1
05/11/2009 1
06/11/2009 1
07/11/2009 2
08/11/2009 2
09/11/2009 2
10/11/2009 2
11/11/2009 3
12/11/2009 3
13/11/2009 3
14/11/2009 3 . .
not sure how I could achieve this grouping set in sql server. Any ideas?
Bhavesh
.NET and SQL Server Blog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Here you go:

SELECT TOP 25

RowNumber= number+1

,DateField= CONVERT(DATE,DATEADD(dd,number,'2009-11-09'))

,DateGroup= (number/4)+1

FROM master.dbo.spt_values

WHERE type = 'P';

I used the system table spt_values to get a list of numbers, but there are other methods to generate a tally table.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 2

Koen Verbeeck (10/9/2013)
Here you go:
SELECT TOP 25
RowNumber= number+1
,DateField= CONVERT(DATE,DATEADD(dd,number,'2009-11-09'))
,DateGroup= (number/4)+1
FROM master.dbo.spt_values
WHERE type = 'P';
I used the system table spt_values to get a list of numbers, but there are other methods to generate a tally table.

Great thanks... following achieved in pl/sql

SELECT to_date('03-Nov-2009') + (LEVEL - 1) datecol,

ceil(LEVEL / 4) gr

FROM dual

CONNECT BY LEVEL <= 6000

Bhavesh
.NET and SQL Server Blog

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Koen's query produces a better query plan than what I came up with. That said, this is a good example of how to use NTILE for this type of thing...

DECLARE @start_date date = '1/1/1990';

DECLARE @rows int=6000;

WITH cteTally(n) AS

(SELECT TOP (@rows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM master..spt_values a CROSS APPLY master..spt_values b)

SELECT TOP (@rows)

RowNumber=n,

DATEADD(DAY,n,@start_date) AS DateField,

NTILE(@rows/4) OVER (ORDER BY (SELECT NULL)) AS DateGroup

FROM cteTally

FYI. You can use tally tables[/url] in Oracle and analytic functions (AKA "Windows functions" in Microsoft world) are supported in pl/sql.

Edit: Code cleanup

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

Some might prefer in-line Tally tables.

DECLARE @start_date DATE = '1990-01-01';

DECLARE @rows INT = 6000;

WITH Tally (n) AS

(

SELECT 0 UNION ALL

SELECT TOP (@rows-1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM (VALUES ($),($),($),($),($),($),($),($),($),($),($),($)) a(n1)

CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) b(n2)

CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) c(n3)

CROSS JOIN (VALUES ($),($),($),($),($)) d(n4)

)

SELECT DateField=DATEADD(DAY,n,@start_date), DateGroup=1+n/4

FROM Tally;

Note that mine is zero-based so that it returns @start_date in the first row.

I also like the nice, symmetric quality of my Tally - easy on the eyes. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St