Link to home
Start Free TrialLog in
Avatar of Rick
Rick

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick
Rick

ASKER

Hi Olaf, for this I'm using 2005.
Table name is tblTimeByDay

Thanks.
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?
Avatar of Rick

ASKER

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

Thanks.
What is | 54 | 12 | 1 | 1 in the result set?
Avatar of Rick

ASKER

_WeekOfYear, _MonthOfYear, _FiscalMonthOfYear, _FiscalQuarter
54, 12, 1, 1

Thank you.
Avatar of Rick

ASKER

Thanks for pointing me in the right direction.