Solved

SQL - Help inserting into table

Posted on 2011-02-14
7
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 13

Author Comment

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

Thanks.
0
 
LVL 41

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

622 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