SELECT o.DocumentID, o.DocumentReceivedTime, o.DocumentReferenceNo, aro.RelatedDocRefNo, abs(datediff(minute, o.DocumentReceivedTime, GETUTCDATE())) AS TimeSince
FROM [BISDW1D].[dbo].Documents o
LEFT JOIN [BISDW1D].[dbo].Documents aro
ON o.DocumentReferenceNo = SUBSTRING(REPLACE(aro.DocumentReferenceNo, 'APVORD-', ''), 0, (
CASE
WHEN CHARINDEX('-', REPLACE(aro.DocumentReferenceNo, 'APVORD-', '')) = 0
THEN len(aro.DocumentReferenceNo) + 1
ELSE CHARINDEX('-', REPLACE(aro.DocumentReferenceNo, 'APVORD-', ''))
END
))
AND o.Sender = aro.Sender
LEFT JOIN [BISDW1D].[dbo].Trading_Partner supp
ON supp.TPID = o.Recipient
LEFT JOIN [BISDW1D].[dbo].Trading_Partner ph
ON ph.TPID = o.Sender
WHERE o.MessageTypeID = 2
AND o.Environment = 1
AND o.DocumentReceivedTime < DATEADD(MINUTE, -3, GETUTCDATE())
AND aro.DocumentID IS NULL
AND o.DocumentReceivedTime > DATEADD(HOUR, -12, GETUTCDATE())
AND aro.MessageTypeID = 5
--AND o.DocumentID NOT IN (203309, 217893) --blocked duplicates
ORDER BY o.DocumentID DESC
So far what i have done is not giving result can anybody suggest me how to improve it?
var query = (from a in db.Documents
join b in db.Documents on new { q = a.DocumentReferenceNo, y = a.Sender } equals new { q= b.DocumentReferenceNo.Replace( "APVORD-", ""),(SqlFunctions.CharIndex( "-" && b.DocumentReferenceNo.Replace( "APVORD-", "")== "0" ? b.DocumentReferenceNo.Length+1 )),y=b.Sender}
join c in db.Trading_Partner on a.Recipient equals c.TPID
join d in db.Trading_Partner on a.Sender equals d.TPID
where a.MessageTypeID == 2 && a.Environment == 1 && b.DocumentID == null && b.MessageTypeID==5
select new Bivir
{
DocumentID = a.DocumentID,
DocumentReceivedTime = a.DocumentReceivedTime,
DocumentReferenceNo = a.DocumentReferenceNo,
RelatedDocRefNo = b.DocumentReferenceNo,
});