请帮助VB和Excel [英] Help with VB and Excel please

问题描述

我正在尝试为一年中的每个月分配给每个销售代表的商店数量。 目前使用以下内容：

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&"）"& _

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 & ") " & _

等等。 。

基本上我想说的是，如果商店已经创建（在我们的系统中），比如说在2014年1月31日或之前，并且它没有在之前或之前关闭2014年2月1日 - 包括该销售代表的1月份的计数。现在，由于月份和年份与OR相关，因此
计数错误。 对此的任何帮助都将大大增加赞赏。 创建日期和过期日期以日期格式输入表格。

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.

推荐答案

那应该放在SQL查询的WHERE子句中，并且需要GROUP BY子句来根据商店收集结果。

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.

这篇关于请帮助VB和Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！