/*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
murthykk 0 Newbie Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.