Solved

SQL - Help inserting into table

Posted on 2011-02-14
7
314 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

777 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