I'm using master..spt_values
within a SQL Server that support is, using it as "months" numbers support (don't ask me why; probably old query write on-the-fly):
SELECT
months.number as Month,
ISNULL(t2.NumVisits, 0) AS NumVisits,
ISNULL(t2.NumActivities, 0) AS NumActivities,
ISNULL(t2.Estimated, 0) AS Estimated,
ISNULL(t2.Accepted, 0) AS Accepted,
ISNULL(t2.PercAccepted, 0) AS PercAccepted,
ISNULL(t2.PercValue, 0) AS PercValue
FROM master..spt_values months
LEFT JOIN
(SELECT
*,
CASE WHEN t1.NumVisits <> 0 THEN (CAST(t1.NumActivities AS DECIMAL) / t1.NumVisits) * 100 ELSE 0 END AS PercAccepted,
CASE WHEN t1.Estimated <> 0 THEN (CAST(t1.Accepted AS DECIMAL) / t1.Estimated) * 100 ELSE 0 END AS PercValue
FROM
(SELECT
MONTH(DateVisit) AS Month,
COUNT(*) AS NumVisits,
SUM(CASE WHEN DateActivity is not null THEN 1 ELSE 0 END) AS NumActivities,
SUM(Estimate) AS Estimated,
SUM(CASE WHEN DateActivity is not null THEN Estimate ELSE 0 END) AS Accepted
FROM [dbo].[Activities]
WHERE
DateVisit IS NOT NULL
AND (@Year IS NULL OR YEAR(DateVisit) = @Year)
AND (@ClinicID IS NULL OR ClinicID = @ClinicID)
AND (@MedicalID IS NULL OR MedicalID = @MedicalID)
AND (@TreatmentTypeID IS NULL OR TreatmentTypeID = @TreatmentTypeID)
GROUP BY MONTH(DateVisit)) t1
) t2
ON months.number = t2.Month
WHERE
Type = 'P' AND number BETWEEN 1 AND 12
AND (@QuarterID IS NULL
OR (@QuarterID = 1 AND months.number <= 3)
OR (@QuarterID = 2 AND months.number > 3 AND months.number <= 6)
OR (@QuarterID = 3 AND months.number > 6 AND months.number <= 9)
OR (@QuarterID = 4 AND months.number > 9 AND months.number <= 12))
ORDER BY months.number
Unfortunately, now I need to migrate the DB on another SQL Server (Azure), where master..spt_values
is not supported:
Reference to database and/or server name in 'master..spt_values' is not supported in this version of SQL Server.
How can I quickly replace that table on the query above? Local table? Any example?