Inserting multiple dates into a table

drsmyth · #1 (**permalink**) 09-09-08, 06:31

Hi, i'm pretty nes to this forum, so please take it easy on me

.

Basically I have a requirement to populate a table with the last 4 moths and the last 5 quarters. My thinking behind this is I do a fresh insert every month and depending on the month number (i.e. if it's currently a quarter end then there are 7 dates curent month, current month -1months to current month -3months, current month -6 months, current month -9 months and current month -9 months)

My thought's were have an insert statement with the selection part based on a case statement which decides how many dates to insert... Sorry not sure if this is clear, but here's the code I have so far:

Quote:

INSERT INTO SESSION.INX_ANDY_DATES
(
REPORT_DT
,DESCRIPTION
,CURRENT_MONTH
,MONTH_END
,QUARTER_YEAR_END
,YEAR_END
,HAS_DATA
)
select case when month((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY) in (3,6,9,12) then (
select ((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)) in (3,6,9,12) then 'Quarter End' else 'Month End' end
,'Y'
,'Y'
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)) in (3,6,9,12) then 'Y'else 'N' end
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)) = 12 then 'Y'else 'N' end
,'N'
FROM
sysibm.sysdummy1
UNION select ((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 1 MONTH
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 1 MONTH) in (3,6,9,12) then 'Quarter End' else 'Month End' end
,'N'
,'Y'
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 1 MONTH) in (3,6,9,12) then 'Y'else 'N' end
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 1 MONTH) = 12 then 'Y'else 'N' end
,'N'
FROM
sysibm.sysdummy1
UNION select ((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 2 MONTH
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 2 MONTH) in (3,6,9,12) then 'Quarter End' else 'Month End' end
,'N'
,'Y'
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 2 MONTH) in (3,6,9,12) then 'Y'else 'N' end
,Case when month (((current_date - day(current_Date) DAYS + 1 DAY) - 1 DAY)- 2 MONTH) = 12 then 'Y'else 'N' end
,'N'
FROM
sysibm.sysdummy1)
end
FROM
sysibm.sysdummy1
WITH UR
;
COMMIT
;

Problem I've got, is this code looks like it's gonna be huge and I can't get even the first part of it to work (Multiple columns are returned from a subquery that is allowed only )...

Does anybody have any suggestions for another approach??

umayer · #2 (**permalink**) 09-09-08, 07:47

You might want to use recursive sql:

INSERT INTO your_table

WITH ABC ( DATUM ) AS
( SELECT (CURRENT DATE - (DAYOFMONTH(CURRENT DATE)-1) DAYS )
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT B.DATUM - 1 MONTH FROM SYSIBM.SYSDUMMY1 A ,
ABC B
WHERE B.DATUM > CURRENT DATE - 1 YEAR
)

SELECT
DATUM ,
CASE WHEN MONTH(DATUM) IN (3,6,9,12) THEN 'Quater End' ELSE 'Month End' END,
CASE WHEN DATUM = (CURRENT DATE - (DAYOFMONTH(CURRENT DATE)-1) DAYS )
THEN 'Y' ELSE 'N' END,
'Y',
CASE WHEN MONTH(DATUM) IN (3,6,9,12) THEN 'Y' ELSE 'N' END ,
CASE WHEN MONTH(DATUM) = 12 THEN 'Y' ELSE 'N' END,
'N'
FROM ABC
WHERE MONTH(DATUM) IN (3,6,9,12) OR DATUM > CURRENT DATE - 4 MONTH ;

based on the current date, this query will insert:

01.09.2008 Quater End Y Y Y N N
01.08.2008 Month End N Y N N N
01.07.2008 Month End N Y N N N
01.06.2008 Quater End N Y Y N N
01.03.2008 Quater End N Y Y N N
01.12.2007 Quater End N Y Y Y N
01.09.2007 Quater End N Y Y N N
!............................. ! .! .! .! .+-- has_data
!............................. ! .! .! .+-- quarter_year_end
!............................. ! .! .+-- quarter_end
!............................. ! .+-- month_end
!............................. +-- current_month
!...............description
+--reportdt

drsmyth · #3 (**permalink**) 09-09-08, 08:08

Thanks for the help UMAYER, I'm a bit new to this and haven't got a grasp of recursive SQL yet.

Looks a lot neater than my solution...

ARWinner · #4 (**permalink**) 09-09-08, 08:28

Here is a good resource for DB2 SQL:

http://mysite.verizon.net/Graeme_Birchall/id1.html

It covers recursive SQL and a whole lot more.

Andy

drsmyth · #5 (**permalink**) 09-09-08, 10:42

Hi Again UMAYER. I've tried your suggestion and I've tweaked it a bit to give me the results I want, so thanks again for your help...

Quote:

INSERT INTO SESSION.INX_ANDY_DATES
WITH ANDY ( DATETHING ) AS
( SELECT (CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS )
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT B.DATETHING - 1 MONTH FROM SYSIBM.SYSDUMMY1 A ,
ANDY B
WHERE B.DATETHING > CURRENT DATE - 15 MONTHS
)

SELECT
DATETHING ,
CASE WHEN MONTH(DATETHING - 1 MONTH) IN (3,6,9,12) THEN 'Quater End' ELSE 'Month End' END,
CASE WHEN DATETHING = (CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS )
THEN 'Y' ELSE 'N' END,
'Y',
CASE WHEN MONTH(DATETHING - 1 MONTH) IN (3,6,9,12) THEN 'Y' ELSE 'N' END ,
CASE WHEN MONTH(DATETHING - 1 MONTH) = 12 THEN 'Y' ELSE 'N' END,
'N'
FROM ANDY
WHERE MONTH(DATETHING - 1 MONTH) IN (3,6,9,12) OR DATETHING - 1 MONTH > CURRENT DATE - 5 MONTH ;
------------------------------

UPDATE SESSION.INX_ANDY_DATES
SET REPORT_DT = REPORT_DT - 1 DAY

I have 1 more question though, is there a quick way of testing it for dates other than the curren date, I've tried replaceing all the CURRENT DATE'S with '01/05/08' but i'm getting an error..

Any ideas

drsmyth · #6 (**permalink**) 09-09-08, 11:03

Not to worry I've found a solution... (getting a bit carried away with this forum malarky!!!). Just added various numbers of days onto every CURRNET DATE.

umayer · #7 (**permalink**) 09-10-08, 02:19

you can replace CURRENT DATE by
DATE('01/05/2008')

Internet Services

Web Development

Digital Marketing

Consumer Tech