Change the value in a resulting set of a query in Oracle

[caloy007]

Hi everyone! I am pretty much new to Oracle it has only been 3 months or so I guess and I have a bit of a problem now. I'm running a query which will display the downtime of a machine. It will display the Start/End Time and its difference. The maching runs in two shifts, Shift A being 6:00 AM to 6:00 PM and Shift B on 6:00 PM to 6:00 AM. Below is a sample resulting set of my query:

___________________________________________________________________________________________

Shift StartDateTime EndDateTime DifferenceInMinutes
B 09/25/2012 17:38:40 09/25/2012 19:12:26 93.7667
B 09/25/2012 19:12:26 09/25/2012 19:25:03 12.6167
B 09/25/2012 19:25:03 09/26/2012 05:55:08 630.0833
A 09/26/2012 05:55:08 09/26/2012 09:19:55 24.8

___________________________________________________________________________________________
The total in DifferenceInMinutes should always be 720 per shift. What I did is that on each start of a shift, it'll be 6:00 AM for Shift A and 18:00 on Shift B. I used Case When to be able to do this. Below will be another example of the resulting set:

___________________________________________________________________________________________

Shift StartDateTime EndDateTime DifferenceInMinutes
B 09/25/2012 18:00:00 09/25/2012 19:12:26 72.43333
B 09/25/2012 19:12:26 09/25/2012 19:25:03 12.6167
B 09/25/2012 19:25:03 09/26/2012 05:55:08 630.0833
A 09/26/2012 06:00:00 09/26/2012 09:19:55 199.9167

___________________________________________________________________________________________

Now my only problem is the EndDateTime, the last item on a shift should always end at 18:00 for Shift A and 6:00 for Shift B. I want to achieve something like below:

___________________________________________________________________________________________

Shift StartDateTime EndDateTime DifferenceInMinutes
B 09/25/2012 18:00:00 09/25/2012 19:12:26 72.43333
B 09/25/2012 19:12:26 09/25/2012 19:25:03 12.6167
B 09/25/2012 19:25:03 09/26/2012 06:00:00 634.95
A 09/26/2012 06:00:00 09/26/2012 09:19:55 199.9167

___________________________________________________________________________________________

I must admit that I do not know how to use Case When like what I have done on StartDateTime so I'm really hoping that there is a way for this. Thanks guys. I hope I made sense here. Cheers :)

[caloy007]

This is what I have so far:

SELECT 
DISTINCT Hm.Txndate
, hm.shiftname AS Shift
, rd.resourcename AS TesterID
, case
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'
    else to_char(Rsh.Oldlaststatuschangedate)
  end as StartDateTime
, lead(hm.shiftname, 1, 0) over (partition by hm.txndate order by rd.resourcename, c.containername, hm.txndate, hm.shiftname) as NextShift
, to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' ' || to_char(Rsh.Laststatuschangedate, 'HH24:MI:SS') as EndDateTime
, ROUND((((TO_DATE(rsh.laststatuschangedate, 'MM/DD/YYYY HH24:MI:SS') - TO_DATE(case
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'
    else to_char(Rsh.Oldlaststatuschangedate)
  end)) * 24) * 60), 4) AS TotalMinutes
, Rsh.Newstatusname AS Status
, Rsh.Newreasonname AS StatusReason
, c.containername AS Lot
, pb.productname AS ProductName
, aep.paramvalue AS HandlerID
, rd.attr_09 AS HandlerType
, (SELECT e.employeename AS OperatorID FROM employee e WHERE e.employeeid = hm.employeeid) AS OperatorID
, (SELECT e.fullname AS EmployeeName FROM employee e WHERE e.employeeid = hm.employeeid) AS EmployeeName
, hm.comments AS Remarks
FROM Historymainline hm
INNER JOIN Resourcestatushistory rsh ON hm.Historymainlineid = rsh.Historymainlineid
INNER JOIN ResourceDef rd ON rd.resourceid = rsh.historyid
INNER JOIN a_equipmentparams aep ON rd.resourceid = aep.resourceid
INNER JOIN a_paramname apn ON aep.paramnameid = apn.paramnameid AND apn.paramnamename ='HANDLERID'
INNER JOIN A_WIPEquipment awe ON rd.resourceid = awe.equipmentid
INNER JOIN container c ON c.ContainerId = awe.ContainerId
INNER JOIN Product p ON c.productid = p.productid
INNER JOIN productbase pb ON p.productbaseid = pb.productbaseid
WHERE rd.Resourcename = :equipID
ORDER BY  c.containername, hm.txndate;

[caloy007]

I have solved this problem using the query below:

SELECT 
DISTINCT Hm.Txndate
, hm.shiftname AS Shift
, rd.resourcename AS TesterID
, case
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'
    else to_char(Rsh.Oldlaststatuschangedate)
  end as StartDateTime
, case
    when lead(hm.shiftname, 2, 0) over (order by c.containername, hm.txndate) <> hm.shiftname then 
     case
        when lead(hm.shiftname, 2, 0) over (order by c.containername, hm.txndate) = 'A' then to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'
        when lead(hm.shiftname, 2, 0) over (order by c.containername, hm.txndate) = 'B' then to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'
      end
    else to_char(Rsh.Laststatuschangedate)
  end as EndDateTime
, ROUND((((TO_DATE(case
    when lead(hm.shiftname, 2, 0) over (order by c.containername, hm.txndate) <> hm.shiftname then 
     case
        when lead(hm.shiftname, 2, 0) over (order by c.containername, hm.txndate) = 'A' then to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'
        when lead(hm.shiftname, 2, 0) over (order by c.containername, hm.txndate) = 'B' then to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'
      end
    else to_char(Rsh.Laststatuschangedate)
  end) - TO_DATE(case
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 06:00:00'
    when Rsh.Oldlaststatuschangedate < to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00' and Rsh.Laststatuschangedate > to_char(Rsh.Laststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'  then to_char(Rsh.Oldlaststatuschangedate, 'MM/DD/YYYY') || ' 18:00:00'
    else to_char(Rsh.Oldlaststatuschangedate)
  end)) * 24) * 60), 4) AS TotalMinutes
, Rsh.Newstatusname AS Status
, Rsh.Newreasonname AS StatusReason
, c.containername AS Lot
, pb.productname AS ProductName
, aep.paramvalue AS HandlerID
, rd.attr_09 AS HandlerType
, (SELECT e.employeename AS OperatorID FROM employee e WHERE e.employeeid = hm.employeeid) AS OperatorID
, (SELECT e.fullname AS EmployeeName FROM employee e WHERE e.employeeid = hm.employeeid) AS EmployeeName
, hm.comments AS Remarks
FROM Historymainline hm
INNER JOIN Resourcestatushistory rsh ON hm.Historymainlineid = rsh.Historymainlineid
INNER JOIN ResourceDef rd ON rd.resourceid = rsh.historyid
INNER JOIN a_equipmentparams aep ON rd.resourceid = aep.resourceid
INNER JOIN a_paramname apn ON aep.paramnameid = apn.paramnameid AND apn.paramnamename ='HANDLERID'
INNER JOIN A_WIPEquipment awe ON rd.resourceid = awe.equipmentid
INNER JOIN container c ON c.ContainerId = awe.ContainerId
INNER JOIN Product p ON c.productid = p.productid
INNER JOIN productbase pb ON p.productbaseid = pb.productbaseid
WHERE rd.Resourcename = :equipID
ORDER BY  c.containername, hm.txndate;

[caloy007]

But there are still errors at the end of the resulting set >.<