Link to home
Start Free TrialLog in
Avatar of Mascolo
Mascolo

asked on

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;
Avatar of mark2150
mark2150

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
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;
Avatar of Mascolo

ASKER

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.
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.
Avatar of Mascolo

ASKER

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).
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;

Avatar of Mascolo

ASKER

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.
Avatar of Mascolo

ASKER

Oops, the above comment is meant to have said Count is NOT working properly.
Sure,

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

If possible send your MSAccess database also with required tables.

-Ginde
ASKER CERTIFIED SOLUTION
Avatar of ginde
ginde

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mascolo

ASKER

Adjusted points to 200
Avatar of Mascolo

ASKER

Points increased for you as promised. Cheers
Thanks.