Multiple datepart functions as parameters in stored procedure

94 Views Asked by At

I've got a query that works perfectly that I'm trying to turn into a stored procedure that allows a user to input parameters for month and then for two years to show a YOY comparison for that month. The Query that works is below.

WITH sales AS (
    SELECT customer_id, customer, [2022] AS [CY sales], [2021] AS [PY sales]
    FROM (
            SELECT customer_id, customer_name AS customer, DATEPART(yyyy, invoice_date) AS [year], SUM(sales_price) AS sales
            FROM sales_history
            WHERE DATEPART(mm, invoice_date) = '8' AND DATEPART(yyyy, invoice_date) IN ('2022', '2021')
            GROUP BY customer_id, customer_name, DATEPART(yyyy, invoice_date)
        ) AS src
    PIVOT (
        SUM(sales) FOR [year] IN ([2022], [2021])
        ) AS salespvt),
profit AS (
    SELECT customer_id, customer, [2022] AS [CY profit], [2021] AS [PY profit]
    FROM (
            SELECT customer_id, customer_name AS customer, DATEPART(yyyy, invoice_date) AS [year], SUM(sales_price-line_other_cost) AS profit
            FROM sales_history
            WHERE DATEPART(mm, invoice_date) = '8' AND DATEPART(yyyy, invoice_date) IN ('2022', '2021')
            GROUP BY customer_id, customer_name, DATEPART(yyyy, invoice_date)
        ) AS src
    PIVOT (
        SUM(profit) FOR [year] IN ([2022], [2021])
        ) AS profitpvt)
SELECT s.customer_id, s.customer, CONCAT(ct.first_name, ' ', ct.last_name) AS [Sales Rep], 
    COALESCE(s.[CY sales], 0) AS [CY Sales], COALESCE(p.[CY profit], 0) AS [CY Profit], COALESCE(p.[CY profit]/NULLIF(s.[CY sales], 0), 0) AS [CY GP],
    COALESCE(s.[PY sales], 0) AS [PY Sales], COALESCE(p.[PY profit], 0) AS [PY Profit], COALESCE(p.[PY profit]/NULLIF(s.[PY sales], 0), 0) AS [PY GP],
    COALESCE(s.[CY sales], 0)-COALESCE(s.[PY sales], 0) AS [Sales Growth], COALESCE((s.[CY sales]-s.[PY sales])/NULLIF(s.[PY sales], 0), 1) AS [Sales Growth %],
    COALESCE(p.[CY profit], 0)-COALESCE(p.[PY profit], 0) AS [Profit Growth], COALESCE((p.[CY profit]-p.[PY profit])/NULLIF(p.[PY profit], 0), 1) AS [Profit Growth %]
FROM sales AS s 
    INNER JOIN profit AS p ON s.customer_id = p.customer_id
    INNER JOIN customer AS c ON s.customer_id = c.customer_id
    INNER JOIN contacts AS ct ON c.salesrep_id = ct.id
ORDER BY [CY Sales] DESC;

I'm attempting to make the procedure like this:

CREATE PROCEDURE sp_YOYmonthly_sales_by_customer @month int, @year1 int, @year2 int
AS
WITH sales AS (
    SELECT customer_id, customer, [@year1] AS [CY sales], [@year2] AS [PY sales]
    FROM (
            SELECT customer_id, customer_name AS customer, DATEPART(yyyy, invoice_date) AS [year], SUM(sales_price) AS sales
            FROM sales_history
            WHERE DATEPART(mm, invoice_date) = @month AND DATEPART(yyyy, invoice_date) IN (@year1, @year2)
            GROUP BY customer_id, customer_name, DATEPART(yyyy, invoice_date)
        ) AS src
    PIVOT (
        SUM(sales) FOR [year] IN ([@year1], [@year2])
        ) AS salespvt),
profit AS (
    SELECT customer_id, customer, [@year1] AS [CY profit], [@year2] AS [PY profit]
    FROM (
            SELECT customer_id, customer_name AS customer, DATEPART(yyyy, invoice_date) AS [year], SUM(sales_price-line_other_cost) AS profit
            FROM sales_history
            WHERE DATEPART(mm, invoice_date) = @month AND DATEPART(yyyy, invoice_date) IN (@year1, @year2)
            GROUP BY customer_id, customer_name, DATEPART(yyyy, invoice_date)
        ) AS src
    PIVOT (
        SUM(profit) FOR [year] IN ([@year1], [@year2])
        ) AS profitpvt)
SELECT s.customer_id, s.customer, CONCAT(ct.first_name, ' ', ct.last_name) AS [Sales Rep], 
    COALESCE(s.[CY sales], 0) AS [CY Sales], COALESCE(p.[CY profit], 0) AS [CY Profit], COALESCE(p.[CY profit]/NULLIF(s.[CY sales], 0), 0) AS [CY GP],
    COALESCE(s.[PY sales], 0) AS [PY Sales], COALESCE(p.[PY profit], 0) AS [PY Profit], COALESCE(p.[PY profit]/NULLIF(s.[PY sales], 0), 0) AS [PY GP],
    COALESCE(s.[CY sales], 0)-COALESCE(s.[PY sales], 0) AS [Sales Growth], COALESCE((s.[CY sales]-s.[PY sales])/NULLIF(s.[PY sales], 0), 1) AS [Sales Growth %],
    COALESCE(p.[CY profit], 0)-COALESCE(p.[PY profit], 0) AS [Profit Growth], COALESCE((p.[CY profit]-p.[PY profit])/NULLIF(p.[PY profit], 0), 1) AS [Profit Growth %]
FROM sales AS s 
    INNER JOIN profit AS p ON s.customer_id = p.customer_id
    INNER JOIN customer AS c ON s.customer_id = c.customer_id
    INNER JOIN contacts AS ct ON c.salesrep_id = ct.id
ORDER BY [CY Sales] DESC;

But I run into the below errors

Error converting data type nvarchar to int.
The incorrect value "@year1" is supplied in the PIVOT operator.

I've attempted declaring the parameters as different datatypes and using CAST to change the datatypes for [year] in the pivot queries but always get the same error.

0

There are 0 best solutions below