Solved

SQL - Help inserting into table

Posted on 2011-02-14
7
290 Views
Last Modified: 2012-06-21
Hello,

I have a table with the following columns

_Date, _CalendarDay, _CalendarMonth, _FiscalMonth, _CalendarYear, _FiscalYear, _DayOfMonth, _WeekOfYear, _MonthOfYear, _FiscalMonthOfYear, _FiscalQuarter


I need help inserting data for 2011.

For example,

The current year is 2011
The fiscal year started on 2010-12-27
Fiscal Month No 1 is from 2010-12-27  to 2011-01-30
Fiscal Quarter No 1 is from 2010-12-27 to 2011-03-27

The  result would be something like this


2010-12-27 | 27 | December | January | 2010 | 2011 | 27 | 54 | 12 | 1 | 1
2010-01-28 | 28 | December | January | 2010 | 2011 | 28 | 54 | 12 | 1 | 1
2010-01-29 | 29 | December | January | 2010 | 2011 | 29 | 54 | 12 | 1 | 1
2010-01-30 | 30 | December | January | 2010 | 2011 | 30 | 54 | 12 | 1 | 1
2010-01-31 | 31 | December | January | 2010 | 2011 | 31 | 54 | 12 | 1 | 1
2011-01-01 | 1 | January | January | 2011 | 2011 | 1 | 1 | 1 | 1 | 1
2011-01-01 | 2 | January | January | 2011 | 2011 | 2 | 2 | 1 | 1 | 1
2011-01-01 | 3 | January | January | 2011 | 2011 | 3 | 2 | 1 | 1 | 1
2011-01-01 | 4 | January | January | 2011 | 2011 | 4 | 2 | 1 | 1 | 1

etc, etc....


Fiscal months:

January      2010-12-27 to 2011-01-30
February    2011-01-31 to 2011-02-27
March         2011-02-28 to 2011-03-27
April           2011-03-28 to 2011-05-01
May            2011-05-02 to 2011-05-29
June           2011-05-30 to 2011-06-26
July            2011-06-27 to 2011-07-31
August       2011-08-01 to 2011-08-28
September  2011-08-29 to 2011-09-25
October      2011-09-26 to 2011-10-30
November  2011-10-31 to 2011-11-27
December  2011-11-28 to 2011-12-25


Fiscal quarters:

Quarter No. 1    2010-12-27 to 2011-03-27
Quarter No. 2    2011-03-28 to 2011-06-26
Quarter No. 3    2011-06-27 to 2011-09-25
Quarter No. 4   2011-09-26 to 2011-12-25


Thank you for your help
0
Comment
Question by:Rick
  • 4
  • 2
7 Comments
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 34889156
First of all, as you posted this to both SQL2005 and 2008, what version of T-SQL are we allowed to use now?
While that may not make much of a difference, also: How is the table name of that table you talk of?
And as a first recommendation I'd put the data about the Fiscal months into another table, which helps looking up the fiscal month and fiscal quarter of a certain date to insert.

Also the fiscal quarters are naturally resulting from the fiscal months data, so there is no gain in giving them, unless they also may be different from the fiscal months.

I'd suggest creating a table for fiscal month and a userdefine function to insert all days of a given fiscal year (as the only parameter) via this fiscal month data.

Bye, Olaf.
0
 
LVL 13

Author Comment

by:Rick
ID: 34889337
Hi Olaf, for this I'm using 2005.
Table name is tblTimeByDay

Thanks.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34890353
In your expected result

2010-12-27 | 27 | December | January | 2010 | 2011 | 27 | 54 | 12 | 1 | 1
2010-01-28 | 28 | December | January | 2010 | 2011 | 28 | 54 | 12 | 1 | 1
2010-01-29 | 29 | December | January | 2010 | 2011 | 29 | 54 | 12 | 1 | 1
2010-01-30 | 30 | December | January | 2010 | 2011 | 30 | 54 | 12 | 1 | 1
2010-01-31 | 31 | December | January | 2010 | 2011 | 31 | 54 | 12 | 1 | 1
2011-01-01 | 1 | January | January | 2011 | 2011 | 1 | 1 | 1 | 1 | 1
2011-01-01 | 2 | January | January | 2011 | 2011 | 2 | 2 | 1 | 1 | 1
2011-01-01 | 3 | January | January | 2011 | 2011 | 3 | 2 | 1 | 1 | 1
2011-01-01 | 4 | January | January | 2011 | 2011 | 4 | 2 | 1 | 1 | 1

After 2010-12-27, you have 2010-01-28. Is that a typo? Do you want 2010-12-28 or 2010-01-28?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 13

Author Comment

by:Rick
ID: 34890467
Typo. The month on the highlighted dates is supposed to be 12.

Thanks.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34891069
What is | 54 | 12 | 1 | 1 in the result set?
0
 
LVL 13

Author Comment

by:Rick
ID: 34891354
_WeekOfYear, _MonthOfYear, _FiscalMonthOfYear, _FiscalQuarter
54, 12, 1, 1

Thank you.
0
 
LVL 13

Author Closing Comment

by:Rick
ID: 34897331
Thanks for pointing me in the right direction.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now