Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.