troubleshooting Question

Create report by Month only to list all birthdays

Avatar of shizue
shizueFlag for United States of America asked on
Programming Languages-OtherOracle DatabaseSQL
17 Comments9 Solutions727 ViewsLast Modified:
I am trying to create a report based on the query below but I would like to list birthdays by month only. I would like to enter a month and list all birthdays for that only. And I will probably do this on a monthly basic. For example, all birthdays for the month of March only. Then I will probably run it again for the month of APril, and so on and so on. But only one month at a time.  So, I am thinking I need to change the Case statement.

SELECT DISTINCT sacwiscuy.get_case_name(cb.case_id) case_name,
                cb.case_id,
                (p.last_name || ', ' || p.first_name) child_name,
                p.person_id,
                p.birth_date,
                funclib.get_emp_for_prif(p.person_id, 'WORKER', 'NAME', SYSDATE) worker,
                funclib.get_address(p.person_id, 'P', 'FULL')
  FROM sac_legal_base lb,
       sac_legal_participants lp,
       sac_legal_status_info lsi,
       sac_case_agency_link cal,
       sac_person p,
       sac_case_base cb
 WHERE     lb.legal_base_id = lp.legal_base_id
       AND lp.person_id = p.person_id
       AND lb.legal_base_id = lsi.legal_base_id
       AND lsi.created_in_error_flag = 0
       AND lsi.termination_date IS NULL
       AND lb.legal_action_code = 'AGENCYLGLSTATUS'
       AND lb.legal_action_type_code NOT IN ('COPS', 'COPSEXT', 'TCOPS')
       AND lb.case_id = cal.case_id
       AND cal.local_agency_id = 10017                                                    --cuyahoga
       AND cal.case_id = cb.case_id
       and CASE
                   -- if person was born on Feb 29, but current year is not a leap year
                   -- then treat the person as having been born on 3/1
                   WHEN TO_CHAR(birth_date, 'mmdd') = '0229'
                    AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
                   THEN
                       '03-01'
                   ELSE
                       TO_CHAR(birth_date, 'mm-dd')
               END BETWEEN '01-11' AND '03-25';
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 9 Answers and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 9 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros