Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel file that uses an SQL query to pull inventory usage of the medications in our pharmacy over a period of time.

I currently just have to go to Data > Connections > Properties > Definition and just edit the command text for the date range that i want. I wanted to make it a little more user friendly so that anybody in the office is capable of changing the dates.

Is there any sort of script I can add to change just the date value in the SQL query?

I assumed it would have to be a visual basic macro of some sort. I just want a pop up to come up when opening the document that says "Input Start Date" and then one that says Input End Date." Here's the query if needed.

SELECT a.patid
    , a.patname
    , a.ndc
    , a.ccid
    , a.DrugLabelName
    , a.PackType
    , sum(a.Qty) AS TotQty
    , sum(isnull(a.ActCost, 0)) AS ActCost
    , Sum(CASE 
            WHEN a.ElecAdj = 1
                AND a.ElecAdj2 = 0
                THEN a.Payor1PaidAmt + a.PatientPayAmt
            WHEN a.ElecAdj = 1
                AND a.ElecAdj2 = 1
                THEN a.Payor1PaidAmt + a.Payor2PaidAmt + a.PatientPayAmt
            WHEN a.ElecAdj = 0
                AND a.ElecAdj2 = 1
                THEN a.Payor2PaidAmt + a.PatientPayAmt
            ELSE a.TtlPrice + a.CoPay
            END) AS Revenue
    , Sum(CASE 
            WHEN a.Reversed = 0
                THEN 1
            ELSE CASE 
                    WHEN a.transtype IN (
                            'P'
                            , 'B'
                            , 'Q'
                            , 'U'
                            )
                        THEN 1
                    ELSE CASE 
                            WHEN a.CreditAmt IS NOT NULL
                                THEN 0
                            ELSE - 1
                            END
                    END
            END) AS NumRxs
    , a.FacID
    , b.FacName
FROM Rx..Hrxs a
LEFT OUTER JOIN Fac..Facilities b ON a.FacID = b.FacID
WHERE a.DispenseDt BETWEEN '11/01/2012' / STARTDATE /
        AND '11/30/2012' / STARTDATE /
    AND a.PharmId = 'cps' / PHARMID /
    AND coalesce(a.UDRx, 0) = 0
    AND a.packtype = 'PHDEF'
GROUP BY a.PatID
    , a.patname
    , a.FacID
    , b.FacName
    , a.CCID
    , a.Ndc
    , a.DrugLabelName
    , a.packtype
ORDER BY a.FacID
    , a.DrugLabelName
share|improve this question

Well I figured it out with more research. I changed the two dates to question marks and changed the parameters to ask "start date" and "end date" and it seems to be executing the query correctly with the right date output.

share|improve this answer

You must log in to answer this question.