SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


While/Loop Help


While/Loop Help

Author
Message
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 155
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24814 Visits: 6519
You will want to look at a calendar table

Something like this
Calendar Table - http://www.sqlservercentral.com/scripts/Date/68389/



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5043 Visits: 3232
(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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52331 Visits: 21179
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 155
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52331 Visits: 21179
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 155
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
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5043 Visits: 3232
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52331 Visits: 21179
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):

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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5043 Visits: 3232
Thats a great take on the Date table creation and added to my snippets.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search