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

12-15-04, 08:41
|
|
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).
|
|

12-15-04, 09:04
|
|
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
|
|

12-15-04, 10:00
|
|
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.
|
|

12-15-04, 10:07
|
|
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
|
|

12-15-04, 10:14
|
|
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
|
|

12-15-04, 10:52
|
|
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.
|
|

12-15-04, 11:12
|
|
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.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|