CODEWITHSUNDEEP
sqloracle-databasecountdate-range
Matej
Matej

Reputation: 43

Count specific date in date range in SQL

I want to ask you how can I get count of specific date (DD-MON) from date range (DD-MON-YY) from Oracle SQL? This is what I have:

MEMBERS    INSTALL_D   DOB      SYSDATE
6934109    22-FEB-12   18-NOV   09-JAN-17
6934109    22-FEB-12   13-JUN   09-JAN-17
6934109    22-FEB-12   12-AUG   09-JAN-17
6934109    22-FEB-12   24-OCT   09-JAN-17
6934109    22-FEB-12   04-FEB   09-JAN-17

I want count DOB from range of dates INSTALL_D and SYSDATE. Let's say that the

  1. row with 18-NOV is appear 5 times,
  2. row with 13-JUN is appear 5 times,
  3. row with 12-AUG is appear 5 times,
  4. row with 24-OCT is apeear 5 times

and the last one is only 4 times.

so, the result should be like:

MEMBERS   INSTALL_D   DOB      SYSDATE    COUNT(DOB)
6934109    22-FEB-12   18-NOV   09-JAN-17  5
6934109    22-FEB-12   13-JUN   09-JAN-17  5
6934109    22-FEB-12   12-AUG   09-JAN-17  5
6934109    22-FEB-12   24-OCT   09-JAN-17  5
6934109    22-FEB-12   04-FEB   09-JAN-17  4

I tried used MONTHS_BETWEEN function but this is not what I want to get.

EDIT:
I will be more specific. I used this query to get the first table above:
select B0022HOME_NO as Members, B0022INSTALLDATE as INSTALL_DATE, to_char(to_date (encryption.decrypt(a.B0031MEMDATEOFBIRT),'DD-MON-YYYY'),'DD-MON')as DOB, SYSDATE from BT0022, BT0031 a where B0022HOME_NO=a.B003101HOME_NO and BT0022.B0022HOME_NO=6934109;

INSTALL_D is day of instalation, which is in this example 22-FEB-12
DOB is birthday of member. Only day and month (DD-MON)
SYSDATE is for actual date.

Let's say, if the sysdate would be 18-NOV-12 and INSTALL_D is 22-FEB-12 and DOB of member is 18-NOV, that's mean that the DOB is occur once in this range of date.
In example: INSTALL_D 22-FEB-12 and SYSDATE 09-JAN-17 is DOB of 18-NOV occur 5 times, but if the SYSDATE will be 18-NOV-17, it will be 6 times. And that's it what I asked you about count(DOB) in date range.

Thanks in advance

Upvotes: 1

Views: 1451

Answers (3)

Boneist
Boneist

Reputation: 23588

Here's another way that will hopefully work with dob's that occur on leap years:

(N.b. I have assumed all your dates are stored as DATEs, meaning that your dob column has years. If that isn't the case, then a) why aren't you storing the year with the dob? and b) you'll have to update my query to convert the string into a suitable format for the comparison)

WITH sample_data AS (SELECT 6934109 members, to_date('22/02/2012', 'dd/mm/yyyy') install_d, to_date('18/11/1969', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934109 members, to_date('22/02/2012', 'dd/mm/yyyy') install_d, to_date('13/06/1991', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934109 members, to_date('22/02/2012', 'dd/mm/yyyy') install_d, to_date('12/08/1982', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934109 members, to_date('22/02/2012', 'dd/mm/yyyy') install_d, to_date('24/10/1963', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934109 members, to_date('22/02/2012', 'dd/mm/yyyy') install_d, to_date('04/02/1975', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934110 members, to_date('28/02/2011', 'dd/mm/yyyy') install_d, to_date('29/02/1976', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934110 members, to_date('29/02/2012', 'dd/mm/yyyy') install_d, to_date('01/03/1975', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934111 members, to_date('01/01/2017', 'dd/mm/yyyy') install_d, to_date('12/01/1957', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934111 members, to_date('01/01/2017', 'dd/mm/yyyy') install_d, to_date('02/01/1980', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934112 members, to_date('01/03/2011', 'dd/mm/yyyy') install_d, to_date('29/02/1976', 'dd/mm/yyyy') dob FROM dual UNION ALL
                     SELECT 6934112 members, to_date('29/02/2012', 'dd/mm/yyyy') install_d, to_date('29/02/1976', 'dd/mm/yyyy') dob FROM dual)
SELECT members,
       install_d,
       dob,
       SYSDATE,
       date_of_first_year,
       date_of_latest_year,
       months_between(date_of_latest_year, date_of_first_year)/12 + 1 count_birthdays
FROM   (SELECT members,
               install_d,
               dob,
               SYSDATE,
               CASE WHEN to_char(dob, 'mmdd') < to_char(install_d, 'mmdd') THEN
                         trunc(add_months(install_d, 12), 'yyyy')
                    ELSE trunc(install_d, 'yyyy')
               END date_of_first_year,
               CASE WHEN to_char(dob, 'mmdd') <= to_char(SYSDATE, 'mmdd') THEN
                         trunc(SYSDATE, 'yyyy')
                    ELSE add_months(trunc(SYSDATE, 'yyyy'), -12)
               END date_of_latest_year
        FROM   sample_data);

   MEMBERS INSTALL_D   DOB         SYSDATE     DATE_OF_FIRST_YEAR DATE_OF_LATEST_YEAR COUNT_BIRTHDAYS
---------- ----------- ----------- ----------- ------------------ ------------------- ---------------
   6934109 22/02/2012  18/11/1969  09/01/2017  01/01/2012         01/01/2016                        5
   6934109 22/02/2012  13/06/1991  09/01/2017  01/01/2012         01/01/2016                        5
   6934109 22/02/2012  12/08/1982  09/01/2017  01/01/2012         01/01/2016                        5
   6934109 22/02/2012  24/10/1963  09/01/2017  01/01/2012         01/01/2016                        5
   6934109 22/02/2012  04/02/1975  09/01/2017  01/01/2013         01/01/2016                        4
   6934110 28/02/2011  29/02/1976  09/01/2017  01/01/2011         01/01/2016                        6
   6934110 29/02/2012  01/03/1975  09/01/2017  01/01/2012         01/01/2016                        5
   6934111 01/01/2017  12/01/1957  09/01/2017  01/01/2017         01/01/2016                        0
   6934111 01/01/2017  02/01/1980  09/01/2017  01/01/2017         01/01/2017                        1
   6934112 01/03/2011  29/02/1976  09/01/2017  01/01/2012         01/01/2016                        5
   6934112 29/02/2012  29/02/1976  09/01/2017  01/01/2012         01/01/2016                        5

The only fly in the ointment occurs if you want a dob of 29th Feb to match to 1st of March on non-leap years.

My query above doesn't have that logic in it, but you could always add in a check to compare the last day of February in the install_d and sysdate years, and if it's 28th, then change a dob of '29/02' to '01/03' before doing that comparison. (I.e. compare install_d of 29/02/2012 with dob = 29/02 as 2012 is a leap year. You don't need to compare the sysdate, since 0301 >= 0229 and 0228 < 0229, regardess of whether the sysdate is a leap year or not.)

Upvotes: 1

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

Try this: (group by should work)

SELECT MEMBERS,INSTALL_D,DOB,SYSDATE,Count(DOB) FROM table_name GROUP BY  DOB ,MEMBERS,INSTALL_D  ,SYSDATE

Upvotes: 0

Monofuse
Monofuse

Reputation: 827

You could group by date segments, it's not the prettiest thing but will work. e.g.

GROUP BY CAST(DATEPART(mm, dob) AS VARCHAR(2)) + '-' + CAST(DATEPART(yyyy, dob) AS VARCHAR(4))

you could also place that in the select statement

Upvotes: 0

Related Questions