【SQLServer】临时表的一些应用

–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  table
BEGIN
    DROP TABLE #TempTableForOutForDeliveryTracking
END    
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_Code
FROM #TempTableForOutForDeliveryShipment ts
INNER JOIN Package_Tracker pt ON ts.Shipment_Id = pt.Shipment_Id
WHERE 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_Code


SELECT Staff_Code,SUM(DATEDIFF(mi,StartDate,ISNULL(EndDate,DATEADD(dd,1,@EndDate)))) /COUNT(*)  AS WADT,Count(*) AS Counts
FROM
(
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) EndDate
FROM #TempTableForOutForDeliveryTracking
GROUP BY Shipment_Id,Staff_Code
) AS a
GROUP BY Staff_Code ORDER BY WADT

END