We help IT Professionals succeed at work.
Get Started

SQL Fiscal Calendar - Stored Procedure with Parameters

299 Views
Last Modified: 2017-11-26
Experts -

Table: FiscalCalendarMain
Fields:
FiscalCalendarID - Autonumber in SQL
FiscalCalendarName - nvarchar(255) - User Entry comes from a web page
FiscalCalendarStartDate - smalldatetime - User Entry comes from a web page
FiscalCalendarEndDate - smalldatetime - User Entry comes from a web page
FiscalStartMonth - nvarchar(255) - User Entry comes from a web page.

User accesses a web page & posts data onto above table. Once they submit, below table should also be automatically updated via a stored procedure with the parameters from above table (FiscalStartDate, FiscalEndDate, FiscalStartMonth)
Ex:
FiscalStartDate = 11/24/2017
FiscalEndDate = 06/30/2020
FiscalStartMonth = 7


Table: FiscalCalendarDetails
Fields:
SNO - Autonumber in SQL
FiscalCalendarID - nvarchar(255) - Comes from above table
FiscalDate - Smalldatetime - Ex: 11/24/2017
FiscalDay - nvarchar(255) - Ex: 24
FiscalDayName - nvarchar(255) - Ex: Friday
FiscalMonth - nvarchar(255) - Ex: 11
FiscalMonthName - nvarchar(255) - Ex: November
IsWeekend - nvarchar(255) - Ex: 1 for weekend(from FiscalDayName for Saturday and Sunday) and 0 for Weekdays
IsHoliday - nvarchar(255) - Default value for this field is 0.
HolidayName - nvarchar(255) - Default value for this field is ""
FiscalWk - nvarchar(255) - Ex: WK01 - Week number will be decided from the FiscalStartMonth from above table
FiscalQtr - nvarchar(255) - Ex: QTR01 - Quarter number will be decided from the FiscalStartMonth from above table
FiscalYear - nvarchar(255) - Ex: 2017
FiscalYearPerCalendar - nvarchar(255) - Ex: FY1718 (with prefix "FY", 17 and 18 in the example comes from FiscalStartMonth(from previous table) and FiscalEndMonth (12 months rolling from FiscalStartMonth)

Kindly help me with an SP for the above please.
Comment
Watch Question
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This problem has been solved!
Unlock 1 Answer and 15 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE