May 11, 2016 at 7:46 am
Hi,
Simple little requirement.
Need to display first date of each of the 12 months prior to current date, i.e. "Last Twelve Months" for reporting purposes.
i.e. today is 10th May 2016 so we need this:
2016-05-01 00:00:00.000
2016-04-01 00:00:00.000
2016-03-01 00:00:00.000
2016-02-01 00:00:00.000
2016-01-01 00:00:00.000
2015-12-01 00:00:00.000
2015-11-01 00:00:00.000
2015-10-01 00:00:00.000
2015-09-01 00:00:00.000
2015-08-01 00:00:00.000
2015-07-01 00:00:00.000
2015-06-01 00:00:00.000
I have some possible solutions in the pipeline but feel there's probably a simpler way out there
May 11, 2016 at 7:50 am
DuncEduardo (5/11/2016)
Hi,Simple little requirement.
Need to display first date of each of the 12 months prior to current date, i.e. "Last Twelve Months" for reporting purposes.
i.e. today is 10th May 2016 so we need this:
2016-05-01 00:00:00.000
2016-04-01 00:00:00.000
2016-03-01 00:00:00.000
2016-02-01 00:00:00.000
2016-01-01 00:00:00.000
2015-12-01 00:00:00.000
2015-11-01 00:00:00.000
2015-10-01 00:00:00.000
2015-09-01 00:00:00.000
2015-08-01 00:00:00.000
2015-07-01 00:00:00.000
2015-06-01 00:00:00.000
I have some possible solutions in the pipeline but feel there's probably a simpler way out there
May 11, 2016 at 7:53 am
Tally Table seemed unnecessarily complex - am sure there's something simpler
May 11, 2016 at 8:05 am
DuncEduardo (5/11/2016)
Tally Table seemed unnecessarily complex - am sure there's something simpler
The most efficient methods I know of require a sequence from 1-12. You can call it dog if you like instead of tally table, and you can build it any number of ways. Doesn't change reality though. 😉
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 11, 2016 at 8:06 am
DuncEduardo (5/11/2016)
Tally Table seemed unnecessarily complex - am sure there's something simpler
Seriously? Do you understand what a tally table is? It's nothing more that a table (which could be a view, a function, a cte or a derived table) with a single column of consecutive numbers. It helps you to generate rows, as many as you need. Using a tally table is the only way to get this in a single statement.
May 11, 2016 at 8:07 am
Select some DATEADD columns from a table is complex?
There's many more complex methods, but some form of tally table's probably one of the easiest.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2016 at 8:11 am
By the looks of it this elementary, is this the real problem?
😎
May 11, 2016 at 8:15 am
I'd like to pile on and say a Tally Table is the best performing and most simple solution.
That said, another high-performing and very simple solution would be one that uses a calendar table.
-- Itzik Ben-Gan 2001
May 11, 2016 at 8:21 am
I've used mixture of "while", "getdate()", "datediff" and for the minute size of it, works well.
I'd consider tally table for something more substantial, but for the sake of 12 rows containing single column where processing is done daily out-of-hours then I'm not worried about performance to be honest.
May 11, 2016 at 8:39 am
DuncEduardo (5/11/2016)
I've used mixture of "while", "getdate()", "datediff" and for the minute size of it, works well.I'd consider tally table for something more substantial, but for the sake of 12 rows containing single column where processing is done daily out-of-hours then I'm not worried about performance to be honest.
I'm pretty sure is not as simple as this.
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-n, 0)
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))x(n);
May 11, 2016 at 8:50 am
Yes but you've explicitly stated numbers corresponding to calendar months from Jan - Nov. I try to avoid any form of "hard-coding".
Nice solution though
May 11, 2016 at 9:38 am
DuncEduardo (5/11/2016)
Yes but you've explicitly stated numbers corresponding to calendar months from Jan - Nov. I try to avoid any form of "hard-coding".Nice solution though
Luis's solution only has a table with 12 numbers (0 - 11) hardcoded. Substitute a datetime variable for the GETDATE() and change the dates and watch what happens.
May 11, 2016 at 9:46 am
For what it's worth, this one can be paramaterized in terms of the number of dates returned, and it doesn't require a pre-existing tally table, which might be useful in a scenario where you are querying a database for which you arn't the owner. However, leveraging a system table and ranking function to perform ad-hoc the job of a tally table is a performance hit and makes the code confusing to read.
select top (12) cast(convert( char(7), dateadd(month,(-row_number()
over (order by msglangid, error))+1, getdate() ), 121 )+'-01' as date) d
from master.sys.sysmessages
order by msglangid, error;
2016-05-01
2016-04-01
2016-03-01
2016-02-01
2016-01-01
2015-12-01
2015-11-01
2015-10-01
2015-09-01
2015-08-01
2015-07-01
2015-06-01
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 11, 2016 at 10:18 am
Eric M Russell (5/11/2016)
For what it's worth, this one can be paramaterized in terms of the number of dates returned, and it doesn't require a pre-existing tally table, which might be useful in a scenario where you are querying a database for which you arn't the owner. However, leveraging a system table and ranking function to perform ad-hoc the job of a tally table is a performance hit and makes the code confusing to read.
Which is why Luis' solution uses the table value constructor to create the sequence.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 11, 2016 at 10:31 am
Lynn Pettis (5/11/2016)
DuncEduardo (5/11/2016)
Yes but you've explicitly stated numbers corresponding to calendar months from Jan - Nov. I try to avoid any form of "hard-coding".Nice solution though
Luis's solution only has a table with 12 numbers (0 - 11) hardcoded. Substitute a datetime variable for the GETDATE() and change the dates and watch what happens.
And I can actually get the following code with 7 keystrokes using code snippets. Code snippets are a built-in functionality since SQL Server 2012.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT n
FROM cteTally
It could be even less work if I used a function which would allow me to get a defined range, but I avoid posting udfs in the forums.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply