Home » SQL & PL/SQL » SQL & PL/SQL » Max Record Count and Date Range
icon5.gif  Max Record Count and Date Range [message #190637] Thu, 31 August 2006 08:52 Go to next message
Messages: 1
Registered: August 2006
Junior Member
I am currently using this SQL code to capture some records over the last 2 months and it has been working great. I am now being asked if I can change this code with specifications:

1) Scan the records in the system until the count (*) as Volume reaches 30 because they prefer that as a denominator when figuring an average

2) Only run the scan for a maximum of 6 months.

So, there will most likely be some records that do not reach a volume number of 30 in this date range. In this instance we will just take the maximum volume number reached at 6 months.

So, how can I write this so it will build the file each time a record has reached the maximum of 30 and keep scanning back until we reach 6 months? I am hoping that the Oracle language is comparable and feel I can decipher any suggestions/replies I may receive. If someone could lead me in the right direction on the proper order of the methodology in my code to accomplish these results it would be greatly appreciated. Desperate!

@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int

@startdate = dateadd (mm, -2, getdate())
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))

@enddate = dateadd (mm, 2 , @startdate)

select distinct

pe1.patev_loc_id as LocID,
pp_cproc_id_r as ProcID,
count (*) as Volume,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time)) as Minutes,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time))/count(*) as AvgMin

risdb_rch08_stag..performed_procedure (index pp_serv_time_r_ndx),
risdb_rch04_stag..patient_event pe1,
risdb_rch04_stag..patient_event pe2

pp_service_time_r between @Startdate and @Enddate
and pp_asn_req_no = pe1.patev_asn_req_no
and pp_asn_seq_no = pe1.patev_asn_seq_no
and pp_status_v = 'CP'
and pp_rep_id > 0
and pe1.patev_event_code = 'PB'
and (pp_asn_req_no = pe2.patev_asn_req_no
and pp_asn_seq_no = pe2.patev_asn_seq_no
and pe2.patev_event_code = 'PL')
and datediff (mi, pe1.patev_event_time, pe2.patev_event_time) > 0

group by
pe1.patev_loc_id , pp_cproc_id_r
Re: Max Record Count and Date Range [message #190653 is a reply to message #190637] Thu, 31 August 2006 09:50 Go to previous message
Messages: 7880
Registered: March 2000
Senior Member
[Edit] Sorry, misread your post.

[Updated on: Thu, 31 August 2006 09:50]

Report message to a moderator

Previous Topic: BLOB
Next Topic: Adding millions of rows to a new table in 11i with unique index
Goto Forum:

Current Time: Thu Oct 27 11:58:30 CDT 2016

Total time taken to generate the page: 0.11448 seconds