Solved

sql count for a date column

Posted on 2012-12-20
16
484 Views
Last Modified: 2012-12-27
This code from part of a larger program but I just need to fiqure out how to get the count for the date column. For example this one is (3). I tried -- count (Ipe.primary_start_date).

For example:

person_id    primary_start_date
8900923      14-NOV-11
8900923      14-NOV-11
8900923       25-SEP-09

select p.person_id, Ipe.primary_start_date
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
and p.person_id = 8900923
0
Comment
Question by:shizue
  • 7
  • 5
  • 2
  • +2
16 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38711592
You say the count is 3 but there are two different dates.  Shouldn't the count be 2 and 1 if grouped by date?

Dates in Oracle have a time portion built in.  The default display value is DD-MON-YY.

Assuming you want the counts by date try this:
select p.person_id, trunc(Ipe.primary_start_date), count(*)
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
and p.person_id = 8900923
group by p.person_id, trunc(Ipe.primary_start_date)
0
 

Author Comment

by:shizue
ID: 38711695
I am not at work anymore I will have to try it tomorrow. There will be multiple different dates for one  person_id. I need a count for all the dates for one person_id. One person_id could have 50 difference dates. And I have several more fields but I didn't list them all because I am going to incorpate this code with minor changes into a larger script.

Thank you in advance
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38711703
>>I need a count for all the dates for one person_id

Then that should look like:
select p.person_id, count(*)
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
and p.person_id = 8900923
group by p.person_id
0
 

Author Comment

by:shizue
ID: 38712192
My final report should look something like what I listed below. I am only having problems with how to display the count for Ipe.primary_start_date, which is the No_of_Pmry_Dt.

Name            Person_id     Age       Legal_Status     No_Pmry_Dt  Service_Desc  Worker
Jim Brow       7894525       2           Temp_Cust              3              Level 1            Mary Jones
Tom Martin   4562561      12          BBLP                       20            Level 2             Jo Smith
Tina Fay         1254887      8            OUHT                      10             Level 3           Sam Terry
Fred Peter      2558794     5             WEDF                      8               Level 5           John Tomas
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 38712962
Use the select as shown by slightwv in http:#a38711703 as "temporary table":
select Name, Person_Id, Age, Legal_Status, No_Pmry_Dt, Service_desc, Worker
from «put your other tables here»
join (select p.person_id, count(*) No_Pmry_Dt
from person p, legal_primary_episode lpe
where p.person_id = ipe.person_id
group by p.person_id) cnt on cnt.person_id = ...

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 38713145
>>My final report should look something like what I listed below<<
Can you post which attributes belong to which table?
0
 

Author Comment

by:shizue
ID: 38713378
ok here is my script

SELECT DISTINCT
       p.last_name || ', ' || p.first_name name,
       p.person_id,
       ROUND(MONTHS_BETWEEN(SYSDATE,p.birth_date)/12,1) age,
       funclib.get_custody_type(lp.person_id) Legal_status,
       ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt,
       st.service_desc,
       funclib.get_emp_for_p(p.person_id, 'WORKER', 'NAME') worker,
       funclib.get_emp_for_p(p.person_id, 'SUPERVISOR', 'NAME', SYSDATE, 'P') supervisor
       FROM sac_legal_bas lb,
       sac_legal_stats_info lsi,
       sac_legal_primary_episode lce,
       sac_legal_part lp,
       sac_f_agency_link cal,
       sac_person p,
       sac_case_part cp,
       sac_prov_name pn,
       sac_ref_data rd,
       sac_ref_data rdc,
       sac_placemnt_settn ps,
       sac_service_type st
   WHERE rdc.domain_code = 'LegalActionTypes'
   AND rdc.parent_ref_data_code = lb.legal_action_code
   AND rdc.ref_data_code = lb.legal_action_type_code
   AND lb.legal_action_type_code NOT IN ('PC')
   AND lb.legal_action_code = 'AGENCYLGLSTATUS'
   AND lb.legal_bas_id = lsi.legal_bas_id
   AND lsi.primary_id = lpe.primary_id
   AND lsi.termination_date IS NULL
   AND lpe.primary_start_date IS NOT NULL
   AND lsi.created_in_error_flag = 0
   AND lsi.legal_bas_id = lp.legal_bas_id
   AND funclib.get_persons_fam(lp.person_id) = cal.case_id
   AND cal.end_eff_date IS NULL
   AND cal.local_agency_id = 10022
   AND lp.person_id = p.person_id
   AND p.person_id = cp.person_id
   AND cp.current_status_code = 'ACTIVE'
   and ROUND(MONTHS_BETWEEN(SYSDATE,p.birth_date)/12,1) between 11 and  14
   AND pn.end_date(+) IS NULL
   AND pn.provider_id(+) = ps.service_provider_id
   AND rd.domain_code(+) = 'CPPlamntType'
   AND rd.ref_data_code(+) = ps.plmnt_type_code
   AND ps.child_id(+) = lp.person_id
   AND ps.service_id = st.service_id
   AND st.service_desc NOT IN ('Kinsh Care - Relative Hm')
   AND NVL(ps.end_reason_code, 'x') <> 'CREATEDINERROR'
   AND ps.end_date(+) IS NULL
   ORDER BY 1, 2, 3

So all I need is to figure out how to get a count for No_of_Pmry_Dt.

name          person_id  age legal_status  no_pmry_Dt  service_desc   worker        supervisor
Jim Brow     7894525    2   Temp_Cust      3                Level 1            Mary Jones   Dan Jones
Tom Martin 562561      12  BBLP              20             Level 2             Jo Smith      Annie Pere
Tina Fay      1254887     8   OUHT             10             Level 3           Sam Terry     Val Smith
Fred Peter  2558794     5   WEDF              8               Level 5           John Tomas  John Matt
0
 
LVL 31

Expert Comment

by:awking00
ID: 38713839
You are showing these lines -
ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt
sac_legal_primary_episode lce,
AND lsi.primary_id = lpe.primary_id

I assume you meant lpe in all cases). If so, can you post the relevant data for that table (i.e. all the records that contain those person_ids in your example)?
Also, your output shows no_pmry_dt (I also assume you mean no_of_pmry_dt) of 20 for Tom Martin. Does that represent the rounded number of days between the start_date and sysdate? Are you looking for an additional column to show the count of the primary_start_dates?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:shizue
ID: 38714017
No, "ROUND(sysdate - Ipe.primarty_start_date) No_of_Pmry_Dt" was suppose to be commented out. It was suppose to return the count for No_of_Pmry_Dt. But it did not return the count. Example: Jim Brow no_pmry_Dt(3), and Tom Martin (20), and Tina Fay (10). I need to retrieve the count. Please review my first txt. It has 3 no_pmry_Dt, so the count would be 3 for this person_id.
0
 
LVL 31

Expert Comment

by:awking00
ID: 38714111
Try sustituting this
...,
COUNT(lpe.primary_start_date) OVER (PARTITION BY p.person_id ORDER BY lpe.primary_start_date) No_of_Pmry_Dt,
ROW_NUMBER() OVER (PARTITION BY p.person_id ORDER BY lpe.primary_start_date) rn,
...
for this -
...,
 ROUND(sysdate - Ipe.primary_start_date) No_of_Pmry_Dt,
...
Then SELECT <columnlist> FROM
<wrap around your query modified as above>
WHERE rn = 1;
0
 

Author Comment

by:shizue
ID: 38714812
Awking00 I will try this on Monday. Thank you very much. I am out of the office and will not return til Monday. I am using Oracle sql. Is this code for Oracle/sql? I don't recall a Partition by statement. The round function brought in the wrong result.  
This code below works but how do I incorporate it into my code to make it work.

select distinct p.person_id, count(*) No_of_Pmry_Dt
from person p, sac_legal_primary_episode lpe
where p.person_id = lpe.person_id
---p.person_id = 7400923
group by p.person_id

Returns
person_id     No_of_Pmry_Dt
7400923                3
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38715129
please make sure you check out the comment of QLemo:
http://www.experts-exchange.com/Database/Oracle/Q_27974896.html#a38712962

I suspect your issue is that the COUNT(*) function currently simply doens't return the correct values, due to the many other joins you have in your query, and this "inline" view will solve that issue.
see also this article about DISTINCT and GROUP BY wihch you use in your queries and the "solutions":
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 38715712
Try the attached script
date-column-count.txt
0
 

Author Comment

by:shizue
ID: 38721818
Hi awking00, I ran my code using the partition by and over clause and it did not return the right count. I also ran it alone and it did not return the right count. I think this might be a data issuse. I have contacted our dba regarding a possible data issue. After I speak with her I will sign off on this open question. Are the group by or partition by clause the only clauses I can use to return the count on a group of records?
0
 
LVL 31

Expert Comment

by:awking00
ID: 38722128
The only two ways to get an aggregate (e.g. a count) are with a group by or a count() OVER ([PARTITION BY expr] ORDER BY expr). Using group by requires that all non_aggregated columns be include in the group by clause. So if you want the count of things by id from a table such as
id   thing  other
1     x        a
1     y        a
1     z        b
You could use select id, count(*) from table group by id.
But if you also want the other values, you would need to do the window function like -
select id, count(thing) over (partition by id order by id) since the group by function would require you to include the other in the group by clause.

Was the count returned too small or too big? I suspect that it might be too big. Given the extensive number of joins in your query, it's hard to say what causes the distortion. The window function probably requires a different partition by or order clause or both, but with access to the data, I wouldn't know where to begin.
0
 

Author Closing Comment

by:shizue
ID: 38724899
Thanks for all the additional information
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now