GROUP BY in some specific way



IBM's flagship relational database management system

GROUP BY in some specific way

Postby chig » Tue Oct 23, 2012 8:54 pm

Hi,

I have a peculiar requirement. apparently this is a smaller part of a bigger query. But rest of the things I am able to manage, except this small part.

I have a regular table whose data is something like this:

Component_number   start_date   end_date     manager
1                  2000-01-01   2005-03-13   M1
2                  2005-03-14   2006-02-28   M1
3                  2006-03-01   2006-03-10   M2
4                  2006-03-11   9999-12-31   M1

My Output should be:

start_date   end_date     Manager
2000-01-01   2006-02-28   M1
2006-03-01   2006-03-10   M2
2006-03-11   9999-12-31   M1

Basically I want to get the first start date and last end date for consecutive components having same managers. else it should be as in the table.

Any help would be greatly appreciated..

thanks a lot.
chig.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: GROUP BY in some specific way

Postby Pandora-Box » Tue Oct 23, 2012 9:09 pm

You could handle this with Simple programming logic + Cursor

Rather complicating

Also Is that been insisted to get it done in Sql?
User avatar
Pandora-Box
 
Posts: 65
Joined: Fri Feb 10, 2012 8:30 pm
Location: Mars
Has thanked: 3 times
Been thanked: 6 times

Re: GROUP BY in some specific way

Postby chig » Tue Oct 23, 2012 9:26 pm

thank you premkrishnan for your response.

As I said, this is a small part of a bigger query. As you know taking this part through cobol will then have to take many other parts of the bigger query also through cobol which is messier.

So, I wanted to know if there's anything thorugh sql then it remains easy. Ofcourse, at the end that would be my last choice.

thank you again for your response.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: GROUP BY in some specific way

Postby dick scherrer » Wed Oct 24, 2012 12:36 am

Hello and welcome to the forum,

How might someone guess where this bit would fit into some more encompassing code?

Your example does not appear to meet your "rules"?

Suggest you provide something that ahows where this "fits" and more complete/correct sample input/output.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: GROUP BY in some specific way

Postby chig » Wed Oct 24, 2012 1:59 am

thank you dick for your response.

I don't understand when you say, "Your example does not appear to meet your "rules"?". I may understand that what I am trying to perform through sql is bit tough, but if you are saying about rules, I thought its pretty simple (unless I have very badly communicated!).

regarding your more complete sample, I can understand your point. But you will have to trust me, it's such a big requirement that does not need to be specified and this is "sort of" a standalone where I can just fit it into it.

But somehow I am getting the feeling that if the moderator himself is in need of more clarification or bigger sample, the approach that I am trying to find is not pursue-able. Actually I knew this but thought to get through you guys. Now since I got the hint, i know what to do.

I will see if anybody else has anything to say before closing this thread and thank everybody for their inputs but nonetheless, I think its a good documentation to everybody that such kind of things can't be done (atleast easily) in sql.

Please pardon me if any of you have felt even slightly offended. I just meant to be honest.

thanks.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: GROUP BY in some specific way

Postby dick scherrer » Wed Oct 24, 2012 6:52 am

Hello,

But somehow I am getting the feeling that if the moderator himself is in need of more clarification or bigger sample,
While i'm one of the moderators, i am not the most knowledgable using SQL.

but if you are saying about rules, I thought its pretty simple (unless I have very badly communicated!).

Basically I want to get the first start date and last end date for consecutive components having same managers. else it should be as in the table.
Possibly i misunderstand, but the way i read this is that the "output" should all be for the same manager. The "output" also has components that are not consecutive.

I would not think it was time to "throw in the towel" yet :)
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: GROUP BY in some specific way

Postby GuyC » Fri Oct 26, 2012 3:42 pm

I've enjoyed myself with this one :
there are two ways to do this
1: keep left joining for as much as there could be consecutive rows
2: use recursive sql

But really if you have this kind of functionality , you might want to take a look at the new DB2 v10 feature :
Temporal data: http://eb140.elearn.ihost.com/InfoMgmt/ ... 120207.pdf

--drop table testtab;
create table testtab
(Cnr   integer,
Dstrt  date,
Dend    date,
mngr char(2))
;
insert into testtab values (1,'2000-01-01','2002-03-13', 'M1');
insert into testtab values (2,'2002-03-14','2005-03-13', 'M1');
insert into testtab values (2,'2005-03-14','2006-02-28', 'M1');
insert into testtab values (3,'2006-03-01','2006-03-01', 'M2');
insert into testtab values (3,'2006-03-02','2006-03-02', 'M2');
insert into testtab values (3,'2006-03-03','2006-03-03', 'M2');
insert into testtab values (3,'2006-03-04','2006-03-04', 'M2');
insert into testtab values (3,'2006-03-05','2006-03-10', 'M2');
insert into testtab values (4,'2006-03-11','9999-12-31', 'M1');

select *  from testtab order by Dstrt;

--Too many left joins --
select min(dstrt) as dstrt ,dend, mngr, qualitycheck from
(Select  a.cnr, a.dstrt ,coalesce(d.dend,c.dend,b.dend,a.dend) as Dend, a.mngr
,case when d.dend is not null then 'Need more Left joins' else 'OK' end as qualitycheck
from testtab A
left join testtab B
       on a.mngr = b.mngr
      and A.Dstrt < B.Dstrt
      and b.dstrt = a.dend + 1 day
left join testtab C
       on a.mngr = c.mngr
      and b.Dstrt < c.Dstrt
      and c.dstrt = b.dend + 1 day
left join testtab D
       on c.mngr = d.mngr
      and c.Dstrt < d.Dstrt
      and d.dstrt = c.dend + 1 day
) X
group by dend,mngr,qualitycheck
order by dstrt
;
-- Recursive method --
with cte( dstrt,dend, mngr) as (
select dstrt,dend, mngr from testtab A
where not exists (select 1 from testtab B where b.mngr=a.mngr and b.Dend = a.dstrt - 1 day)
union all
select A.dstrt,B.dend, a.mngr from cte A , testtab B
 where b.mngr=a.mngr
   and b.dend < '9999-12-31'
   and b.Dstrt = case when a.dend = '9999-12-31' then '9999-12-31' else a.dend + 1 day end
)
select dstrt,max(dend),mngr  from cte
group by dstrt,mngr
order by dstrt

drop table testtab;
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post