Count of birthdays for next 7 days and need count for each day and total

Question

I have seen so question in this site; but they are just giving birthday data only not giving count like: mysql query to get birthdays for next 10 days

SQL Select Upcoming Birthdays

Here what I want is birthday count for each day for upcoming next 9 days and from 2 days after: for example today is 03-18 so i want to send data of 03-20 to 03-26

Here is my query

SELECT count(DOB) as count,DAYOFMONTH(DOB) as day 
from patient 
where 
    day(DOB) >= day(DATE_ADD(CURDATE(), interval 2 day)) 
and day(DOB) < day(DATE_ADD(CURDATE(), interval 9 day)) 
and  month(DOB) < month(DATE_ADD(CURDATE(), interval 1 month))
group by day
ORDER BY day ASC

it is giving output like:

count   date
2444    03-20

2337    03-21

2354    03-22

2064    03-23

2118    03-24

2357    03-25

2181    03-26

But it is not correct as per I query where DOB LIKE '%-03-20' the count is different.

Thank you in advance.

Be sure to test your query for `CURDATE()` values of '2014-12-24' and '2016-02-24' to make sure you've handled end-of-year and leap-year cases correctly. This stuff is harder than it looks! — O. Jones, Mar 18 '15 at 12:32
thank you, however i know the leap year cases doesn't affect. i have to add another condition say- if the month date is at 03-28 then sending date might be 03-30 to 04-06 so this condition is also not satisfying. — Cruzer, Mar 18 '15 at 12:36

O. Jones · Accepted Answer · 2015-03-18T12:59:31.927

A good way to do that is to generate a subquery containing the appropriate days for these birthdays. That's good because it will work over month- and year- rollovers.

That subquery is this, for your 2nd-9th day requirement.

          SELECT MONTH(CURDATE()+INTERVAL 2+n DAY) mn, 
                 DAYOFMONTH(CURDATE()+INTERVAL 2+n DAY) dy
           FROM (
                   SELECT 0 n UNION ALL SELECT 1 UNION ALL
                   SELECT 2 UNION ALL SELECT 3 UNION ALL 
                   SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                ) seq

Then, you can join that result to your list of dates of birth, and summarize, like this.

SELECT COUNT(*), MONTH(p.DOB) birthday_month, DAYOFMONTH(p.DOB) birthday
  FROM patient p
  JOIN (
          SELECT MONTH(CURDATE()+INTERVAL 2+n DAY) mn, 
                 DAYOFMONTH(CURDATE()+INTERVAL 2+n DAY) dy
           FROM (
                   SELECT 0 n UNION ALL SELECT 1 UNION ALL
                   SELECT 2 UNION ALL SELECT 3 UNION ALL 
                   SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                ) seq
       ) d ON MONTH(p.DOB) = d.mn AND DAYOFMONTH(p.DOB) = d.dy
  GROUP BY MONTH(p.DOB), DAYOFMONTH(p.DOB)
  ORDER BY MONTH(p.DOB), DAYOFMONTH(p.DOB)

This should give you the correct counts.

Using numerical ranges for this kind of work is extremely error-prone.

Count of birthdays for next 7 days and need count for each day and total

1 Answers1