Hi
SQL Server 2008 R2
I'm struggling to achieve something that I'm pretty sure must be achievable!
I have a table with the following structure:
SalonID Int
Month1 Int
Month2 Int
Month3 Int
Month4 Int
Month5 Int
Month6 Int
Month7 Int
Month8 Int
Month9 Int
Month10 Int
Month11 Int
Month12 Int
The following code is a stored procedure that generates 12 rows of data. The final column is Availabledays and I want to update records on the AvailableDays table with the 12 values created into Month1, Month2 etc but I have been unable to get this to work!! Any ideas please?
ALTER PROCEDURE [dbo].[GetMonthlyAvail]
@SalonID int
AS
BEGIN
Declare @InputDate Date
EXEC [CalculateInputDate] @InputDate OUTPUT
Select @InputDate as 'ResultFromNestedSP'
SET NOCOUNT ON;
; With Calendar As
(
Select DateAdd(Day, Number, @InputDate) As WorkDay,
DatePart(Weekday, DateAdd(Day, Number, @InputDate)) As DayId
From Master..spt_values
Where Type = 'P'
And number < 366
)
Select Top 12
Year(Calendar.WorkDay) As WorkYear,
Month(Calendar.Workday) As WorkMonth,
Count(*) As WorkingDays,
Count(BankHoliday) As BankHolidays,
Count(*) - Count(BankHoliday) As AvailableDays
From Calendar
Inner Join salonOpening
On Calendar.DayId = salonOpening.Day
Left Join PublicHolidays
On Calendar.Workday = PublicHolidays.BankHoliday
where SalonOpening.SalonID = @SalonID
Group By Year(Calendar.WorkDay), Month(Calendar.Workday)
Order By WorkYear, WorkMonth
END
SQL Server 2008 R2
I'm struggling to achieve something that I'm pretty sure must be achievable!
I have a table with the following structure:
SalonID Int
Month1 Int
Month2 Int
Month3 Int
Month4 Int
Month5 Int
Month6 Int
Month7 Int
Month8 Int
Month9 Int
Month10 Int
Month11 Int
Month12 Int
The following code is a stored procedure that generates 12 rows of data. The final column is Availabledays and I want to update records on the AvailableDays table with the 12 values created into Month1, Month2 etc but I have been unable to get this to work!! Any ideas please?
ALTER PROCEDURE [dbo].[GetMonthlyAvail]
@SalonID int
AS
BEGIN
Declare @InputDate Date
EXEC [CalculateInputDate] @InputDate OUTPUT
Select @InputDate as 'ResultFromNestedSP'
SET NOCOUNT ON;
; With Calendar As
(
Select DateAdd(Day, Number, @InputDate) As WorkDay,
DatePart(Weekday, DateAdd(Day, Number, @InputDate)) As DayId
From Master..spt_values
Where Type = 'P'
And number < 366
)
Select Top 12
Year(Calendar.WorkDay) As WorkYear,
Month(Calendar.Workday) As WorkMonth,
Count(*) As WorkingDays,
Count(BankHoliday) As BankHolidays,
Count(*) - Count(BankHoliday) As AvailableDays
From Calendar
Inner Join salonOpening
On Calendar.DayId = salonOpening.Day
Left Join PublicHolidays
On Calendar.Workday = PublicHolidays.BankHoliday
where SalonOpening.SalonID = @SalonID
Group By Year(Calendar.WorkDay), Month(Calendar.Workday)
Order By WorkYear, WorkMonth
END
