Solved

Week numbers

  • 26 February 2020
  • 6 replies
  • 461 views

Userlevel 3
Badge +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')

 

icon

Best answer by anmise 27 February 2020, 05:31

View original

6 replies

Userlevel 4

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

Userlevel 3
Badge +7

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 

 

 

 

 

Userlevel 4

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
 

Userlevel 3
Badge +7

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

Userlevel 7

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.

Userlevel 3
Badge +7

Hi Jouni,

 

Perfect, that works.

 

Thank you so much for your help.

 

Dave

Reply