Need some help with Group By / Having query 1

[CraigBest]

I have a journal table that keeps track of changes made, the system creates a new incremental record every time something is changed on the primary record.

I want to identify and capture records from the journal table where a specific field (company type) changes value, capturing the new company type, company code and the date it was changed, all values that will be in the journal record. To do this I need to read through all the records for each company in Company ID / chronological order from oldest to newest based on the date changed. It's possible that for many companies this value might never change at all.

I know it means using some kind of group by... having structure, but I cannot figure out how to set it up properly. No matter what I try I'm not getting the result set back that I want.

Can anyone help me figure this out? Thanks.

CraigHartz

 

[SantaMufasa]

Craig,

We're flying a little blind here. We could solve your issue (virtually) instantly if you posted a very simple (cut-down) version of your table CREATE and a very limited subset of data in the form of INSERTs into your cut-down table. It would also be useful to see the code that you have attempted so far, tailored to run against the abbreviated table, above, and the (incorrect) result set. Also please post an example of the output you do want from the abbreviated data.

We can then adjust your code to produce the results you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

As Santa says, your requirements aren't very precise, but I would guess something like lead/lag might be a way to go. Here's an example:

drop table company_audit;

create table company_audit (audit_id number, comp_id number, comp_name varchar2(50), other_detail varchar2(30), change_date date);

insert into company_audit values (1, 1,  'F Bloggs', 'Something', '01-jan-09');
insert into company_audit values (2, 1,  'F Bloggs', 'Something Else', '02-jan-09');
insert into company_audit values (3, 1,  'F Bloggs Ltd', 'Something', '03-jan-09');
insert into company_audit values (4, 2,  'J Bloggs', 'Something', '01-jan-09');
insert into company_audit values (5, 2,  'J Bloggs', 'Something Else', '06-jan-09');
insert into company_audit values (6, 3,  'Smiths', 'Something', '01-jan-09');
insert into company_audit values (7, 3,  'Joneses', 'Something Else', '06-jan-09');

select audit_id, comp_id, comp_name, next_comp_name
from
(select audit_id, comp_id, comp_name, lead(comp_name) over (partition by comp_id order by audit_id) as next_comp_name
from company_audit)
where next_comp_name is not null
and (comp_name <> next_comp_name)

 

[CraigBest]

Hi SantaMufasa, thanks. A very reasonable request, but I don't think the company I work for would allow me to post any of that, it's supposed to be secure.

But let me see what I can come up with, I might be able to put together something similar as an example without posting any actual code.



CraigHartz

 

[CraigBest]

Thanks Dagon, I will give that a try, I think you've correctly divined the intent here. I'll let you know if it works (It looks like it should).

CraigHartz

 

[SantaMufasa]

I don't think the company I work for would allow me to post any of that...

We don't want to see anything proprietary, either data, structures, or code. But take a look at Dagon's excellent assertion(s), above, and tell us how close he came to the results you want/need. (In case you haven't run his code yet, here are results of his data and code:

  AUDIT_ID    COMP_ID COMP_NAME                                          NEXT_COMP_NAME
---------- ---------- -------------------------------------------------- --------------
         2          1 F Bloggs                                           F Bloggs Ltd
         6          3 Smiths                                             Joneses

2 rows selected.

).


If Dagon produced what you need, give him a

...If it needs more work, then let us know what's neeeded.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CraigBest]

Yes, I think Dagon now has me on the right track. I can see where I was making an error. Thanks for the help!

CraigHartz