SQL Server 訂單分析

標籤：style   blog   color   io   os   ar   sp   div   2014   

select  distinct PurchasedByContactID as PurchasedByContactID into #SPfrom BASKET.. BaseOrderTracker(nolock )where OrderDate> ‘2014-2-1‘ and OrderDate< ‘2014-3-1‘ and OrderTypeID =15 and      OESourceID= 2and StageID= 10400select  distinct PurchasedByContactID as PurchasedByContactID into #Pfrom BASKET.. BaseOrderTracker(nolock )where OrderDate> ‘2014-2-1‘ and OrderDate< ‘2014-3-1‘  and OrderTypeID= 15 and  OESourceID= 2--流失的人select a.* into #NSP from #P( nolock)        as awhere not exists(select top 1 1 from #SP as b where a.PurchasedByContactID= b.PurchasedByContactID )--流失人的詳細資料select c. StartDate as StartDate ,c. ConsultantLevelID as Levelcode ,i.DirectSellerID as DirectSellerID, #NSP.PurchasedByContactID as contactIdinto #SNPDfrom CONTACTS.. consultants c(nolock )  inner join #NSP ( nolock )on #NSP. PurchasedByContactID=c .ContactID  inner join CONTACTS..InternationalConsultants i( nolock)on c. ContactID=i .ContactID--流失的人的訂單select #SNPD.*, b.orderid as orderid into #porderfrom BASKET.. BaseOrderTracker b ( nolock) inner join #SNPD( nolock ) on b.PurchasedByContactID= #SNPD.contactIdand b. OrderDate>‘2014-2-1‘ and b .OrderDate< ‘2014-3-1‘ and b.OrderTypeID= 15 and b .StageID<> 10400 and  b .OESourceID= 2--訂單最大值select #porder. orderid,max (o .StageID) as stageid into #odermax from #porder (nolock )inner join BASKET..OrderTrackerAudit o (nolock ) on o. OrderID = #porder.OrderIDgroup by #porder.orderid--lastselect #porder.* ,#odermax .stageid into #last from #porder (nolock ), #odermax(nolock )where #odermax. orderid=#porder .orderid--僅查詢select * from #last (nolock )where stageid= 10400--失敗人員訂單的STAGE分析select stageid, COUNT( orderid ) from #last( nolock )group by stageidorder by stageid--失敗人員訂單的level分析select  com. PurchaserCareerLevelID,COUNT (com . OrderID) from  #last(nolock ) l inner join CosmeticOrderMaster (nolock ) comon l. orderid=com .orderidgroup by com.PurchaserCareerLevelIDorder by com.PurchaserCareerLevelID--成功訂單的level分析select  com. PurchaserCareerLevelID,COUNT (com . OrderID) from  BASKET .. BaseOrderTracker( nolock ) bot inner join CosmeticOrderMaster (nolock ) comon bot. OrderID=com .orderidwhere OrderDate> ‘2014-2-1‘ and OrderDate<     ‘2014-3-1‘  and OrderTypeID =15 and      OESourceID= 2and StageID = 10400group by com.PurchaserCareerLevelIDorder by com.PurchaserCareerLevelID

 

SQL Server 訂單分析