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/y yyy")) 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;
SELECT DISTINCT Count(format(Date,"mm/dd/y
May be I didn't understand your question, but why can't you use simple where clause,
SELECT DISTINCT Count(format(Date,"mm/dd/y yyy")) 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;
SELECT DISTINCT Count(format(Date,"mm/dd/y
WHERE (Date >= #04/01/1999# AND Sale_Type = 'S' AND Client_ID <> 0)GROUP BY Client_ID
ORDER BY Client_ID ASC;
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.
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.
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).
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;
SELECT DISTINCT Count(format(A.Date,"mm/dd
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"
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;
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Adjusted points to 200
ASKER
Points increased for you as promised. Cheers
Thanks.
.... WHERE ( TRUNC( DATE ) >= ...
M