Getting totals from a query...not an easy one though...

  • Thread starter Thread starter brian
  • Start date Start date
B

brian

Okay, here's my situation...I have a table that looks as
follows:


Name 1stCall 2ndCall
Rep1 Date1 Date2
Rep2 Date1 Date2
Rep3 Date2 Date3

So this is tracking the dates that reps are making
outbound calls to a client. There is a date for the first
attempt, and a date for the second attempt.
I need to write a query that will give me total calls per
rep per day.

So far, I have the following SQL statement:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCTROW [CMA User Contact].[CS_Rep], Count([CMA
User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date,
Count([CMA User Contact].[2nd_Call_Date]) AS
CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date])) OR ((([CMA User Contact].
[2nd_Call_Date]) Between [Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];

The issue is that it is returning counts for dates that
are outside the range entered. For example, if I enter a
single date as the start and stop for my range, it will
include calls that fell outside that range if they are in
the corresponding first/second call date field.
For example, if I enter 5/5/2004 as the start and end
date, it will return a count for a first call made
4/29/2004.
Can anyone think of a way to get the query to only return
records that fall within the given date range?
Thanks in advance
B
 
See if the following gets you what you are after
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [CMA User Contact].[CS_Rep], Sum(IIf([CMA
User Contact].[1st_Call_Date]<[Start Date],0,IIf([CMA
User Contact].[1st_Call_Date]>[End Date],0,1) + IIf([CMA
User Contact].[2nd_Call_Date]<[Start Date],0,IIf([CMA
User Contact].[2nd_Call_Date]>[End Date],0,1)) AS
CountOf_Call_Date,
FROM [CMA User Contact]
GROUP BY [CMA User Contact].[CS_Rep];

Hope This Helps
Gerald Stanley MCSD
 
brian said:
Okay, here's my situation...I have a table that looks as
follows:


Name 1stCall 2ndCall
Rep1 Date1 Date2
Rep2 Date1 Date2
Rep3 Date2 Date3

So this is tracking the dates that reps are making
outbound calls to a client. There is a date for the first
attempt, and a date for the second attempt.
I need to write a query that will give me total calls per
rep per day.

So far, I have the following SQL statement:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCTROW [CMA User Contact].[CS_Rep], Count([CMA
User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date,
Count([CMA User Contact].[2nd_Call_Date]) AS
CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date])) OR ((([CMA User Contact].
[2nd_Call_Date]) Between [Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];
Click to expand...

brian,

PARAMETERS [Start Date] DateTime
,[End Date] DateTime;
SELECT DISTINCTROW
[CMA User Contact].[CS_Rep]
,Count([CMA User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date
,Count([CMA User Contact].[2nd_Call_Date]) AS CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date]))
OR ((([CMA User Contact].[2nd_Call_Date]) Between
[Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];


CREATE TABLE [CMA User Contact]
(CMAUserContactID Autoincrement
,[CS_Rep] TEXT(10)
,[1st_Call_Date] DATE
,[2nd_Call_Date] DATE
,CONSTRAINT pk_CMAUserContactID PRIMARY KEY (CMAUserContactID)
)

I entered sample data of:

Sally, 01/01/2004, 02/02/2004
Mike, 04/04/2004, 06/06/2004
Richard, 04/29/2004, 08/08/2004

The issue is that it is returning counts for dates that
are outside the range entered. For example, if I enter a
single date as the start and stop for my range, it will
include calls that fell outside that range if they are in
the corresponding first/second call date field.
For example, if I enter 5/5/2004 as the start and end
date, it will return a count for a first call made
4/29/2004.
Can anyone think of a way to get the query to only return
records that fall within the given date range?
Thanks in advance
B
Click to expand...


I copied and pasted the above query, without changes, into my test db.

I typed in 05/05/2004, as you did, for start and end dates, several times,
and the Query always returned 0 rows.

When I typed in start and end dates of 01/01/2004 and 10/10/2004, the
Query returned all 3 rows.

I'm sorry, but I can't reproduce the problem as stated.


Sincerely,

Chris O.
 
Chris,
The issue is that if you use your sample data, and query
only on the date of 6/6/2004, the results will come back
as a count of 2, one for the call on 6/6 (which is a 2nd
call) and one for the call on 4/4, the date of the first
call to this same client.

That's what I'm trying to avoid.
Thanks
-----Original Message-----

brian said:
Okay, here's my situation...I have a table that looks as
follows:


Name 1stCall 2ndCall
Rep1 Date1 Date2
Rep2 Date1 Date2
Rep3 Date2 Date3

So this is tracking the dates that reps are making
outbound calls to a client. There is a date for the first
attempt, and a date for the second attempt.
I need to write a query that will give me total calls per
rep per day.

So far, I have the following SQL statement:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCTROW [CMA User Contact].[CS_Rep], Count ([CMA
User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date,
Count([CMA User Contact].[2nd_Call_Date]) AS
CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date])) OR ((([CMA User Contact].
[2nd_Call_Date]) Between [Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];
Click to expand...

brian,

PARAMETERS [Start Date] DateTime
,[End Date] DateTime;
SELECT DISTINCTROW
[CMA User Contact].[CS_Rep]
,Count([CMA User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date
,Count([CMA User Contact].[2nd_Call_Date]) AS CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date]))
OR ((([CMA User Contact].[2nd_Call_Date]) Between
[Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];


CREATE TABLE [CMA User Contact]
(CMAUserContactID Autoincrement
,[CS_Rep] TEXT(10)
,[1st_Call_Date] DATE
,[2nd_Call_Date] DATE
,CONSTRAINT pk_CMAUserContactID PRIMARY KEY (CMAUserContactID)
)

I entered sample data of:

Sally, 01/01/2004, 02/02/2004
Mike, 04/04/2004, 06/06/2004
Richard, 04/29/2004, 08/08/2004

The issue is that it is returning counts for dates that
are outside the range entered. For example, if I enter a
single date as the start and stop for my range, it will
include calls that fell outside that range if they are in
the corresponding first/second call date field.
For example, if I enter 5/5/2004 as the start and end
date, it will return a count for a first call made
4/29/2004.
Can anyone think of a way to get the query to only return
records that fall within the given date range?
Thanks in advance
B
Click to expand...


I copied and pasted the above query, without changes, into my test db.

I typed in 05/05/2004, as you did, for start and end dates, several times,
and the Query always returned 0 rows.

When I typed in start and end dates of 01/01/2004 and 10/10/2004, the
Query returned all 3 rows.

I'm sorry, but I can't reproduce the problem as stated.


Sincerely,

Chris O.


.
Click to expand...
 
The problem is in your table structure. The temporary fix to this is to use a
UNION query to normalize the data and then use the union query as the source.


PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [CMA User Contact].[CS_Rep],
[CMA User Contact].[1st_Call_Date]
FROM [CMA User Contact]
WHERE [CMA User Contact].[1st_Call_Date]
Between [Start Date] And [End Date]
UNION ALL
SELECT [CMA User Contact].[CS_Rep],
[CMA User Contact].[2nd_Call_Date]
FROM [CMA User Contact]
WHERE [CMA User Contact].[2nd_Call_Date]
Between [Start Date] And [End Date]

Save that as query one (q1 for shorthand) and then

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCTROW [CS_Rep],
Count([1st_Call_Date]) AS CountOf1st_Call_Date
FROM Q1
GROUP BY [CS_Rep];
 
See Below:


brian said:
Chris,
The issue is that if you use your sample data, and query
only on the date of 6/6/2004, the results will come back
as a count of 2, one for the call on 6/6 (which is a 2nd
call) and one for the call on 4/4, the date of the first
call to this same client.
Click to expand...

When I run the Query with 6/6/2004 for start and end date, it only returns
1 record.

I'm running Access 2000 SR-3, Jet 4.0 SP-6, MDAC 7.0.

Sincerely,

Chris O.
That's what I'm trying to avoid.
Thanks
-----Original Message-----

brian said:
Okay, here's my situation...I have a table that looks as
follows:


Name 1stCall 2ndCall
Rep1 Date1 Date2
Rep2 Date1 Date2
Rep3 Date2 Date3

So this is tracking the dates that reps are making
outbound calls to a client. There is a date for the first
attempt, and a date for the second attempt.
I need to write a query that will give me total calls per
rep per day.

So far, I have the following SQL statement:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DISTINCTROW [CMA User Contact].[CS_Rep], Count ([CMA
User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date,
Count([CMA User Contact].[2nd_Call_Date]) AS
CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date])) OR ((([CMA User Contact].
[2nd_Call_Date]) Between [Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];
Click to expand...

brian,

PARAMETERS [Start Date] DateTime
,[End Date] DateTime;
SELECT DISTINCTROW
[CMA User Contact].[CS_Rep]
,Count([CMA User Contact].[1st_Call_Date]) AS CountOf1st_Call_Date
,Count([CMA User Contact].[2nd_Call_Date]) AS CountOf2nd_Call_Date
FROM [CMA User Contact]
WHERE ((([CMA User Contact].[1st_Call_Date]) Between
[Start Date] And [End Date]))
OR ((([CMA User Contact].[2nd_Call_Date]) Between
[Start Date] And [End Date]))
GROUP BY [CMA User Contact].[CS_Rep];


CREATE TABLE [CMA User Contact]
(CMAUserContactID Autoincrement
,[CS_Rep] TEXT(10)
,[1st_Call_Date] DATE
,[2nd_Call_Date] DATE
,CONSTRAINT pk_CMAUserContactID PRIMARY KEY (CMAUserContactID)
)

I entered sample data of:

Sally, 01/01/2004, 02/02/2004
Mike, 04/04/2004, 06/06/2004
Richard, 04/29/2004, 08/08/2004

The issue is that it is returning counts for dates that
are outside the range entered. For example, if I enter a
single date as the start and stop for my range, it will
include calls that fell outside that range if they are in
the corresponding first/second call date field.
For example, if I enter 5/5/2004 as the start and end
date, it will return a count for a first call made
4/29/2004.
Can anyone think of a way to get the query to only return
records that fall within the given date range?
Thanks in advance
B
Click to expand...


I copied and pasted the above query, without changes, into my test db.

I typed in 05/05/2004, as you did, for start and end dates, several times,
and the Query always returned 0 rows.

When I typed in start and end dates of 01/01/2004 and 10/10/2004, the
Query returned all 3 rows.

I'm sorry, but I can't reproduce the problem as stated.


Sincerely,

Chris O.


.
Click to expand...
Click to expand...
 
Back
Top