Results 1 to 9 of 9

Thread: Concatenate

  1. #1
    Join Date
    Oct 2014
    Posts
    4

    Unanswered: Concatenate

    I have following data:
    DAYS OPENHR CLOSED
    SATURDAY 08:00:00.0000000 17:00:00.0000000
    FRIDAY 09:00:00.0000000 16:00:00.0000000
    SUNDAY 10:00:00.0000000 16:00:00.0000000
    MONDAY 08:00:00.0000000 17:00:00.0000000
    WEDNESDAY 08:00:00.0000000 17:00:00.0000000
    TUESDAY 09:00:00.0000000 17:00:00.0000000
    Now, I want result like this:
    mon-thu: 8-5
    fri :9-4
    sat:8-5
    can somebody help me ?? i'm using db2 database.

    Thanks,
    mike

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions.

    (1) Only 6 rows data?

    (2) Ignore SUNDAY?

    (3)
    mon-thu: 8-5
    From earliest OPENHR to latest CLOSED?

  3. #3
    Join Date
    Oct 2014
    Posts
    4
    Hi There,
    its just a sample data and i can not ignore sunday .
    Also those DAYS,OPEHHR AND CLOSED are column name.

    Thanks,

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    its just a sample data and i can not ignore sunday
    (1) Your sample data include sunday. But, your wanted result didn't include sunday.
    DAYS OPENHR CLOSED
    SATURDAY 08:00:00.0000000 17:00:00.0000000
    FRIDAY 09:00:00.0000000 16:00:00.0000000
    SUNDAY 10:00:00.0000000 16:00:00.0000000
    MONDAY 08:00:00.0000000 17:00:00.0000000
    WEDNESDAY 08:00:00.0000000 17:00:00.0000000
    TUESDAY 09:00:00.0000000 17:00:00.0000000
    Now, I want result like this:
    mon-thu: 8-5
    fri :9-4
    sat:8-5
    (2) Please supply more sample data. Hopefully, more than 8 additional rows.
    And expected result from the sample data.

  5. #5
    Join Date
    Oct 2014
    Posts
    4
    Hi Tonkuma ,
    you can exclude sunday.

    clerk_name DAYS OPENHR CLOSED
    A SATURDAY 08:00:00 17:00:00
    A FRIDAY 09:00:00 16:00:00
    A MONDAY 08:00:00 17:00:00
    A WEDNESDAY 08:00:00 17:00:00
    A TUESDAY 09:00:00 17:00:00


    clerk_name DAYS OPENHR CLOSED
    B SATURDAY 08:00:00 17:00:00
    B FRIDAY 09:00:0 16:00:00
    B MONDAY 08:00:00 17:00:00
    B WEDNESDAY 08:00:00 17:00:00
    B TUESDAY 09:00:00 17:00:00



    clerk_name DAYS OPENHR CLOSED
    C SATURDAY 08:00:00 17:00:00
    C FRIDAY 09:00:00 16:00:00
    C MONDAY 08:00:00 17:00:00
    C WEDNESDAY 08:00:00 17:00:00
    C TUESDAY 08:00:00 17:00:00

    Also I have added one more column name which is clerk_name. I guess the questions is clear
    Now, I want result like this:
    mon-thu: 8-5
    fri :9-4
    sat:8-5

    thanks,
    Last edited by mike2014; 10-23-14 at 11:57.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    All clerk's hours(OPENHR and CLOSED) are same for a DAYS, in your last example.

    (1) If always so,
    I want to recommend you to revise design of table(s), like...
    schedule_table:
    DAYS OPENHR CLOSED
    SATURDAY 08:00:00.0000000 17:00:00
    FRIDAY 09:00:00.0000000 16:00:00
    MONDAY 08:00:00.0000000 17:00:00
    WEDNESDAY 08:00:00.0000000 17:00:00
    TUESDAY 09:00:00.0000000 17:00:00

    clerk_table:
    clerk_name
    A
    B
    C

    Your last sample data can be generated by cross joining the two tables(schedule_table and clerk_table).


    (2) or if that was not always so,
    please publish such sample data and expected result from the sample.
    Last edited by tonkuma; 10-23-14 at 12:07.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There is quite a bit to your request. Lets see if we can't point you in the proper direction for some of it. Unless you have a billing address for one of us to do it for you.

    The time format that you are looking for can be gotten to fairly easily with:
    Code:
    CHAR(TIME(CURRENT TIMESTAMP),USA)
    and a SUBSTR function. Would you have to take into account a HALF hour? like 08:30:00.000000?

    Next item is your days. Looks like you aren't using normal abbreviation as you have THU instead of THUR, so, looks like again another job for SUBSTR and maybe LOWERCASE???

    Once you have that , then it will be time to get into some recursion to find consecutive days with the same times to get the MIN and the MAX day with those and maybe we can throw in a DAYOFWEEK function to determine that MIN and MAX and if those days were consecutive or not.
    Dave

  8. #8
    Join Date
    Oct 2014
    Posts
    4
    Thanks, Dave !
    CHAR(TIME(CURRENT TIMESTAMP),USA) it works perfectly but i dont know why alttime is not working?

    SELECT, CLERK_NAME,
    (CASE
    WHEN DAYS='SUNDAY' THEN 'SUN'
    WHEN DAYS= 'MONDAY' THEN 'MON'
    WHEN DAYS= 'TUESDAY'THEN 'TUE'
    WHEN DAYS= 'WEDNESDAY'THEN 'WED'
    WHEN DAYS= 'THURSDAY'THEN 'THUR'
    WHEN DAYS= 'FRIDAY' THEN 'FRI'
    WHEN DAYS= 'SATURDAY' THEN 'SAT'
    ELSE 'UNKNOWN'
    END YYY )|| '-'||CHAR(TIME(OPENHR),USA)||'-'||CHAR(TIME(CLOSED),USA) AS OFFICEHOURS,
    CASE
    WHEN DAYS= 'SUNDAY' THEN '1'
    WHEN DAYS= 'MONDAY' THEN '2'
    WHEN DAYS= 'TUESDAY' THEN '3'
    WHEN DAYS= 'WEDNESDAY'THEN'4'
    WHEN DAYS= 'THURSDAY' THEN '5'
    WHEN DAYS= 'FRIDAY' THEN '6'
    WHEN DAYS= 'SATURDAY' THEN '7'
    ELSE 'UNKNOWN'
    END AS AAA
    FROM ABC
    ORDER BY AAA




    anyway i got my result in this form i.e,
    Sun: 8am-5pm
    Mon: 8am-5pm
    Tue: 10am-5pm
    Wed: 8am-5pm
    Thur: 9am-6pm
    Fri: 10am-5pm
    Sat: 10am-3pm

    but actually I want result like this :
    Sun,Mon,Wed- 8am-5pm
    Tue,fri-10am-5pm
    sat:10am-3pm

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    hi, mike
    The following code is not well enough to your requirement and is only for your reference:
    WITH
    week_decode
    (
    code,
    name
    ) AS
    (
    VALUES
    (
    1,
    'SUNDAY'
    )
    ,
    ( 2, 'MONDAY' ),
    ( 3, 'TUESDAY' ),
    ( 4, 'WEDNESDAY' ),
    ( 5, 'THURSDAY' ),
    ( 6, 'FRIDAY' ),
    ( 7, 'SATURDAY' )
    )
    ,
    clerk_time
    (
    days,
    openhr,
    closed
    ) AS
    (
    VALUES
    (
    'SATURDAY',
    '08:00:00',
    '17:00:00'
    )
    ,
    ( 'FRIDAY', '09:00:00', '16:00:00' ),
    ( 'MONDAY', '08:00:00', '17:00:00' ),
    ( 'WEDNESDAY', '08:00:00', '17:00:00' ),
    ( 'TUESDAY', '09:00:00', '17:00:00' )
    )
    SELECT
    listagg(days,',') within GROUP ( ORDER BY code ) ,
    officehours
    FROM
    (
    SELECT
    a.days,
    b.code ,
    CHAR(TIME(openhr),USA)||'-'||CHAR(TIME(closed),USA) AS OFFICEHOURS
    FROM
    clerk_time a,
    week_decode b
    WHERE
    a.days=b.name )
    GROUP BY
    OFFICEHOURS

Posting Permissions

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