quote:

---

SELECT d.guserid ,
d.iReadInWith ,
d.dTransferStartDate ,
SUM(CASE WHEN efiletype = 129
OR efiletype = 128 THEN 1
ELSE 0
END) AS DCefiletype ,
( SUM(CASE WHEN efiletype = 129
OR efiletype = 128
THEN CONVERT(DECIMAL, lOriginalFileSize)
ELSE 0
END) / 1048576 ) AS DClFileSize ,
SUM(CASE WHEN efiletype = 130 THEN 1
ELSE 0
END) AS VUefiletype ,
( SUM(CASE WHEN efiletype = 130
THEN CONVERT(DECIMAL, lOriginalFileSize)
ELSE 0
END) / 1048576 ) AS VUlFileSize ,
SUM(CASE WHEN efiletype = 131 THEN 1
ELSE 0
END) AS VDOefiletype ,
( SUM(CASE WHEN efiletype = 131
THEN CONVERT(DECIMAL, lOriginalFileSize)
ELSE 0
END) / 1048576 ) AS VDOlFileSize
INTO #FileTransfer2
FROM #FileTransfer d
GROUP BY d.guserid ,
d.iReadInWith ,
d.dTransferStartDate

SELECT e.iReadInWith AS isourceid ,
'1' iSP ,
'SP' sSP ,
COALESCE(DCefiletype, 0) DCefiletype ,
COALESCE(DClFileSize, 0) DClFileSize ,
COALESCE(VUefiletype, 0) VUefiletype ,
COALESCE(VUlFileSize, 0) VUlFileSize ,
COALESCE(VDOefiletype, 0) VDOefiletype ,
COALESCE(VDOlFileSize, 0) VDOlFileSize
FROM #FileTransfer e
LEFT JOIN #FileTransfer2 f ON e.guserid = f.guserid
AND e.iReadInWith = f.iReadInWith
AND CAST (e.dTransferStartDate AS DATETIME) = CAST (f.dTransferStartDate AS DATETIME)
INNER JOIN Users u ON e.gUserID = u.gUserID
INNER JOIN Account a ON u.lAccountID = a.lAccountID
INNER JOIN InvoiceOrganisation SP ON a.lInvoiceOrganisation = sp.lInvoiceOrganisation
AND ( CHARINDEX(','
+ LTRIM(RTRIM(CAST(InvoiceOrganisation.lInvoiceOrganisation AS VARCHAR)))
+ ',',
','
+ LTRIM(RTRIM(@sSPID))
+ ',') > 0 )
WHERE e.iReadInWith > 0
AND e.eState = 3
AND e.eTransferDirection = 1
AND eFileType IN ( 128, 129, 130, 131 )

---