|
kamii47
Constraint Violating Yak Guru
353 Posts |
 Posted - 2011-06-20 : 02:49:22
|
| I need improvement in the following query (created by my colleague)declare @JobID varchar(8)Set @JobID = 'sb001534'SELECT JobID ,-1 as TransactionID ,'-1' as TransactionType ,importjob.fkBranchID ,importjob.fkCompanyID ,fkUserID ,imjModifiedBy ,imjModifiedDate ,CreatedDate AS hdrCreatedDate ,isnull((SELECT COUNT(*) FROM HEADERRECORD WHERE fkjobID = @JobID),0) AS HeaderRecordCount ,SEGFileStatus ,isnull((SELECT COUNT(*) FROM SegmentRecord WHERE fkjobID = @JobID AND segSEGRecordStatus <> 'D'),0) as SEGRecordCount ,MPUFileStatus ,isnull((SELECT COUNT(*) FROM PackageQuantityRecord WHERE fkjobID = @JobID AND pqtPQTRecordStatus <> 'D'),0) as PQTRecordCount ,CSMFileStatus ,isnull((SELECT COUNT(*) FROM ContainerSummaryRecord WHERE fkjobID = @JobID AND csmCSMRecordStatus <> 'D'),0) as CSMRecordCount ,CQTFileStatus ,isnull((SELECT COUNT(*) FROM ContainerQuantityRecord WHERE fkjobID = @JobID AND cqtCQTRecordStatus <> 'D'),0) as CQTRecordCount ,hdrModifiedBy = ISNULL(up16.usrUserName, 'N/A') ,SEGModifiedBy = ISNULL(up1.usrUserName, 'N/A') ,CSMModifiedBy = ISNULL(up7.usrUserName, 'N/A') ,CQTModifiedBy = ISNULL(up8.usrUserName, 'N/A') ,PQTModifiedBy = ISNULL(up6.usrUserName, 'N/A') ,ImportJob.hdrModifiedDate ,isnull(hdrIsPOValid,0) as hdrIsPOValid ,isnull(segIsPOValid,0) as segIsPOValid ,isnull(csmIsPOValid,0) as csmIsPOValid ,isnull(cqtIsPOValid,0) as cqtIsPOValid ,isnull(pqtIsPOValid,0) as pqtIsPOValid ,fkPostalOneValidationStatusId FROM ImportJob left join UserProfile up1 on up1.UserID = ImportJob.SEGModifiedBy left join UserProfile up6 on up6.UserID = ImportJob.PQTModifiedBy left join UserProfile up7 on up7.UserID = ImportJob.CSMModifiedBy left join UserProfile up8 on up8.UserID = ImportJob.CQTModifiedBy left join UserProfile up16 on up16.UserID = ImportJob.imjModifiedBy WHERE JobID = @JobID Kamran ShahidSr. Software Engineer(MCSD.Net,MCPD.net) |
|
Imrpovement suggestion for the query with agregate