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 your edpieces
Into # T1
From mktg_shipbills
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
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
