troubleshooting Question

SQL Fiscal Calendar - Stored Procedure with Parameters

Avatar of Manju
ManjuFlag for India asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
15 Comments1 Solution305 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros