DECLARE @EndDate VARCHAR(10)
SET @EndDate='2018-01-19'

DECLARE @Date VARCHAR(10)
SET @Date='2018-01-19'

select * 
 into #temptable
 from(

 SELECT DISTINCT Device_Unique_ID__c as PhysicalDevice,
  CASE M.Model
  WHEN '426i (v2)' THEN 'V2'
 WHEN 'PXUV3D (v3 120v)' THEN 'V3'
 WHEN 'PXUV4D (v4 120v)' THEN 'X4'
 WHEN 'PXUV4E (v4 230v)' THEN 'X4'
 WHEN 'PXUV4F (v4 230v)' THEN 'X4'
 WHEN 'PXUV4K (v4 230v F Plug)' THEN 'X4'
 WHEN 'PXUV5D (v5 120v)' THEN 'X5'
 END AS Model#,

 DTransactionType,
 ActiveStatus,
 PDID as PhysicalDeviceID,
   --M.Name as AM1,contractEdate
    --Parent_Account_Owner__c,
   LogName as LogicalRobotName,
   LAccountName as LogicalAccountName,
    Type as LogicalAccountType,
   ALID as LogicalAccountID,
   Left(ALID,15) as LogicalAccountID15,
    CASE IIF(IIF(ImpAM is NULL,M.Name,ImpAM)='name','International',EVS)
    WHEN 'SODEXO' THEN 'Sodexo'
    WHEN 'x' THEN 'x'
   WHEN 'International' THEN 'International'
   Else 'Commercial' END as EVSProvider,
   ACID as ContractAccountID,
   ContractNumber,
   Convert(DATETIME,CAST(StartDate as DATETIME),105) ContractStartDate,
   Convert(DATETIME,CAST(EndDate as DATETIME),105) ContractEDate,
   Fee_per_Payment_Schedule__c as FeePerPaymentSchedule,
  Payment_Schedule__c as PaymentSchedule,
  Robots__c as Robots,
  Auto_Renew_or_Terminate__c as AutoRenew,
  ActivatedDate,
  CLIENT_SERVICES_DIRECTOR__C as RegionalAccountsDirector,
  ContractAccountName,
  ACType as ContractAccountType,
  Convert(DATETIME,CAST(D.Date as DATETIME),105)  LastFlashDate,
   D.Dys as DysSinceLastFlashed,
   DaysLastUpload,
  IIF((Dys>90 and (DaysLastUpload >90 or DaysLastUpload is null) and (Convert(DATETIME,CAST(EndDate 
 as 
 DATETIME),105)<Getdate())),'Dormant', 'NotDormant') as Dormancy,
 IIF(ImpAM is NULL,M.Name,ImpAM) as AM,
 CommissionedImplement as FirstCommissionAndImplementDtForLogicalDevice,
 recentstatus.Status__c,
 recentstatus.Decommissioned as DatePDeviceDecommissioned,
 recentstatus.LatestCommisDt as LastestCommissionDateForLogicalDevice

from
(Select distinct 
L.Device_Unique_ID__c,
PDID,
DTransactionType,
ActiveStatus,
Model,
LogID,
LogName,
LAccount,
LContract,
 LAccountName,
 AL.Name as LDAName,
 AL.Type,
 AL.Id as ALID,
 C.ContractNumber,
 C.StartDate,
C.EndDate,
C.Fee_per_Payment_Schedule__c,
C.Payment_Schedule__c,
C.Robots__c,
C.Auto_Renew_or_Terminate__c,
C.ActivatedDate,
AC.CLIENT_SERVICES_DIRECTOR__C,
AC.Name as ContractAccountName,
AC.Type as ACType,
AC.ID as ACID,
left(AC.ID, 15) as SalesForceCAccountID,
 U.Name,
 AC.PARENT_ACCOUNT_OWNER__c,
P.ACCOUNT_OWNER_TEXT__C,
 P.CASENUMBER

from 
(Select 
D.Device_Unique_ID__c,
D.ID as PDID,
D.Device_Transaction_Type__c as DTransactionType,
D.Active_Device__c as ActiveStatus,
D.Model2_c__c as Model,

D.Name as DName,

L.ID as LID,
L.Name as LName,
L.Account__C as LACCOUNT__C,
L.CONTRACT__C as LCONTRACT__C,
L.ACCOUNT_NAME__C as LACCOUNT_NAME__C,

 L2.ID as L2ID,
 L2.Name as L2Name,
 L2.Account__C as L2ACCOUNT__C,
 L2.CONTRACT__C as L2CONTRACT__C,
 L2.ACCOUNT_NAME__C as L2ACCOUNT_NAME__C,

 IIF(L.Id is null,L2.Id, L.Id) as LogID,
 IIF(L.Name is null ,L2.Name, L.Name) as LogName,
 IIF(L.Account__C is null,L2.ACCOUNT__C, L.Account__C) as LAccount,
 IIF(L.CONTRACT__C is null ,L2.CONTRACT__C,L.CONTRACT__C) as LContract,
 IIF(L.ACCOUNT_NAME__C is null ,L2.ACCOUNT_NAME__C,L.ACCOUNT_NAME__C) as LAccountName

 from ProdCopy.salesforce.Device as D
 left join [ProdCopy].dbo.Device as D1 on D.Name=D1.DeviceID
  left join [ProdCopy].[dbo].[LogicalDevice] as LD on LD.Id = D1.LogicalDeviceId
  Left join ProdCopy.salesforce.Logical_Device as L2 on LD.SalesforceId=L2.ID
  Left join ProdCopy.salesforce.Logical_Device as L on D.Logical_Device__c=L.ID) as L

  left join ProdCopy.salesforce.Account as AL on L.LAccount=AL.ID
  Left Join ProdCopy.salesforce.Contract as C on L.LContract=C.Id
  left join ProdCopy.salesforce.Account as AC on C.AccountId=AC.ID
  Left join [ProdCopy].[salesforce].[Case] as P on P.ACCOUNTID=AL.Id
  left join [ProdCopy].[salesforce].[User] as U on U.ID=AL.PARENT_ACCOUNT_OWNER__c
  )M
   Left join 
   (Select R.LatestCommisDt,R.Logical_Device__c,DAP.Device__c, 
   DAP.Decommissioned,CommisDt,DAP.Status__c from
                    (SELECT max(Convert(DATETIME,CAST((Commissoned_On__c) as DATETIME),105)) as 
   LatestCommisDt  ,Device__c,Logical_Device__c from [ProdCopy].[salesforce].[Device_Assignment]
                            Group by Logical_Device__c,Device__c) R left join

  (SELECT Convert(DATETIME,CAST((Commissoned_On__c) as DATETIME),105) as CommisDt  , 
   Convert(DATETIME,CAST((Decommissioned_On__c) as DATETIME),105)as 
   Decommissioned,Device__c,Status__c,Logical_Device__c
    from [XenexProdCopy].[salesforce].[Device_Assignment]
                )as DAP on DAP.CommisDt=R.LatestCommisDt and R.Device__c=DAP.Device__c and 
   R.Logical_Device__c=DAP.Logical_Device__c
                    group by 
   R.LatestCommisDt,R.Logical_Device__c,DAP.Device__c,DAP.Status__c,DAP.Decommissioned,CommisDt

                    ) RecentStatus on RecentStatus. [Logical_Device__c] =M.LogID and 
    Recentstatus.Device__c=PDID --Recent status of Physical Device
    left join

    (Select distinct 
    P.STATUS as ImpStatus,
    P.ACCOUNT_OWNER_TEXT__C as ImpAM,
    P.ACCOUNTID,
    P.ID

    from [ProdCopy].[salesforce].[Case] as P 
    where P.STATUS like 'IMP%'
    ) I on I.ACCOUNTID=M.ALID or I.ACCOUNTID=M.ACID

     left join (Select min(Convert(DATETIME,CAST((Commissoned_On__c) as DATETIME),105)) as 
     CommissionedImplement ,Logical_Device__c from [ProdCopy].[salesforce].[Device_Assignment]
                    group by logical_Device__c)as DA on DA.Logical_Device__c=M.LogID  --- 
   Implementation

    left join 

        (Select  right(C.[PhysicalDeviceId],6) as ID ,
          max((C.CycleStartTime_Date)) as Date,
          DATEDIFF(day, max((C.CycleStartTime_Date)),getdate()) as Dys
        from
        [ProdCopy].[report].[DisinfectionStats]as C
        group by C.[PhysicalDeviceID]
        union
        SELECT 
            right(a.[DeviceID],6) as ID,

            max(B.DateTimeStart) as Date,
            DATEDIFF(day, max(B.DateTimeStart),getdate()) as Dys

          FROM [ProdCopy].[v2legacy].[Device] as A left join [ProdCopy].[v2legacy]. 
   [DeviceLog]  as B on A.DeviceID=B.DeviceID
          group by a.[DeviceID]) D on M.Device_Unique_ID__c=D.ID
   left join
   (SELECT 
    DeviceLogSerialNumber
    ,min([DaysFromLastLogUpload])  as DaysLastUpload
    FROM [ProdCopy].[report].[LastUploadedLogCombined]  group by DeviceLogSerialNumber) LU on 
     LU.DeviceLogSerialNumber=M.Device_Unique_ID__c

    where 

  Type not in ('Former Customer', 'Former Sodexo Customer', 'Employee','Test Account')
  and
   ((LogName not like '%Training%'))
 and 
   (recentstatus.Status__c='Active')
 --and (recentstatus.Decommissioned is Null)---check for v2s only. once the V2s are gone, put the 
 clause back.
 and 
  (ContractAccountName is not null)

and (LAccountName  not like '%DMC%')
and (LAccountName  not like '%Intertek%')

 Group by
 Device_Unique_ID__c,
 M.Model,
 DTransactionType,
 ActiveStatus,
 PDID,
 LogName,
 LogID,
 LAccount,
 LContract,
 LAccountName,
 M.Name,
 EVS,
 Type,
  ALID,
   ACID,
 ContractNumber,
StartDate,
EndDate,
Fee_per_Payment_Schedule__c,
Payment_Schedule__c,
 Robots__c,
 Auto_Renew_or_Terminate__c,
 ActivatedDate,
 CLIENT_SERVICES_DIRECTOR__C,
 ContractAccountName,
 ACType,
 ImpAm,
 D.ID,
 D.Date,
 D.Dys,
 CommissionedImplement,
 RecentStatus.Status__c,
 Decommissioned,
 LatestCommisDt,
 DaysLastUpload

  Order by Device_Unique_ID__c desc)

为什么我在尝试创建临时表以进一步分析数据时收到错误消息…，我正在尝试从此查询创建临时表以将其联接到已存在的表？
这个错误使我无法完成我的任务。。。其内容如下：order by子句在视图、内联函数、派生表、子查询和公共表表达式中无效，除非还指定了top、offset或for xml。
当我删除order by函数，但在最后一行代码之后，它会给我一个无效的语法错误。。。有什么建议吗？