Hello guys,

Thanks you for your reply. This is the definition of the sp. The data is taken from a ingress db. Unfotonately I don't have acces to the tables.

CREATE PROCEDURE [dbo].[usp_RPT_HSG_PropertyRepairsHistoryTSL]

(

@PropertyReferenceNumberNVARCHAR (max),

@StartDateDATETIME,

@EndDateDATETIME,

@GetCHRepTypeNVARCHAR (20)

)

AS

BEGIN

SET NOCOUNT ON

-- ===================================================

-- Execute no-lock expression at named linked servers

-- ===================================================

EXECUTE ('set lockmode session where readlock=nolock') AT INGRES2

DECLARE @PropertyRepairsHistory Table

(

JobnumberINT

,PropertyReferenceNVARCHAR (max)

,JobTitlenvarchar (60)

,Prioritynvarchar (20)

,JobTypenvarchar (20)

,Contractorcodenvarchar (4)

,Contractornvarchar (30)

,BilledValueMONEY

,ValuePaid MONEY

,StageHistDateDATE

,JobType2NVARCHAR (20)

)

INSERT INTO @PropertyRepairsHistory

Select * FROM OPENQUERY (ingres2, '

SELECT repheader.repairno

, repheader.propref

, repheader.repairdesc

, priority.decode

, reptype.code

, workforce.wforcecode

, workforce.wforcename

, worderline.billval

, worderline.payval

, statehist.statedate

, reptype.decode AS JobType

FROM((repheader INNER JOIN workorder ONrepheader.repairno = workorder.repairno) inner JOIN worderlineON workorder.repairno = worderline.repairno AND workorder.repsubno = worderline.repsubno)

LEFT OUTER JOIN reptype ON reptype.code = repheader.reptypenow

LEFT OUTER JOIN priorityONpriority.code = workorder.prioritycode

LEFT OUTER JOIN statehistON statehist.repaltkey = repheader.repairno

LEFT OUTER JOIN workforceON repheader.wforcecode = workforce.wforcecode

WHERE statehist.repstatecode = 99

')

DECLARE @Property Table

(

PropertyRefnvarchar(max)

,HouseNoNVARCHAR (4)

,AddressLine1nvarchar (30)

,AddressLine2nvarchar (30)

,AddressLine3nvarchar (30)

,Postcodenvarchar (8)

,Officenamenvarchar (max)

,Officecodenvarchar (max)

)

INSERT INTO @Property

Select * FROM OPENQUERY (ingres2, '

SELECT

propfixed.propref

,propfixed.houseno

, propfixed.proadd1

, propfixed.proadd2

, propfixed.proadd3

, propfixed.propstcde

,mnode.mnodename

, propfixed.mnodecode

FROM propfixedinner join mnode ON propfixed.mnodecode = mnode.code

' )

-- =======================

-- OutPut for Detail

-- =======================

SELECT *

FROM @PropertyRepairsHistory r inner join @Property P on R.PropertyReference = P.PropertyRef

WHERE r.StageHistDate BETWEEN @StartDate AND @EndDate

AND r.JobType IN (SELECT * FROM fn_CSVToTable(@GetCHRepType))

AND p.PropertyRef = @PropertyReferenceNumber

ORDER BY Jobnumber DESC

Thanks,

Nubia