How can I replace master..sp_values (used for months) on this query?

1000 Views Asked by At

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?

0

There are 0 best solutions below