none
Help with VB and Excel please RRS feed

  • Question

  • I am trying to get the count of stores assigned to each sales reps for each months in a year.  Currently using the following:

    sqlSummary1 = "SELECT D.salesRep, " & _
        "count(CASE WHEN (D.createdMonth = 1 OR D.createdYear < " & intYear & ") " & _
            "AND (D.expiredMonth >= 1 OR D.expiredYear > " & intYear & ") THEN D.customer END) jan, " & _
        "count(CASE WHEN (D.createdMonth <= 2 OR D.createdYear < " & intYear & ") " & _
            "AND (D.expiredMonth >= 2 OR D.expiredYear > " & intYear & ") THEN D.customer END) feb, " & _
        "count(CASE WHEN (D.createdMonth <= 3 OR D.createdYear < " & intYear & ") " & _

    and so on..

    Basically what I wanted to say is, if a store is created(in our system), say on or before January 31 2014, and it is not closed on or before February 1, 2014 - include that in the count for January under that sales rep.  Right now the count is wrong as the month and year are connected with an OR.  Any help on this will be greatly appreciated.  The created date and Expired date are entered in the table in date format.

    Wednesday, March 26, 2014 12:09 AM

All replies

  • That should go in the WHERE clause of your SQL query, and you need a GROUP BY Clause to gather your result based on the store.

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Wednesday, March 26, 2014 10:18 AM
  • There are so many ways to count things in Excel.  Why are you using SQL to count?

    If you really want to convert SQL to VBA, use this little utility.

    http://allenbrowne.com/ser-71.html


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, May 2, 2014 3:26 PM