sql-server - 我可以为外部数据查询创建日期范围参数，特别是对于 JOIN SQL 查询（数据不是来自一个表）吗？

Question

我需要使用日期范围参数将数据从 SQL Server 检索到 Excel 文件中。因此最终用户可以在特定的 Excel 单元格中输入日期范围，数据将根据该输入刷新。

我搜索并尝试了许多说明，但事实证明这些解决方案适用于基本 SQL 查询（数据仅来自一个表），而我的 SQL 查询是 JOIN 查询。

我找到但不起作用的解决方案在这里：http: //kannekens.nl/how-to-use-an-sql-query-with-parameters-in-excel/ 如何在 Excel 中向外部数据查询添加参数哪些不能以图形方式显示？

这是代码。参数是STORESALESLINE.DATEID

SELECT
    T2.*,
    T1.[TotalVendors(OB&E)],
    T1.[TotalSKUs(OB&E)],
    T1.[TotalSalesQty(OB&E)],
    T1.[TotalRevenue(-tax-promo)(OB&E)],
    T1.[TotalGP(-promo)(OB&E)],
    T1.[TotalGP%(OB&E)]

FROM

    (SELECT
            PRODUCT.CAT1 AS Cat1,
            PRODUCT.CAT2 AS Cat2,
            PRODUCT.CAT3 AS Cat3,
            COUNT(DISTINCT(PRODUCT.VENDORID)) AS 'TotalVendors(OB&E)',
            COUNT(DISTINCT(PRODUCT.ITEMID)) AS 'TotalSKUs(OB&E)',
            SUM(STORESALESLINE.INVENTQTY) AS 'TotalSalesQty(OB&E)',
            SUM(STORESALESLINE.NETAMOUNT) AS 'TotalRevenue(-tax-promo)(OB&E)',
            SUM(STORESALESLINE.COSTAMOUNT) AS 'TotalGP(-promo)(OB&E)',
            (SUM(STORESALESLINE.NETAMOUNT) - SUM(STORESALESLINE.COSTAMOUNT))/SUM(STORESALESLINE.NETAMOUNT)*100 AS 'TotalGP%(OB&E)'
     FROM PRODUCT
     JOIN STORESALESLINE
     ON PRODUCT.ITEMID = STORESALESLINE.ITEMID
     WHERE
        YEAR(STORESALESLINE.DATEID) = 2019
     AND
        (PRODUCT.OWNBRAND = 'OWNBRAND'
         OR
         PRODUCT.OWNBRAND = 'EXCLUSIVE')
     GROUP BY PRODUCT.CAT1, PRODUCT.CAT2, PRODUCT.CAT3) AS T1

JOIN

    (SELECT
            PRODUCT.CAT1 AS Cat1,
            PRODUCT.CAT2 AS Cat2,
            PRODUCT.CAT3 AS Cat3,
            COUNT(DISTINCT(PRODUCT.VENDORID)) AS 'TotalVendors(All)',
            COUNT(DISTINCT(PRODUCT.ITEMID)) AS 'TotalSKUs(All)',
            SUM(STORESALESLINE.INVENTQTY) AS 'TotalSalesQty(All)',
            SUM(STORESALESLINE.NETAMOUNT) AS 'TotalRevenue(-tax-promo)(All)',
            SUM(STORESALESLINE.COSTAMOUNT) AS 'TotalGP(-promo)(All)',
            (SUM(STORESALESLINE.NETAMOUNT) - SUM(STORESALESLINE.COSTAMOUNT))/SUM(STORESALESLINE.NETAMOUNT)*100 AS 'TotalGP%(All)'
     FROM PRODUCT
     JOIN STORESALESLINE
     ON PRODUCT.ITEMID = STORESALESLINE.ITEMID
     WHERE
        YEAR(STORESALESLINE.DATEID) = 2019
     GROUP BY PRODUCT.CAT1, PRODUCT.CAT2, PRODUCT.CAT3) AS T2

ON T1.Cat1 = T2.Cat1 AND T1.Cat2 = T2.Cat2 AND T1.Cat3 = T2.Cat3 

标签： sql-serverexcelparameters