Skip to main content
Solved

Week numbers

  • February 26, 2020
  • 6 replies
  • 562 views
D
Sidekick (Customer)
Forum|alt.badge.img+7

Hi ,

I am trying to capture week numbers ( 1/1/20 being week 1 and 31/12/20 being week 52). This is so i can report breakdown time on a week to week bases and then display in a chart for, lest say 10 week and then see a trend.

I have tried the DATEPART command  but with no success.

 

Any suggestions?

 

 

select JT_TASK_UIV.STATE, JT_TASK_UIV.TASK_SEQ, JT_TASK_UIV.ACTUAL_OBJECT_DESCRIPTION, JT_TASK_UIV.REPORTED_DATE, JT_TASK_UIV.ACTUAL_FINISH, JT_TASK_UIV.ERROR_TYPE, JT_TASK_UIV.PERFORMED_WORK, JT_TASK_UIV.SITE, JT_TASK_UIV.ITEM_CLASS_ID, JT_TASK_UIV.ACTUAL_START, JT_TASK_UIV.ACTUAL_OBJECT_ID as "Serial Object", JT_TASK_UIV.PRIORITY_ID, JT_TASK_UIV.DESCRIPTION, EQUIPMENT_SERIAL_UIV.MCH_CODE, EQUIPMENT_SERIAL_UIV.MCH_LOC, EQUIPMENT_SERIAL_UIV.MCH_POS, DATEPART (wk, JT_TASK_UIV.ACTUAL_FINISH) AS week

from &AO.JT_TASK_UIV LEFT OUTER JOIN &AO.EQUIPMENT_SERIAL_UIV ON  &AO.JT_TASK_UIV.ACTUAL_OBJECT_ID = &AO.EQUIPMENT_SERIAL_UIV.MCH_CODE 

WHERE STATE IN ('Work Done','Finished','Released','Started','FaultReport','Work Started')

 

Best answer by anmise

Hi Jouni,

 

Thanks again,

That did not work, i have screen shot the error message

Its a date and time and it is displayed like this,

30/01/2020 10:09:25

 

Dave

Hard to see that tiny screenshot but this works for me.

 

SELECT jt_task_uiv.state,
 jt_task_uiv.task_seq,
 jt_task_uiv.actual_object_description,
 jt_task_uiv.reported_date,
 jt_task_uiv.actual_finish,
 jt_task_uiv.error_type,
 jt_task_uiv.performed_work,
 jt_task_uiv.site,
 jt_task_uiv.item_class_id,
 jt_task_uiv.actual_start,
 jt_task_uiv.actual_object_id AS "Serial Object",
 jt_task_uiv.priority_id,
 jt_task_uiv.description,
 equipment_serial_uiv.mch_code,
 equipment_serial_uiv.mch_loc,
 equipment_serial_uiv.mch_pos,
 TO_CHAR(jt_task_uiv.actual_finish, 'IW') AS week
FROM &ao.jt_task_uiv
LEFT OUTER JOIN &ao.equipment_serial_uiv ON &ao.jt_task_uiv.actual_object_id = &ao.equipment_serial_uiv.mch_code
WHERE state IN ('Work Done', 'Finished', 'Released', 'Started', 'FaultReport', 'Work Started')

If you are using &AO. in SQL Query Tool, make sure to enter ‘IFSAPP.’  (without single quotes) in the variable definition box.

    6 replies

    J
    Sidekick (Partner)
    • Jouni
    • Sidekick (Partner)
    • February 26, 2020

    like this

    select
      to_char(sysdate,'WW-YYYY'),
      to_char(sysdate,'IW-YYYY')
    from dual

     

    Depends on day, those could return different week number ;)

    09-2020    09-2020

    -Jouni-
    D
    Sidekick (Customer)
    Forum|alt.badge.img+7
    • DaveNorman
    • Author
    • Sidekick (Customer)
    • February 26, 2020

    Hi Jouni

     

    I must start by thanking you for your response and help. I must now tell you that i am new to SQL but learning quick.

     

    I only need to get e week number from one date and this need to be a new created column, may be with the AS command

     

    I have a couple of question if i may;

    First question

    In my scenario does to_char = ACTUAL_FINISH?

    Second question

    are you giving me two different solutions or are they one command?

     

    Giving my sequence above how would you insert it?

     

    I have tried every way i can think of and it wont work.

     

    Thank you in advance 

     

     

     

     

    J
    Sidekick (Partner)
    • Jouni
    • Sidekick (Partner)
    • February 26, 2020

    Like this,

    select JT_TASK_UIV.STATE, JT_TASK_UIV.TASK_SEQ, JT_TASK_UIV.ACTUAL_OBJECT_DESCRIPTION, JT_TASK_UIV.REPORTED_DATE, JT_TASK_UIV.ACTUAL_FINISH, JT_TASK_UIV.ERROR_TYPE, JT_TASK_UIV.PERFORMED_WORK, JT_TASK_UIV.SITE, JT_TASK_UIV.ITEM_CLASS_ID, JT_TASK_UIV.ACTUAL_START, JT_TASK_UIV.ACTUAL_OBJECT_ID as "Serial Object", JT_TASK_UIV.PRIORITY_ID, JT_TASK_UIV.DESCRIPTION, EQUIPMENT_SERIAL_UIV.MCH_CODE, EQUIPMENT_SERIAL_UIV.MCH_LOC, EQUIPMENT_SERIAL_UIV.MCH_POS, to_char( JT_TASK_UIV.ACTUAL_FINISH,’WW’) AS week

    from &AO.JT_TASK_UIV LEFT OUTER JOIN &AO.EQUIPMENT_SERIAL_UIV ON  &AO.JT_TASK_UIV.ACTUAL_OBJECT_ID = &AO.EQUIPMENT_SERIAL_UIV.MCH_CODE 

    WHERE STATE IN ('Work Done','Finished','Released','Started','FaultReport','Work Started')


    Problem could be if JT_TASK_UIV.ACTUAL_FINISH is not date
     

    -Jouni-
    D
    Sidekick (Customer)
    Forum|alt.badge.img+7
    • DaveNorman
    • Author
    • Sidekick (Customer)
    • February 26, 2020

    Hi Jouni,

     

    Thanks again,

    That did not work, i have screen shot the error message

    Its a date and time and it is displayed like this,

    30/01/2020 10:09:25

     

    Dave

    A
    Superhero (Employee)
    • anmise
    • Superhero (Employee)
    • Answer
    • February 27, 2020

    Hi Jouni,

     

    Thanks again,

    That did not work, i have screen shot the error message

    Its a date and time and it is displayed like this,

    30/01/2020 10:09:25

     

    Dave

    Hard to see that tiny screenshot but this works for me.

     

    SELECT jt_task_uiv.state,
     jt_task_uiv.task_seq,
     jt_task_uiv.actual_object_description,
     jt_task_uiv.reported_date,
     jt_task_uiv.actual_finish,
     jt_task_uiv.error_type,
     jt_task_uiv.performed_work,
     jt_task_uiv.site,
     jt_task_uiv.item_class_id,
     jt_task_uiv.actual_start,
     jt_task_uiv.actual_object_id AS "Serial Object",
     jt_task_uiv.priority_id,
     jt_task_uiv.description,
     equipment_serial_uiv.mch_code,
     equipment_serial_uiv.mch_loc,
     equipment_serial_uiv.mch_pos,
     TO_CHAR(jt_task_uiv.actual_finish, 'IW') AS week
    FROM &ao.jt_task_uiv
    LEFT OUTER JOIN &ao.equipment_serial_uiv ON &ao.jt_task_uiv.actual_object_id = &ao.equipment_serial_uiv.mch_code
    WHERE state IN ('Work Done', 'Finished', 'Released', 'Started', 'FaultReport', 'Work Started')

    If you are using &AO. in SQL Query Tool, make sure to enter ‘IFSAPP.’  (without single quotes) in the variable definition box.

    D
    D
    Sidekick (Customer)
    Forum|alt.badge.img+7
    • DaveNorman
    • Author
    • Sidekick (Customer)
    • February 27, 2020

    Hi Jouni,

     

    Perfect, that works.

     

    Thank you so much for your help.

     

    Dave

    Terms & ConditionsCookie settingsAccessibility statement