Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    5

    Unanswered: Inserting multiple dates into a table

    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:

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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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
    Last edited by umayer; 09-10-08 at 05:04.

  3. #3
    Join Date
    Aug 2008
    Posts
    5
    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...

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2008
    Posts
    5
    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...
    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

  6. #6
    Join Date
    Aug 2008
    Posts
    5
    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.

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    you can replace CURRENT DATE by
    DATE('01/05/2008')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •