Home » SQL & PL/SQL » SQL & PL/SQL » SQL to find chained item groups
SQL to find chained item groups [message #299012] Fri, 08 February 2008 09:48 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Just to preface this is purely a brainstorming post at this point, we have accomplished what I am going to describe using some fairly complex PL/SQL logic but I think there has to be a better way to do this purely through SQL and I am hoping someone may have done something similar or has some ideas no how it could be accomplished.

Essentially for examples sake we have a table which lists expenditures with an associated person id, type, quantity, and charge_date (there is more info obviously but for the purpose of what we are looking for those four values are all we should need)

CREATE TABLE TIME_CHARGES
(person_id NUMBER, 
 charge_date DATE,
 type VARCHAR2(20),
 quantity NUMBER);


The types can be regular, holiday, leave, or overtime (again obviously simplified for this example).

What I am looking for is chains of days which contain only holiday and leave that have a quantity of 160 or more with the following rules:

1. A day can contain quantities of more than 1 type.
2. If a day contains any quantity of regular or overtime it breaks the chain and any leave or holiday hours on the same day should not be included in the previous chain.
3. There may be days with no charges of any type, these will not break the chain.
4. There may be days which have offsetting charges to the same type so a +8 regular and a -8 regular for example these charges should be allowed to cancel each other out.
5. I have a limited number of dates to look and the user will pass a starting and end date which will define the range of dates to examine. Any charges outside this range are ignored so the chain must be able to exist with only the charges in our valid date range.

Some parts are easy #4 and #5 is just a simple sum to start off with something like:

SELECT sum(quantity) quantity, type, person_id, charge_date
FROM TIME_CHARGES
WHERE charge_date BETWEEN :date_from AND :date_to
GROUP BY type, person_id, charge_date
HAVING SUM(quantity) <> 0


and then build the rest of the query on top of it. I think the 160 hour requirement should come after the fact, so I determine all chains and their length first and then filter out the ones I need from the rest of the pack.

Like I said right now we use some fairly complex PL/SQL logic to get to this point but I think there must be a SQL way to accomplish the same thing and I am just looking for some help coming up with an idea of how to build these charge chains.

Any ideas?
Andrew
Re: SQL to find chained item groups [message #299016 is a reply to message #299012] Fri, 08 February 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to post a full test case: create table and insert statements as well as the result for this test case and explaination why you have each row in the result, the rules that lead to these rows.
Of course, your test case must cover all the rules and as much as possible cases that can happen.

Also you have to post your Oracle version with at least 3 decimals.

Regards
Michel
Re: SQL to find chained item groups [message #299218 is a reply to message #299016] Sun, 10 February 2008 20:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Using your starting point, we can decode the TYPE to be Holiday and Non-Holiday:
SELECT sum(quantity) AS quantity, person_id, charge_date
,      min(decode(type, 'holiday', 1, 0)) AS is_holiday
FROM (
  SELECT sum(quantity) quantity, type, person_id, charge_date
  FROM   time_charges
  WHERE  charge_date BETWEEN :date_from AND :date_to
  GROUP BY type, person_id, charge_date
  HAVING SUM(quantity) <> 0
)
GROUP BY person_id, charge_date

This gives us a row per person per day. is_holiday is 1 if the only type of time is a holiday, 0 otherwise.

Now we use a variant of the Theatre Seating Problem to count consecutive holidays. This is totally untested SQL of course. It might take some tweaking.

SELECT person_id, MIN(charge_date) from_date, COUNT(*) num_days
FROM (
  SELECT person_id, is_holiday,
         charge_date - RANK() OVER(PARTITION BY person_id, ORDER BY is_holiday, charge_date) AS grp_key
  FROM (
      SELECT sum(quantity) AS quantity, person_id, charge_date
      ,      min(decode(type, 'holiday', 1, 0)) AS is_holiday
      FROM (
        SELECT sum(quantity) quantity, type, person_id, charge_date
        FROM   time_charges
        WHERE  charge_date BETWEEN :date_from AND :date_to
        GROUP BY type, person_id, charge_date
        HAVING SUM(quantity) <> 0
      )
      GROUP BY person_id, charge_date
  )
)
WHERE is_holiday = 1
GROUP BY person_id, grp_key
HAVING COUNT(*) >= 160


Ross Leishman
Re: SQL to find chained item groups [message #299330 is a reply to message #299218] Mon, 11 February 2008 08:21 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Very cool idea, I had not used the RANK function before the only issue I had was non-consecutive days (days were there are neither holiday nor non-holiday charges) I just called them holidays and gave them a quantity of 0 which gets the job done not sure if there is a better way to do that though.

Thanks for the help Ross I never would have thought to that one on my own but now I can.

Andrew
Re: SQL to find chained item groups [message #299332 is a reply to message #299330] Mon, 11 February 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if you posted/post/will post a full test case, you surely had/have/will have a full answer/solution but it's up to you.

Regards
Michel
Re: SQL to find chained item groups [message #299335 is a reply to message #299332] Mon, 11 February 2008 08:40 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I am not asking for anything more than what Ross gave me, that is all I was looking for a good idea to start with I was not looking for someone to write a query for me.

My response mentioned the only piece I had trouble working around in Ross' example and explained the way in which I did work around it.

I do understand the need at times for more detailed examples than the one I gave, but I think that in this case where all I was looking for was a general example with a good idea to get me going in the right direction my example was sufficient. What I wanted and got was a general example for problems of this type, an example which as it turns out has been posted on this board before. IMHO all a more detailed sample set would have gotten me is a more detailed example which does not serve anyone as such an example would take more work on the part of the person providing it and this work would be for no real tangible gain in the end anyway.
Re: SQL to find chained item groups [message #299339 is a reply to message #299335] Mon, 11 February 2008 09:13 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said It's up to you.
Maybe for native english speakers it is easy to understand and maybe for non native english speakers an example is mandatory but if native english speakers are sufficient for you, why not.

Regards
Michel
Previous Topic: Trim function on table
Next Topic: Alternative for UPDATE CURSOR
Goto Forum:
  


Current Time: Thu Dec 08 12:47:38 CST 2016

Total time taken to generate the page: 0.27263 seconds