SQL - Count Query

I have this query(shown below) which returns (if it worked properly) the number of Visits, Total Spend and the Client ID for the year to date. However there may be History entries for the same client with the date/time a second or so apart, so the count treats them seperately. How do I ignore the time and count based on the unique date?

SELECT DISTINCT Count(format(Date,"mm/dd/yyyy")) as NoOfVisits, Sum(Cost) AS SumOfActual_Cost, Client_ID FROM ClientHistory WHERE (History_ID IN (SELECT History_ID FROM ClientHistory WHERE (Date >= #04/01/1999# AND Sale_Type = 'S' AND Client_ID <> 0))) GROUP BY Client_ID ORDER BY Client_ID ASC;
MascoloAsked:
Who is Participating?
 
gindeConnect With a Mentor Commented:
Refined query that I emailed you...
SELECT DISTINCT Count(format(A.Date,"mm/dd/yyyy")) AS NoOfVisits, A.Client_ID
FROM test AS A
WHERE (A.History_ID IN (SELECT MAX(B.History_ID) FROM test B
WHERE A.Client_ID = B.Client_ID
AND format(A.Date,"mm/dd/yyyy") = format(B.Date,"mm/dd/yyyy")
AND B.Sale_Type = 'S' ))
AND A.Date >= #04/01/1999#
AND A.Sale_Type = 'S'
AND A.Client_ID <> 0
GROUP BY A.Client_ID
ORDER BY A.Client_ID;

0
 
mark2150Commented:
Make the date = instead of >=. TRUNC will isolate the date part of the date/time field so you might wanna try:

.... WHERE ( TRUNC( DATE ) >= ...

M
0
 
gindeCommented:
May be I didn't understand your question, but why can't you use simple where clause,

SELECT DISTINCT Count(format(Date,"mm/dd/yyyy")) as NoOfVisits, Sum(Cost) AS SumOfActual_Cost, Client_ID FROM ClientHistory WHERE
WHERE (Date >= #04/01/1999# AND Sale_Type = 'S' AND Client_ID <> 0)GROUP BY Client_ID
ORDER BY Client_ID ASC;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
MascoloAuthor Commented:
mark2150,
Trunc is an unknown function, I'm using Microsoft Access 97.

Ginde,
I need to increment the count by 1 for each day they came regardless of the time or no. of visits that day.
0
 
gindeCommented:
May be you need History_ID  in the table, but I don't see any constructive use of it in your query. In other words, I think your query and my query will generate the same results, but you are un-necessarily using sub-query degrading the performance.
0
 
MascoloAuthor Commented:
There is a history_id but each entry gets a unique id. Just to expand, this is a clients hair treatment history in a hair dressers salon and they would have a shampoo and a cut which is 2 seperate entries but only 1 visit to the salon.
Maybe your query will work but it will still count each entry as they are not exactly the same date (i.e. 10mins apart).
0
 
gindeCommented:
Coming back to your problem, try this

SELECT DISTINCT Count(format(A.Date,"mm/dd/yyyy")) as NoOfVisits, Sum(A.Cost) AS SumOfActual_Cost, A.Client_ID FROM ClientHistory A

WHERE
(A.History_ID IN (SELECT MAX(B.History_ID) FROM ClientHistory B
WHERE A.Client_ID = B.Client_ID
AND format(A.Date,"mm/dd/yyyy") = format(B.Date,"mm/dd/yyyy")))
AND A.Date >= #04/01/1999#
AND A.Sale_Type = 'S'
AND A.Client_ID <> 0

GROUP BY A.Client_ID
ORDER BY A.Client_ID ASC;

0
 
MascoloAuthor Commented:
Ginde,
This is VERY close, I've had to use my old method to total the spend as your method only totalled for the max history id record not all the records making up that entire visit. However the count is working properly. For Client ID 79 it is saying 25 visits when, if calculated manually it is 29!
Is there anyway I can send you some data and you have a look, I'm willing to up the points.
0
 
MascoloAuthor Commented:
Oops, the above comment is meant to have said Count is NOT working properly.
0
 
gindeCommented:
Sure,

You can send me your data at
sandeep.ginde@bowne.com

If possible send your MSAccess database also with required tables.

-Ginde
0
 
MascoloAuthor Commented:
Adjusted points to 200
0
 
MascoloAuthor Commented:
Points increased for you as promised. Cheers
0
 
gindeCommented:
Thanks.
0
All Courses

From novice to tech pro — start learning today.