–create Index Index_Status_Header_UpdateTime on Status_Header (event_code,update_datetime) –exec sp_DeliveryPerformanceReport ‘HKG’,’2015-11-01’,’2015-11-08’
CREATE PROC sp_DeliveryPerformanceReport @BranchCode CHAR(5), @StartDate VARCHAR(12), @EndDate VARCHAR(12)
AS BEGIN IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N’tempdb.dbo.#TempTableForOutForDeliveryShipment’) AND TYPE=’U’ ) –check the temp table BEGIN DROP TABLE #TempTableForOutForDeliveryShipment END CREATE TABLE #TempTableForOutForDeliveryShipment ( Shipment_Id CHAR(18), Staff_Code CHAR(5) ) INSERT INTO #TempTableForOutForDeliveryShipment (Shipment_Id,Staff_Code) SELECT dd.Shipment_id,fs.Staff_Code FROM Drs_Detail dd INNER JOIN Drs_Header dh ON dh.Drs_Number = dd.Drs_Number INNER JOIN Status_Detail sd ON dd.Shipment_Id = sd.Shipment_Id INNER JOIN Status_Header sh ON sh.Reference_Number = sd.Reference_Number AND sh.Event_Code IN (‘LI’,’OI’) INNER JOIN Field_Staff fs ON fs.Field_Staff_id = dh.Staff_Id INNER JOIN Company_Offices co ON dh.Office_Code = co.Office_Code WHERE co.Controlling_Branch = @BranchCode AND CONVERT(DATETIME,CONVERT(VARCHAR,sh.Update_datetime ,101)) BETWEEN CONVERT(DATETIME,@StartDate) AND CONVERT(DATETIME,@EndDate)
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb.dbo.#TempTableForOutForDeliveryTracking') AND TYPE='U' ) --check the temp tableBEGIN DROP TABLE #TempTableForOutForDeliveryTrackingEND CREATE TABLE #TempTableForOutForDeliveryTracking( Shipment_Id CHAR(18), Staff_Code CHAR(5), Event_Date_Time DATETIME, Event_Code CHAR(2),)INSERT INTO #TempTableForOutForDeliveryTracking(Shipment_Id,Staff_Code,Event_Date_Time,Event_Code)SELECT ts.Shipment_Id,ts.Staff_Code,MAX(pt.Event_date_time),pt.Event_CodeFROM #TempTableForOutForDeliveryShipment tsINNER JOIN Package_Tracker pt ON ts.Shipment_Id = pt.Shipment_IdWHERE pt.Event_Code IN ('LI','OI') OR (pt.Event_Code IN ('PE','CH','CP','DR') AND Event_Type = 'D')GROUP BY ts.Shipment_Id,ts.Staff_Code,pt.Event_Code--SELECT Staff_Code,--SUM(DATEDIFF(mm,(CASE WHEN Event_Code IN ('LI','OI') THEN MAX(Event_Date_Time) THEN NULL END),(CASE WHEN Event_Code IN ('PE','CH','CP','DR') THEN MAX(Event_Date_Time) THEN NULL END)))--COUNT(DISTINCT Shipment_Id)--FROM #TempTableForOutForDeliveryTracking--GROUP BY Staff_CodeSELECT Staff_Code,SUM(DATEDIFF(mi,StartDate,ISNULL(EndDate,DATEADD(dd,1,@EndDate)))) /COUNT(*) AS WADT,Count(*) AS CountsFROM(SELECT Shipment_Id,Staff_Code,MAX(CASE WHEN Event_Code IN ('LI','OI') THEN Event_Date_Time ELSE NULL END) StartDate,MAX(CASE WHEN Event_Code IN ('PE','CH','CP','DR') THEN Event_Date_Time ELSE NULL END) EndDateFROM #TempTableForOutForDeliveryTrackingGROUP BY Shipment_Id,Staff_Code) AS aGROUP BY Staff_Code ORDER BY WADTEND
新闻热点
疑难解答
