none
Need a macro to get data from sql database for a given period with certain condition RRS feed

  • Question

  • I have sql database namely sales.dbo with 7 columns. First column contains different sales person names, on column 2 to 6 other information, on column 7,sales average. What i need is a macro to get all sales person details if there sales average is more than 60 % between a given date. For example:

    In my database, i have data from 01/05/2016 to 31/05/2016, if i enter a period in my excel sheet between 25/05/2016 to 31/05/2016 and my required average for ex.60%(should be changed as per my need), then i need all the sales person details who continuously have sales average of more than 60% between 25 to 31st.

    If a sales man average was dropped below 60 % on 28th May , then i don't want to see him on my report.In simple words, i need all sales person who continuously hitting 60 % or more on average sales within my search period.


    • Edited by pvhy Wednesday, June 8, 2016 6:57 AM
    Tuesday, June 7, 2016 4:30 PM

All replies

  • Re:  average based upon a condition

    If you can get your data into an Excel worksheet (xl2010+) then maybe the AverageIfs function would do the job.
    See Excel help for "AVERAGEIFS"

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0

    • Edited by James Cone Wednesday, September 21, 2016 10:24 PM
    Tuesday, June 7, 2016 11:02 PM
  • i think my subject line makes my query very simple and confused. Can you please have a go on my detailed question and help me. I also changed the subject now. Also. i can't get my entire data into excel, i have a very big database. So that i thought a sql query could be helpful to me.




    • Edited by pvhy Wednesday, June 8, 2016 7:22 AM
    Wednesday, June 8, 2016 7:00 AM
  • This is an SQL question and not a VBA question.  I would recommend a forum that deals with SQL.  I write some SQL and I build the query a step at a time.  There are 100's of books on SQL.
    Saturday, June 11, 2016 12:55 PM
  • please Try out the below link

    Sub GetDataFromADO()
    
        'Declare variables'
            Set objMyConn = New ADODB.Connection
            Set objMyCmd = New ADODB.Command
            Set objMyRecordset = New ADODB.Recordset
    
        'Open Connection'
            objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"
            objMyConn.Open
    
        'Set and Excecute SQL Command'
            Set objMyCmd.ActiveConnection = objMyConn
            objMyCmd.CommandText = "select * from myTable"
            objMyCmd.CommandType = adCmdText
            objMyCmd.Execute
    
        'Open Recordset'
            Set objMyRecordset.ActiveConnection = objMyConn
            objMyRecordset.Open objMyCmd
    
        'Copy Data to Excel'
            ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
    
    End Sub

    Thanks,

    Saturday, June 11, 2016 1:00 PM
  • Hello,

    Just have a look at below link as well!

    http://www.becomeceleb.com?navigate=http://stackoverflow.com/questions/24210479/calculate-an-average-as-a-percent

    Thanks,

    Jessica T

    Sunday, June 12, 2016 8:45 AM