Scott.Skinner (6/30/2014)
This seems to work OK. Many ways to solve it.UPDATE
date_calendar
SET
quarter_weekdays_remaining =
(
SELECTISNULL( SUM( r.is_weekday ), 0 )
FROMdate_calendar r
WHEREr.calendar_date > s.calendar_date
ANDr.calendar_year = s.calendar_year
ANDr.calendar_quarter = s.calendar_quarter
),
quarter_weekdays_completed =
(
SELECTISNULL( SUM( c.is_weekday ), 0 )
FROMdate_calendar c
WHEREc.calendar_date <= s.calendar_date
ANDc.calendar_year = s.calendar_year
ANDc.calendar_quarter = s.calendar_quarter
)
FROM
date_calendar s
Nice. I opted to go with changing the following:
UPDATE
dbo.date_calendar
SET
month_weekdays_remaining = A.month_weekdays_remaining
,quarter_weekdays_remaining = A.quarter_weekdays_remaining
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,COALESCE (Y.month_weekdays_remaining, Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
,COALESCE (Y.quarter_weekdays_remaining, Z.quarter_weekdays_remaining, X.weekdays_in_quarter) AS quarter_weekdays_remaining
FROM
dbo.date_calendar X
LEFT JOIN dbo.date_calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
AND Y.year_month = X.year_month
LEFT JOIN dbo.date_calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
AND Z.year_month = X.year_month
WHERE
X.month_weekdays_remaining IS NULL
) A
WHERE
A.subquery_calendar_date = calendar_date
To:
UPDATE
X
SET
X.month_weekdays_remaining = (CASE
WHEN Y.calendar_month = X.calendar_month AND Y.month_weekdays_remaining IS NOT NULL THEN Y.month_weekdays_remaining
WHEN Z.calendar_month = X.calendar_month AND Z.month_weekdays_remaining IS NOT NULL THEN Z.month_weekdays_remaining
ELSE X.weekdays_in_month
END)
,X.quarter_weekdays_remaining = (CASE
WHEN Y.calendar_quarter = X.calendar_quarter AND Y.quarter_weekdays_remaining IS NOT NULL THEN Y.quarter_weekdays_remaining
WHEN Z.calendar_quarter = X.calendar_quarter AND Z.quarter_weekdays_remaining IS NOT NULL THEN Z.quarter_weekdays_remaining
ELSE X.weekdays_in_quarter
END)
FROM
dbo.date_calendar X
LEFT JOIN dbo.date_calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
LEFT JOIN dbo.date_calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
WHERE
X.month_weekdays_remaining IS NULL