Hi,

I'm looking for a report that contains all requests that were ever in a certain group regardless if they were reassigned to another group before they were closed at a certain time.

 

For instance all request assigned to Helpdesk group on Monday26 november 2012 ?

Thanks,
Eric

 

PS the following is not working because I do not return ticket that was assigned to my group previous to '2012-08-18 00:00:00.000'

 

DECLARE

@currentDay DATETIME , @histoStartDate DATETIME

SELECT

@histoStartDate = '2012-08-18 00:00:00.000' --history starting date

SELECT

@currentDay = DATEADD ( d , 1 , @histoStartDate )

select

@currentDay ;

SELECT distinct 1 as [Type] ,

wo

. WORKORDERID

, dbo . [longtodate] ( woh . operationtime ) AS udate

, @currentDay as [Date]

FROM serviceDesk . workorder wo

LEFT

JOIN serviceDesk . workorderhistory woh ON wo . workorderid = woh . workorderid

LEFT

JOIN serviceDesk . workorderhistorydiff wohd ON woh . historyid = wohd . historyid

LEFT

JOIN serviceDesk . queuedefinition qd ON cast ( qd . queueid AS VARCHAR ) = cast ( wohd . prev_value AS VARCHAR )

LEFT

JOIN serviceDesk . queuedefinition qdx ON cast ( qdx . queueid AS VARCHAR ) = cast ( wohd . current_value AS VARCHAR ) WHERE qdx . queuename IN ( 'Management' , 'Infrastructure' , 'Applications' )

AND dbo . [longtodate] ( woh . operationtime ) between @currentDay and DATEADD ( d , 1 , @currentDay )

ORDER

BY 1 , 2