Hi all,
I am trying to create one field (lets call this field as (YearMonth) that has
yyyymm format from current date (getdate()) to the last two years
it would be like this:
201604
201603
201602
201601
201512
201511
201510
201509
201508
201507
201506
201505
201504
201503
201502
201501
201412
201411
201410
201409
201408
201407
201406
201405
Anyone know how to develop this field with those values with sQL?
Limiting the size of the varchar chops of the hour portion that you don't want.
SELECT CONVERT(char(10), GetDate(),126)
Use a Numbers table (also called a Tally table). If you don't have one in your database, create one temporarily like this.
create table #N(n int not null primary key);
;with N(n) as
(
select 1 union all select 1 union all select 1
)
insert into #N
select
ROW_NUMBER() over (order by a.n)-1
from
N a cross join N b cross join N c;
Then, your query would be
select
REPLACE(convert(varchar(7),dateadd(mm,-n,getdate()),126),'-','')
from
#N
where
dateadd(mm,-n,getdate()) > dateadd(yy,-2,getdate());This is an excellent time to add the proverbial "Swiss Army Knife of T-SQL" to the permanent collection of tools in your database. Add this iTVF (high performance INLINE Table Valued Function) to your database.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL --Note that this is NOT "Recursion!!!"
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
Once that's in place, queries like this become child's play...
--===== Create the last two years of YYYYMM labels using the current month
-- as a starting point.
SELECT YYYYMM = CONVERT(CHAR(6),DATEADD(mm,DATEDIFF(mm,0,GETDATE())-t.N,0),112)
FROM dbo.fnTally(0,23) t
;Jeff, this is cool! Thank you for sharing.
Every time I have had to use numbers table, I have wondered to myself whether it is better to generate the numbers or pay the price of reading the data. In the same breath, I tell myself that the data is probably already in the cache, and then moved on.
The fnTally function runs very fast BUT it still won't beat a physical table for the very reason you mention... the table will live in cache, which is memory. Really tough to beat that for performance. BUT, the fnTally does come very close to being as fast and has the added benefit of generating precisely zero reads on it's own. Like my notes in the header say, Itzik Ben-Gan is the fellow that first came up with the principle.
Here's a picture of the performance measurements I made for an article (http://www.sqlservercentral.com/articles/T-SQL/74118/) a while back . If you look at the Red Skyrocket to the left, you'll also see why I beg people to stay away from recursive CTEs that "count".
I've got a physical tally table in every database (on the grounds that on some servers there is only our APP database, no "support" database, etc. etc.)
It only has 8,000 rows
The table is not very big, but given that it is in every database on our in-house servers, including Production, QA and DEV variants, it contributes to some bloat on the disk AND in every FULL backup that we make ... and then store, permanently, on tape etc. etc.
I may be mis-remembering but I can't think of a single circumstance where we use our Tally table that is performance critical (I don't think we use the Tally table in our String Splitter any more - we used to, and I guess that would have been a performance-sensitive operation).
So I think, given that your Tally Function is close-performing to a physical table, that it is MORE than good enough!! Trivial to deploy on a client's server if the need arises, and so on. In fact there is lots to love about it 
Heh... it's also a source of entertainment for me. When people have a performance problem at work, I can tell then they should have used the "eff-en Tally function" I told them about without it being an HR violation. 
Just to be sure, I don't normally us any kind of object-type prefix (Hungarian Notation) but we already have a Tally table in many of our databases and didn't want to remove it because we didn't want to have to find all the code that uses it and then change it and test it. So I caved-in and called in "fnTally".
.
JamesK, you are so smart!!! It works for me!!! Thanks JamesK and all!!! You guys are awesome!
Apologies for the delayed response, James (JamesK). Thank you for the kind feedback.
Very cool Jeff! Thanks
My pleasure. Thank you for the feedback.
