IBM Mainframe Forum

by **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:

Select all

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:

Select all

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.

by **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?

by **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.

by **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.

by **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.

by **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

by **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

Select all

--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;

IBM Mainframe Forum

GROUP BY in some specific way

GROUP BY in some specific way

Re: GROUP BY in some specific way

Re: GROUP BY in some specific way

Re: GROUP BY in some specific way

Re: GROUP BY in some specific way

Re: GROUP BY in some specific way

Re: GROUP BY in some specific way