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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
gindeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MascoloAuthor Commented:
Adjusted points to 200
0
MascoloAuthor Commented:
Points increased for you as promised. Cheers
0
gindeCommented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.