/*I have a situation where in I have to convert the TSQL code to PLSQL procedure. For the purpose of better understanding, I have created temp tables in place of source tables. Can I get hep on this please. 
    The code is as follows: */


---create temp tables

CREATE TABLE #ticket (
TicketID int,
TicketDesc Varchar(100),
ReportDate Datetime,
ClosedDate Datetime
 )

CREATE TABLE #Owner (
TicketID int,
Owner Varchar(100),
OwnerGroup Varchar(100),
OwnDate datetime
)

CREATE TABLE #User (
UserID int,
ResParty Varchar(100),
PersonGroup Varchar(100)
)


--- insert data into temp tables similar to socurce tables
Insert into #User(UserID, ResParty, PersonGroup) Values
(1, 'JOEWILLIAMS', 'MXTEAM')
Insert into #User(UserID, ResParty, PersonGroup) Values
(2, 'BCOMO', 'EMAXADMIN')
Insert into #User(UserID, ResParty, PersonGroup) Values
(3, 'MLEASE', 'EMAXADMIN')




Insert into #ticket (ticketID, TicketDesc, Reportdate, ClosedDate) Values
(1510, 'Replace toner in Finance Copier', '1/21/2014', '1/26/2014')
Insert into #ticket (ticketID, TicketDesc, Reportdate, ClosedDate) Values
(1511, 'My recyling box needs emptying', '1/23/2014', '1/29/2014')
Insert into #ticket (ticketID, TicketDesc, Reportdate, ClosedDate) Values
(1512, 'PRIM-BUS System is in the wrong Org', '1/29/2014', '1/31/2014')


Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1510, 'JOEWILLIAMS', NULL, '1/21/2014 08:00:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1510, 'BCOMO', NULL , '1/21/2014 09:00:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1510, 'MLEASE', NULL , '1/21/2014 10:30:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1510, NULL,  'MXTEAM', '1/21/2014 10:40:00')


Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1511, 'JOEWILLIAMS', NULL,  '1/23/2014 07:00:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1511, NULL,  'MXTEAM', '1/23/2014 07:15:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1511, 'BCOMO', NULL,  '1/23/2014 07:30:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1511, 'MLEASE', NULL, '1/23/2014 08:30:00')

Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1512, 'JOEWILLIAMS', NULL,  '1/29/2014 08:00:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1512, NULL,  'EMAXADMIN', '1/29/2014 08:15:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1512, NULL,  'MXTEAM', '1/29/2014 08:30:00')
Insert into #Owner (TicketID, Owner, OwnerGroup, OwnDate) values
(1512, 'MLEASE', NULL, '1/29/2014 11:30:00')

----Combine Owner and Ownergroup in one column

Select t.TicketID
,t.ReportDate
,t.ClosedDate
,o.OwnDate
,Case when o.Owner like '' OR o.Owner Is Null then o.OwnerGroup else o.owner end as Owners
,Row_Number() OVER (partition by t.TicketID Order by o.OwnDate )as RowNumber
into #ticketdetails
from #ticket t inner join #Owner o on t.TicketID = o.ticketID

/*
The result set of data for the above query:
Here the row number is assigned  and grouped  based on the ticket number using partition by clause and ordered by owndate
data from the the table #ticketdetails are as follows:
select * from #ticketdetails

1510    2014-01-21 00:00:00.000 2014-01-26 00:00:00.000 2014-01-21 08:00:00.000 JOEWILLIAMS 1
1510    2014-01-21 00:00:00.000 2014-01-26 00:00:00.000 2014-01-21 09:00:00.000 BCOMO       2
1510    2014-01-21 00:00:00.000 2014-01-26 00:00:00.000 2014-01-21 10:30:00.000 MLEASE      3
1510    2014-01-21 00:00:00.000 2014-01-26 00:00:00.000 2014-01-21 10:40:00.000 MXTEAM      4

1511    2014-01-23 00:00:00.000 2014-01-29 00:00:00.000 2014-01-23 07:00:00.000 JOEWILLIAMS 1
1511    2014-01-23 00:00:00.000 2014-01-29 00:00:00.000 2014-01-23 07:15:00.000 MXTEAM      2
1511    2014-01-23 00:00:00.000 2014-01-29 00:00:00.000 2014-01-23 07:30:00.000 BCOMO       3
1511    2014-01-23 00:00:00.000 2014-01-29 00:00:00.000 2014-01-23 08:30:00.000 MLEASE      4

1512    2014-01-29 00:00:00.000 2014-01-31 00:00:00.000 2014-01-29 08:00:00.000 JOEWILLIAMS 1
1512    2014-01-29 00:00:00.000 2014-01-31 00:00:00.000 2014-01-29 08:15:00.000 EMAXADMIN   2
1512    2014-01-29 00:00:00.000 2014-01-31 00:00:00.000 2014-01-29 08:30:00.000 MXTEAM      3
1512    2014-01-29 00:00:00.000 2014-01-31 00:00:00.000 2014-01-29 11:30:00.000 MLEASE      4


*/

----Total Time ticket assigned to groups other than MXTEAM and members of MXTEAM.


    select    myTicket, SUM(IsNull(TimeSpanInHrs, 0))  as TotalTicketHrs


from
(
            select      
            cur.TicketID                  as      myTicket
            ,cur.OwnDate                  as      CurrentTaskDateTime
            ,prev.OwnDate                 as      PreviousTaskDateTime
            ,cur.Owners                   as      CurrentOwner
            ,Prev.Owners                  AS      PreviousOwner
            ,Case when
            not prev.Owners in (Select ResParty from #User Where PersonGroup like 'MXTEAM') ---any members that are NOT in MXTEAM
            and not prev.Owners like 'MXTEAM' ---anygroup that is NOT MXTEAM
            then DATEDIFF(n, Prev.OwnDate, Cur.OwnDate) ---Difference in minutes between previous assigned time to current assigned time
            else Null end  as TimeSpanInHrs

            from #ticketdetails cur
            left outer join #ticketdetails prev 
            on prev.ticketID = cur.TicketID and (prev.RowNumber + 1) = cur.RowNumber

) TicketDetailWithTimeSpan
   group by myTicket



/* Comments:the result set for the subquery in the above query
 Choose the subquery from the above:

select      
            cur.TicketID                  as      myTicket
            ,cur.OwnDate                  as      CurrentTaskDateTime
            ,prev.OwnDate                 as      PreviousTaskDateTime
            ,cur.Owners                   as      CurrentOwner
            ,Prev.Owners                  AS      PreviousOwner
            ,Case when
            not prev.Owners in (Select ResParty from #User Where PersonGroup like 'MXTEAM') ---any members that are NOT in MXTEAM
            and not prev.Owners like 'MXTEAM' ---anygroup that is NOT MXTEAM
            then DATEDIFF(n, Prev.OwnDate, Cur.OwnDate) ---Difference in minutes between previous assigned time to current assigned time
            else Null end  as TimeSpanInHrs

            from #ticketdetails cur
            left outer join #ticketdetails prev 
            on prev.ticketID = cur.TicketID and (prev.RowNumber + 1) = cur.RowNumber


The data is as follows:
In the above query calculates timespan for each ticket for the previous owners not in mxteam. 
The query calculates this time span using a self join with the same table # ticket details

myTicketCurrentTaskDateTime PreviousTaskDateTime    CurrentOwner    PreviousOwner   TimeSpanInHrs
1510    2014-01-21 08:00:00.000 NULL            JOEWILLIAMS NULL        NULL
1510    2014-01-21 09:00:00.000 2014-01-21 08:00:00.000 BCOMO       JOEWILLIAMS NULL
1510    2014-01-21 10:30:00.000 2014-01-21 09:00:00.000 MLEASE      BCOMO       90
1510    2014-01-21 10:40:00.000 2014-01-21 10:30:00.000 MXTEAM      MLEASE      10

1511    2014-01-23 07:00:00.000 NULL            JOEWILLIAMS NULL        NULL
1511    2014-01-23 07:15:00.000 2014-01-23 07:00:00.000 MXTEAM      JOEWILLIAMS NULL
1511    2014-01-23 07:30:00.000 2014-01-23 07:15:00.000 BCOMO       MXTEAM      NULL
1511    2014-01-23 08:30:00.000 2014-01-23 07:30:00.000 MLEASE      BCOMO       60

1512    2014-01-29 08:00:00.000 NULL            JOEWILLIAMS NULL        NULL
1512    2014-01-29 08:15:00.000 2014-01-29 08:00:00.000 EMAXADMIN   JOEWILLIAMS NULL
1512    2014-01-29 08:30:00.000 2014-01-29 08:15:00.000 MXTEAM      EMAXADMIN   15
1512    2014-01-29 11:30:00.000 2014-01-29 08:30:00.000 MLEASE      MXTEAM      NULL

*/
/* Comments:
The final query above  calculates the sum of the time span for each ticket ID:
the data  is as follows:
myTicket    TotalTicketHrs
1510    100  -- This is 90+10 in above row set
1511    60    ---This is 60  in above row set
1512    15    --- This is 15 in the above row set

*/

Drop table #Owner
Drop table #ticket
Drop table #User
Drop table #ticketdetails