Ok, I have an sql script that exports employee records(revenue generated, total comission due etc..) for the start date and end date(these being the 1st of any month to the end of every month) that I must hard code into the script. Is there any way I can have a user enter the start/end date across the netowrk (say on excel), and have it read in by my script?????? help is appreciated
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER=MICROSOFT EXCEL DRIVER (*.XLS);DBQ=C:\filename,
'SELECT * FROM [SHEET1$]')>>>
Where exactly would I place the statement above??
this is where i pass in parameters right now...
COUNT(ResultPercent) AS [TestTaken],
COUNT(ResultPercent)*(AcctBillPricePer) AS [Dollar Amount],
COUNT(ResultPercent)*(AcctBillPricePer) * (ProveItRepRate) AS [Commission Due]
FROM Account JOIN AccountBilling ON AcctID = AcctBillAcctID
JOIN AccountLeadSource ON AcctAcctLeadSourceID = AcctLeadSourceID
JOIN ProveItRep ON AcctProveItRepID = ProveItRepID
JOIN Result ON AcctID = ResultAcctID
++++WHERE ResultDate BETWEEN @Start AND @End+++++++++++
then i just type in the dates when i run the program...
Maybe a structured solution would be to declare the @Start and @End dates and before the main statement to fill them with the Excel values. I.e.:
@Start datetime, @End datetime
Select @Start = * FROM OPENROWSET('MSDASQL', 'DRIVER=MICROSOFT EXCEL DRIVER (*.XLS);DBQ=C:\filename,
'SELECT * FROM [SHEET1$]')
... the same with @End...
The final statement remains the same...
I've never did these but I guess it'll work ok-- Maybe the SELECT * the "*" would have to be replaced with the cell name in excel.