Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

While/Loop Help Expand / Collapse
Author
Message
Posted Wednesday, February 06, 2013 6:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:30 AM
Points: 62, 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

Post #1416459
Posted Wednesday, February 06, 2013 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1416471
Posted Wednesday, February 06, 2013 6:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 860, Visits: 2,323
(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
Post #1416481
Posted Wednesday, February 06, 2013 7:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1416497
Posted Wednesday, February 06, 2013 7:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:30 AM
Points: 62, 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.
Post #1416501
Posted Wednesday, February 06, 2013 7:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1416504
Posted Wednesday, February 06, 2013 7:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:30 AM
Points: 62, 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
Post #1416511
Posted Wednesday, February 06, 2013 7:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 860, Visits: 2,323
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
Post #1416513
Posted Wednesday, February 06, 2013 8:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1416522
Posted Wednesday, February 06, 2013 8:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 860, Visits: 2,323
Thats a great take on the Date table creation and added to my snippets.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1416539
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse