ALTER PROCEDURE MKTG_DiffeRenceDailyReport @CustomerName varchar(100), @ShipDate datetime AS BEGIN Set NoCount ON DECLARE @NoReceivedPackages varchar(2000), @CurrBillTrackId varchar(30), @CurrPackageId varchar(30) SELECT a.BillTrackId, a.Pieces, (case isnull(a.Canceled, 0) when 1 then '√' end) as Canceled, c.FacilityName AS CustomerName, a.ShipDate, CAST('' as varchar(2000)) AS NoReceivedPackageIdList, COUNT(b.PackageTrackId) AS ReceivedPieces INTO #t1 FROM MKTG_ShipBills a LEFT JOIN MKTG_ShipmentsCheckin b ON a.BillTrackId = b.BillTrackId LEFT JOIN Common_Facilities c ON a.CustomerId = c.FacilityId WHERE c.FacilityName = @CustomerName AND a.ShipDate = @ShipDate GROUP BY a.BillTrackId, a.Pieces, a.Canceled, c.FacilityName, a.ShipDate ORDER BY a.BillTrackId DECLARE c1 CURSOR FOR SELECT BillTrackId FROM #t1 OPEN c1 FETCH NEXT FROM c1 INTO @CurrBillTrackId WHILE @@FETCH_STATUS = 0 BEGIN SELECT a.PackageTrackId INTO #t2 FROM MKTG_ShipPackages a left join MKTG_ShipmentsCheckin b ON a.BillTrackId = b.BillTrackId left join MKTG_ShipBills d ON a.BillTrackId = d.BillTrackId LEFT JOIN Common_Facilities c ON c.FacilityId = d.CustomerId WHERE a.PackageTrackId not in (SELECT PackageTrackId FROM MKTG_ShipmentsCheckin) and c.FacilityName = @CustomerName AND d.ShipDate = @ShipDate and a.BillTrackId = @CurrBillTrackId SET @NoReceivedPackages = '' DECLARE c2 CURSOR FOR SELECT PackageTrackId FROM #t2 OPEN c2 FETCH NEXT FROM c2 INTO @CurrPackageId WHILE @@FETCH_STATUS = 0 BEGIN SET @NoReceivedPackages = @NoReceivedPackages + ';' + @CurrPackageId FETCH NEXT FROM c2 INTO @CurrPackageId END SET @NoReceivedPackages = SUBSTRING(@NoReceivedPackages, 2, LEN(@NoReceivedPackages)) UPDATE #t1 SET NoReceivedPackageIdList = @NoReceivedPackages WHERE BillTrackId = @CurrBillTrackId; DROP TABLE #t2 CLOSE c2 DEALLOCATE c2 FETCH NEXT FROM c1 INTO @CurrBillTrackId END CLOSE c1 DEALLOCATE c1 SELECT * FROM #t1 DROP TABLE #t1 END