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
