If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > How to pivot dates into columns?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-15-04, 08:41
database_abuser database_abuser is offline
Registered User
 
Join Date: Dec 2004
Location: Frankfurt
Posts: 26
How to pivot dates into columns?

Hi everyone,

at the risk of annoying everyone, I have a question.

How can I pivot date values from rows to columns?

I have already used sum(case when .... then 1 else 0 end) for example to total up rows into columns. The problem with dates is that I don't know how many various dates per customer I may have in total (could be 1 row, could be several thousand).

Ok, an example:

customer contract valid_from valid_to
123456 12 01.01.2003 13.01.2003
123456 12 13.01.2003 01.04.2003
123456 12 05.04.2003 31.12.2003
123456 12 01.01.2004 31.12.2004

in the above example, I am only interested in seeing the contiguous periods
that the contract was opened (ie. 01.01.2003 thru 01.04.2003, and then 05.04.2003 thru 31.12.2004)

Yeah, I know - I should use a cursor within a COBOL program or something like that - but I wanna know if it's possible using only SQL. It's probly a good thing that SQL isn't recursive.

Personally, I hate using valid-from and valid-to logic (probably because I've had to program endless programs in COBOL to handle the logic).
Reply With Quote
  #2 (permalink)  
Old 12-15-04, 09:04
ARWinner ARWinner is offline
Registered User
 
Join Date: Jan 2003
Posts: 3,575
SQL can be used recursively. Try this for an example:

with t1 (num) as (select 1 from sysibm.sysdummy1 union all select num+1 from t1 where num < 100) select * from t1

Also check out the section in this free book on recursion:
http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM


What you want can probably be done with straight SQL.
If you get it working, please post your solution. I have seen other questions
similar to this.

Andy
Reply With Quote
  #3 (permalink)  
Old 12-15-04, 10:00
database_abuser database_abuser is offline
Registered User
 
Join Date: Dec 2004
Location: Frankfurt
Posts: 26
hi,

thanks for the response - I tried the example and received error messages - apparently the DBA's don't wanna let people run recursive stuff. It would probably be easier to write a cobol prog than to convince the dba's to change the settings.

I originally tried it in QMF (yeah, I know, QMF is for wimps), then tried going into spufi and got the following message:
SPUFI SSID: DB32
+-----------------------------------------------------------------------------+
|, DSNESP06 ,|
|, DSNE345I WARNING: DB2 DATA CORRUPTION CAN RESULT ,|
|, FROM THIS SPUFI SESSION BECAUSE THE ,|
|, CCSID USED BY THE TERMINAL IS NOT THE ,|
|, SAME AS THE CCSID USED BY SPUFI. ,|
|, ,|
|, - TERMINAL CCSID: ,1141 ,|
|, - SPUFI CCSID: ,273 ,|
|, NOTIFY THE DB2 SYSTEM ADMINISTRATOR. ,|
|, ,|
|, PRESS: ENTER to continue ,|
|, END to exit SPUFI ,|
|, ,|
|, ,|
|, ,|
|, ,|
|, ,|
|, ,|
|, ,|
|, ,|
|, F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE ,|
|, F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT ,|
+-----------------------------------------------------------------------------+

Well, I'm gonna keep trying to see what I can come up with....any other replies would be appreciated.
Reply With Quote
  #4 (permalink)  
Old 12-15-04, 10:07
ARWinner ARWinner is offline
Registered User
 
Join Date: Jan 2003
Posts: 3,575
You did not mention which Version of DB2 and OS you are using. I assume from your response that it is OS/390. Recursion may not work there. What I gave you works under DB2 for LUW.

Andy
Reply With Quote
  #5 (permalink)  
Old 12-15-04, 10:14
database_abuser database_abuser is offline
Registered User
 
Join Date: Dec 2004
Location: Frankfurt
Posts: 26
hi,

yeah, ooops - i did forget to mention platform, etc.... DB2 V7R1 on os/390....ISPF 5.2 etc etc
Reply With Quote
  #6 (permalink)  
Old 12-15-04, 10:52
sathyaram_s sathyaram_s is offline
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,534
Recursion on os/390 is available only from v8 ..
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #7 (permalink)  
Old 12-15-04, 11:12
Wim Wim is offline
Registered User
 
Join Date: Nov 2004
Posts: 1,279
A nice intriguing problem.

Code:
CREATE TABLE MYTABLE (
	CUSTOMER	CHAR(1) NOT NULL,
	CONTRACT	SMALLINT NOT NULL,
	VALID_FROM	DATE NOT NULL,
	VALID_TO	DATE NOT NULL,
	CONSTRAINt PK_MYTABLE PRIMARY KEY (CUSTOMER, CONTRACT, VALID_FROM)
);

INSERT INTO MYTABLE VALUES ('A', 1, '01/01/2000', '01/02/2001'); --
INSERT INTO MYTABLE VALUES ('A', 1, '02/02/2001', '01/04/2001');
INSERT INTO MYTABLE VALUES ('A', 1, '02/04/2001', '01/12/2001');
INSERT INTO MYTABLE VALUES ('A', 1, '02/12/2001', '01/02/2002');
INSERT INTO MYTABLE VALUES ('A', 1, '01/03/2002', '01/04/2002'); --
INSERT INTO MYTABLE VALUES ('A', 1, '02/04/2002', '01/02/2003');
INSERT INTO MYTABLE VALUES ('A', 1, '02/02/2003', '01/12/2004');
INSERT INTO MYTABLE VALUES ('A', 2, '01/03/2002', '01/04/2002'); --
INSERT INTO MYTABLE VALUES ('A', 2, '02/04/2002', '01/02/2003');
INSERT INTO MYTABLE VALUES ('B', 1, '01/01/2000', '01/02/2001'); --
INSERT INTO MYTABLE VALUES ('B', 1, '02/02/2001', '01/04/2001');
INSERT INTO MYTABLE VALUES ('B', 1, '02/04/2001', '01/12/2001');
INSERT INTO MYTABLE VALUES ('B', 1, '02/12/2001', '01/02/2002');
INSERT INTO MYTABLE VALUES ('C', 1, '02/12/2001', '01/02/2002'); --
 (dates are in DD//MM/YYYY format)

The result should be:
	A	1	01/01/2000	01/02/2002
	A	1	01/03/2002	01/12/2004
	A	2	01/03/2002	01/02/2003
	B	1	01/01/2000	01/02/2002
	C	1	02/12/2001	01/02/2002
This is the recursive part:


Code:
WITH RPL (customer, contract, valid_from, valid_to, iter) AS
(
-- Insert all records from MYTABLE when for that customer/contract 
-- combination, the valid_from is not preceded by another contract-period 
-- (i.e. it is the first record of 1 or more consecutive contract periods). 
-- Marked with "--" after the INSERT statements.

SELECT T1.customer, T1.contract, T1.valid_from, T1.valid_to, 0
FROM MYTABLE T1
WHERE NOT EXISTS (SELECT 1
		FROM MYTABLE T2
		WHERE T1.customer = T2.customer AND
			T1.contract = T2.contract AND
			T1.valid_from <> T2.valid_from AND
			T1.valid_from = T2.valid_to + 1 DAY
		)

UNION ALL

-- make a new record with the consecutive valid_to for that 
-- customer/contract-combination, until there is no consecutive 
-- mytable.valid_to to be found. You can check iter to find out how many 
-- iterations were necessary.

SELECT RPL.customer, RPL.contract, RPL.valid_from, MYTABLE.valid_to, RPL.iter+1
FROM RPL, MYTABLE
WHERE MYTABLE.customer = RPL.customer AND
	MYTABLE.contract = RPL.contract AND
	MYTABLE.valid_from = RPL.valid_to + 1 DAY
)
-- Get those records per customer/contract/valid_from-combination 
-- with the highest valid_to

SELECT customer, contract, valid_from, max(valid_to)
FROM RPL
GROUP BY customer, contract, valid_from
ORDER BY customer, contract, valid_from
;
 
The result:
A;1;2000-01-01;2002-02-01;
A;1;2002-03-01;2004-12-01;
A;2;2002-03-01;2003-02-01;
B;1;2000-01-01;2002-02-01;
C;1;2001-12-02;2002-02-01;
It was fun solving this one

Wim

Last edited by Wim; 12-15-04 at 13:05.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On