February 6, 2013 at 6:10 am
Good Morning
I am new to T-Sql and I have been taske with creating a simple Time table with four columns:
Date date not null,
CalendarYear int not null,
CalendarMonth varchar (30) not null,
FinancialYear int not null,
FinancialMonth varchar (30) not null)
And what I would like to do is populate the Date column with dates from 01/01/2011 upto 31/03/2015 and the other columns will self populate:
Example:
Date CalendarYear CalendarMonth FinancialYear FinancialMonth
01 Jan 2011 2011 January 2010 January
02 Jan 2011 2011 January 2010 January
03 Jan 2011 2011 January 2010 January
04 Jan 2011 2011 January 2010 January
05 Jan 2011 2011 January 2010 January
Can you help?
Thanks
Wayne
February 6, 2013 at 6:34 am
You will want to look at a calendar table
Something like this
Calendar Table - http://www.sqlservercentral.com/scripts/Date/68389/
February 6, 2013 at 6:46 am
(reposted from other thread)
Your best bet is to use a TALLY table, a CTE, or at worst a Recursive CTE.
using an crude tally table
With Cte_n
AS
(
Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
UNION ALL Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
),Cte_n1
AS
(
Select n1.a a from CTE_n n, Cte_n n1,Cte_n n2
),
Cte_Tally
AS
(
Select Row_Number() OVER (ORDER BY a) a from Cte_n1
)
Select
DateAdd(d,a-1,'01-Jan-1900')
From Cte_Tally
Working out the date parts year, months etc should be relatively simple.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 6, 2013 at 7:09 am
Jason-299789 (2/6/2013)
(reposted from other thread)Your best bet is to use a TALLY table, a CTE, or at worst a Recursive CTE.
using an crude tally table
With Cte_n
AS
(
Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
UNION ALL Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
),Cte_n1
AS
(
Select n1.a a from CTE_n n, Cte_n n1,Cte_n n2
),
Cte_Tally
AS
(
Select Row_Number() OVER (ORDER BY a) a from Cte_n1
)
Select
DateAdd(d,a-1,'01-Jan-1900')
From Cte_Tally
Working out the date parts year, months etc should be relatively simple.
Not the best bet IMO - doesn't even meet the requirements (what about the other columns?). Calendar table gets my vote.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2013 at 7:19 am
thanks for the quick replies, because I am learning I have decided to just populate the first column with a date say 01/01/2010 and then increase this in increments of 1. I have quickly had a look online and I have seen I can do a do wile statement.
February 6, 2013 at 7:39 am
wafw1971 (2/6/2013)
thanks for the quick replies, because I am learning I have decided to just populate the first column with a date say 01/01/2010 and then increase this in increments of 1. I have quickly had a look online and I have seen I can do a do wile statement.
Just because you can does not mean that you should.
Your WHILE loop will be an order of magnitude slower than a set-based solution, such as those proposed here.
--EDIT fixed typo.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2013 at 7:48 am
Hi Phil
Thanks for you replies, the calendar template option will be something I will use in the future but I would like to learn about loops. This is why I have just posted another post asking for help with a piece of code I have just written, once you see the code you will see how new I am to SQL
Thanks again.
Wayne
February 6, 2013 at 7:50 am
Phil Parkin (2/6/2013)
Jason-299789 (2/6/2013)
(reposted from other thread)Your best bet is to use a TALLY table, a CTE, or at worst a Recursive CTE.
using an crude tally table
With Cte_n
AS
(
Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
UNION ALL Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
),Cte_n1
AS
(
Select n1.a a from CTE_n n, Cte_n n1,Cte_n n2
),
Cte_Tally
AS
(
Select Row_Number() OVER (ORDER BY a) a from Cte_n1
)
Select
DateAdd(d,a-1,'01-Jan-1900')
From Cte_Tally
Working out the date parts year, months etc should be relatively simple.
Not the best bet IMO - doesn't even meet the requirements (what about the other columns?). Calendar table gets my vote.
Phil,
It was aimed as more of an example of how to generate the basic data (ie a Sequential date), so that you can then start adding on the additional attributes (month, year month, etc), my last comment about working out the date parts etc, was aimed at getting the OP to figure out the DATEPART, YEAR,MONTH, DAY, etc to add into the table, not that you dont need them in the table, though in review it does come across as the later.
The link to the SSC Article that Anthony posted covered the date table in more detail but I missed it before I re-posted the solution from the other thread.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 6, 2013 at 8:00 am
Jason-299789 (2/6/2013)
Phil,It was aimed as more of an example of how to generate the basic data (ie a Sequential date), so that you can then start adding on the additional attributes (month, year month, etc), my last comment about working out the date parts etc, was aimed at getting the OP to figure out the DATEPART, YEAR,MONTH, DAY, etc to add into the table, not that you dont need them in the table, though in review it does come across as the later.
The link to the SSC Article that Anthony posted covered the date table in more detail but I missed it before I re-posted the solution from the other thread.
Got you. But I still prefer Jeff Moden's method (see here[/url]):
DECLARE @StartDate DATETIME, --Inclusive
@EndDate DATETIME, --Exclusive
@Days INT
;
SELECT @StartDate = '2011', --Inclusive
@EndDate = '2015', --Exclusive
@Days = DATEDIFF(dd,@StartDate,@EndDate)
;
SELECT TOP (@Days)
TheDate = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2013 at 8:25 am
Thats a great take on the Date table creation and added to my snippets.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 2:38 pm
johmga26ssn (2/7/2013)
I have just read more closely ... I had thought the board was 2 dimensional. Yes, you just need a println() once you have printed the array: it doesn't depend on the value of k as I said.
Reported as spam.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2013 at 6:32 pm
wafw1971 (2/6/2013)
Hi PhilThanks for you replies, the calendar template option will be something I will use in the future but I would like to learn about loops.
Wayne
Better advice is learn to avoid them.
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
February 7, 2013 at 6:42 pm
dwain.c (2/7/2013)
wafw1971 (2/6/2013)
Hi PhilThanks for you replies, the calendar template option will be something I will use in the future but I would like to learn about loops.
Wayne
Better advice is learn to avoid them.
Better advice, yes, but hard to follow if the boss says you must use them.
February 8, 2013 at 2:27 pm
Lynn Pettis (2/7/2013)
Better advice, yes, but hard to follow if the boss says you must use them.
declare @success bit;
exec CurriculumVitae_prepare;
while (1=1) begin
exec Job_Apply @isWhileRequired = 0, @isApplicationSuccessful = @success output;
if (@success = 1) begin
exec Interview_Attend @isInterviewSuccessful = @success output;
if (@success = 1) begin
exec Job_Accept @isJobAccepted = 1;
exec Resignation_Give @timePeriod = 'immediate';
break;
end; -- if
end; -- if
end; -- while
February 11, 2013 at 12:56 am
Bruce W Cassidy (2/8/2013)
Lynn Pettis (2/7/2013)
Better advice, yes, but hard to follow if the boss says you must use them.
declare @success bit;
exec CurriculumVitae_prepare;
while (1=1) begin
exec Job_Apply @isWhileRequired = 0, @isApplicationSuccessful = @success output;
if (@success = 1) begin
exec Interview_Attend @isInterviewSuccessful = @success output;
if (@success = 1) begin
exec Job_Accept @isJobAccepted = 1;
exec Resignation_Give @timePeriod = 'immediate';
break;
end; -- if
end; -- if
end; -- while
:hehe:
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply