Selecting all days between two dates.

[PaidTheUmpire]

Hi,

I've got a table "CALLS" which is in the following format:
ClientID, ClientUnitNumber, Date, CallLength, CallCost
1000000, 94658798, 10/04/06, 40, $2.00
1000000, 94658798, 12/04/06, 230, $9.50
1000000, 94658798, 12/04/06, 120, $6.50
1000005, 98765432, 12/04/06, 120, $6.50

Where each client can have multiple unit numbers.

What I'm trying to get some SQL to show the sum of CallLength and of
CallCost for each day.

SELECT ClientID, ClientUnitNumber, Date, Sum(CallLength), Sum(CallCost)
FROM Calls
GROUP BY ClientID, ClientUnitNumber, Date
HAVING (ClientID="1000000") AND (ClientUnitNumber="94658798");

Which would give the following:
ClientID, ClientUnitNumber, Date, CallLength, CallCost
1000000, 94658798, 10/04/06, 40, $2.00
1000000, 94658798, 12/04/06, 350, $16.00

What i need is a way to show ALL the dates between two preselected
dates (example 9/04/06 and 12/04/06) which give the following:
ClientID, ClientUnitNumber, Date, CallLength, CallCost
1000000, 94658798, 09/04/06, 0, $0.00
1000000, 94658798, 10/04/06, 40, $2.00
1000000, 94658798, 11/04/06, 0, $0.00
1000000, 94658798, 12/04/06, 350, $16.00


Any ideas?

 

[Ken Snell \(MVP\)]

This should do what you seek:

SELECT ClientID, ClientUnitNumber, [Date], Sum(CallLength), Sum(CallCost)
FROM Calls
WHERE ClientID="1000000" AND ClientUnitNumber="94658798"
AND [Date] Between #9/04/06# And #12/04/06#
GROUP BY ClientID, ClientUnitNumber, [Date];

Also, do not use Date as the names of a field. It is a reserved word in
ACCESS, and your use of it as a field name can lead to all kinds of problems
and confusion. See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

 

[Van T. Dinh]

Try:

SELECT ClientID, ClientUnitNumber, [Date], Sum(CallLength), Sum(CallCost)
FROM Calls
WHERE (ClientID="1000000")
AND ([Date] Between #04/09/2006# And #04/12/2006#)
GROUP BY ClientID, ClientUnitNumber, [Date]

"Date" in a bad Field name since there is an ibbuilt VBA function Date.
Suggest you change the Field name to something like "CallDate".

 

[Douglas J. Steele]

Both Ken and Van have given you good answers. What they failed to point out,
though, is that dates in SQL statements must be in mm/dd/yyyy format*,
regardless of what you may have set the Short Date format to under Regional
Settings. It's not clear from your example whether 10/04/06 represents 10
April, 2006 or 04 October, 2006. That's why Ken and Van had different
constants in their examples: Van's from Australia, where 10/06/06 means 10
April, 2006, so he changed it to #04/10/2006# in his solution. (My initial
assumption when reading your question is that you were using dd/mm/yyyy
format)

You might want to take a look at Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I have in my September
2003 Access Answers column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

* It's not actually true that dates must be in mm/dd/yyyy format. They can
be in any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point
is, dd/mm/yyyy format is going to be misinterpretted for the first 12 days
of each month. Access is going to assume mm/dd/yyyy format until it comes to
13/nn/nnnn, in which case it will realize that there is no 13th month, so
the first value must be a day.