Any Idea how can I identify if there is new client added on my database.
I was thinking about identifying it thru date_added field.
id client_name date_added
---------------------------------
1 ABC 2013-01-02
2 XYZ 2013-01-03
3 EFG 2013-01-02
4 HIJ 2013-01-05
as you can see a new client added HIJ on 2013-01-05.
I was looking with this kind of result:
Client List
Total NO: 4
New Client
Total No: 1
Client Name: HIJ
It's hard to tell but based on your comment ...my reference date is 1 month interval... you might be looking for something like this
SELECT id, client_name, new_count, total_count
FROM
(
SELECT id, client_name
FROM clients
WHERE date_added BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
) c CROSS JOIN
(
SELECT
(
SELECT COUNT(*) new_count
FROM clients
WHERE date_added BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
) new_count,
(
SELECT COUNT(*) total_count
FROM clients
) total_count
) t
Obviously you can easily change CURDATE() with any other reference date in the past in this query and you get results for that date.
Lets assume that you have following sample data
+------+-------------+------------+ | id | client_name | date_added | +------+-------------+------------+ | 1 | ABC | 2013-05-13 | | 2 | XYZ | 2013-06-13 | | 3 | EFG | 2013-06-13 | | 4 | HIJ | 2013-08-11 | +------+-------------+------------+
and today is 2013-08-13 then the output from the query will be
+------+-------------+-----------+-------------+ | id | client_name | new_count | total_count | +------+-------------+-----------+-------------+ | 4 | HIJ | 1 | 4 | +------+-------------+-----------+-------------+