| Consecutive Days Query [message #165490] |
Thu, 30 March 2006 10:21  |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Hello everyone. Long time no talk. It's been absolutely nuts here, but the implementation has been moving along quite nicely.
I have been given a requirement from our payroll office to report employees charging a specific time code for a certain length of time. In one instance, we want to see emps charging a time code for 6 consecutive days - or more (excluding weekends and holidays) and in another instance we have a different time code and 20 consecutive days or more. The output should resemble this:
Emp Name Account Date From Date To Time Code
Smitty, Joe 812397-09 01-JAN-06 13-JAN-06 S, L
I'm not going to assume that all of you guys are familiar with the Oracle Applications tables, so I'll give a simplified version of the table structure. This IS a denormalized table and yes it is seeded out of the box.
One row (record) would contain the following info:
Person_ID, Monday_Hours, Monday_Date, Monday_Time_Code, Tuesday_Hours, Tuesday_Date, Tuesday_Time_Code, etc.
There is other info, but this gives the table structure. I hope I am clear.
The problem is, we have to achieve these requirements using only SQL and PL/SQL and I am having a very difficult time with the caveat of 'X consecutive days OR MORE'. The 'OR MORE' part is what's killing me. I can return chunks of 6 days or 20 days, but these are hardcoded literals in my application (eg, if you find this time code, go back one day and compare the time code again, if it qualifies go back another day until it hits the predefined number of days). How can I make the query break (ie stop reporting data) when the Time Code in question is not in the pre-defined set rather than setting the limit as a hardcoded value? Any help would be greatly appreciated and I know I don't have the right tools for the job in this case, but I'd like to see if it could be done.
Thank you in advance!
|
|
|
|
| Re: Consecutive Days Query [message #165545 is a reply to message #165490] |
Thu, 30 March 2006 21:27  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I reckon this is possible, but I don't have time to work through the solution right now. I can give you some pointers though.
I would go about it in two steps:
1. Normalize the data by casting it to a nested table and un-nesting the nested table. See here for an example. This will give you a result set of the form:NAME TIME_CODE DATE
--------- --------- ---------
2. Use analytic functions to determine the number of consecutive dates for name and time code.
Once again, I don't have time to post a worked solution for your problem, so as an analogy, here is a SQL I wrote a while ago to solve the "Theatre Seating Problem". It is a query that finds n consecutive vacant seats in a theatre (assuming the theatre seats are arranged in a spiral with no aisles  .SELECT MIN(SeatNum) FromSeat, COUNT(*) NumSeats
FROM (
SELECT SeatNum, Booked,
SeatNum - RANK() OVER(ORDER BY Booked, SeatNum) AS GRP_KEY
FROM Seat
)
WHERE Booked = 'N'
GROUP BY GRP_KEY
HAVING COUNT(*) >= 4
Hopefully this gives you enough ideas to go forward. If not, post some CREATE TABLE and INSERT statements to set up test data and I'll see what I can do.
Ross Leishman
[Updated on: Thu, 30 March 2006 21:28] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Consecutive Days Query [message #181425 is a reply to message #165545] |
Mon, 10 July 2006 01:32 |
somegeek
Messages: 1
Registered: July 2006
|
Junior Member |
|
|
Hi
I have a very similar problem. It is called "Calculating consecutive days absent for a student". This involves calculating the consecutive no of days absent from the "AsOfDate" or present date. This consecutive block of days is the first block of days after the "AsOfDate", and the query returns just this number.
Could you give me more details about "theatre seating problem"
thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
