0

I need to retrieve data from SQL Server into Excel file with date range parameter. So the end user can enter the date range in specific excel cells and data will refresh bases on that input.

I searched and tried many instructions but it turns out that these solutions apply for basic SQL query (data come from one table only) whereas my SQL query are JOIN query.

The solutions i found but did not work are here: http://kannekens.nl/how-to-use-an-sql-query-with-parameters-in-excel/ How to add parameters to an external data query in Excel which can't be displayed graphically?

Here is the code. The parameter is 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
Bagus Tesa
  • 1,317
  • 2
  • 19
  • 42
Lauren Mai
  • 1
  • 2
  • **whereas my SQL query are JOIN query** any [mcve] can be provided? most of the time you just need to use the [table alias](https://www.techonthenet.com/sql_server/alias.php). – Bagus Tesa Oct 29 '19 at 06:49
  • Thank you for replying. I update the code in the description. – Lauren Mai Oct 29 '19 at 08:19
  • sorry, i didnt read your question fully (you need more than just a query, that excel thing), `YEAR(STORESALESLINE.DATEID) = 2019` this filter you can change it with `STORESALESLINE.DATEID BETWEEN '2019-10-03' AND '2019-11-03'`. for fetching data from column, i guess you can try [this](https://stackoverflow.com/questions/32404582/get-value-from-a-cell-on-a-worksheet). – Bagus Tesa Oct 29 '19 at 09:22
  • Thanks for answering. But the purpose is that I want to create a parameter, so that the end user can change the date without editing the original query. – Lauren Mai Oct 29 '19 at 09:39

0 Answers0