Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    25

    Unanswered: Getting user info from a script

    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

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Yes you can.

    Something like :

    SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER=MICROSOFT EXCEL DRIVER (*.XLS);DBQ=C:\filename,
    'SELECT * FROM [SHEET1$]')

    Good Luck!
    Shadow to Light

  3. #3
    Join Date
    May 2003
    Posts
    25
    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...

    SELECT ProveItRepTitle,
    AcctLeadSourceTitle,
    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...

  4. #4
    Join Date
    May 2003
    Location
    Chile
    Posts
    1
    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.:
    DECLARE
    @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.

    Gus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •