Solved

sql count for a date column

Posted on 2012-12-20
16
491 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 69

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 32

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 32

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
 

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 32

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 32

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 32

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

776 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