[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

SQL - Help inserting into table

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
Rick
Asked:
Rick
  • 4
  • 2
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
RickAuthor Commented:
Hi Olaf, for this I'm using 2005.
Table name is tblTimeByDay

Thanks.
0
 
SharathData EngineerCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RickAuthor Commented:
Typo. The month on the highlighted dates is supposed to be 12.

Thanks.
0
 
SharathData EngineerCommented:
What is | 54 | 12 | 1 | 1 in the result set?
0
 
RickAuthor Commented:
_WeekOfYear, _MonthOfYear, _FiscalMonthOfYear, _FiscalQuarter
54, 12, 1, 1

Thank you.
0
 
RickAuthor Commented:
Thanks for pointing me in the right direction.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now