SQL Server 2012查询/作业

debugcn 发表于 Dev

基思

我有一个非常复杂的SQL查询，需要在每个星期一早上（早）运行。

我已经获得了SQL查询来为我提供所需的数据，但是我不知如何使查询将结果插入表中。

声明语句导致了问题（我认为）

有人有什么想法吗？理想情况下，我想设置一个可以运行并附加到表的作业。

谢谢。K.

DECLARE @StartDateTime     DATETIME;
DECLARE @EndDateTime       DATETIME;
DECLARE @ReportOn          CHAR(1);
DECLARE @Group1            VARCHAR(25);
DECLARE @Group2            VARCHAR(25);
DECLARE @Date1Grouping     VARCHAR(25);
DECLARE @Date2Grouping     VARCHAR(25);
DECLARE @DivisionID        VARCHAR(10);
DECLARE @GroupSubsidiaries BIT;

SET @StartDateTime     = '3/06/2013'  ;
SET @EndDateTime       = '3/12/2013' ;
SET @ReportOn          = 'S'          ;
SET @Group1            = 'Due Date'   ;
SET @Group2            = 'Sale Date'  ;
SET @Date1Grouping     = 'Yearly'     ;
SET @Date2Grouping     = 'Daily'      ; 
SET @GroupSubsidiaries = 1            ;

DECLARE @GLEntries TABLE (ID INT PRIMARY KEY, Amount FLOAT, EntryDateTime     DATETIME, GLClassificationType INT, 
                      GroupID INT, GLAccountID INT, GLAccountClassTypeID  INT, AccountID INT, 
                      TransHeaderID INT, TransDetailID INT, DivisionID INT, 
                      Classification INT, IsTaxable BIT, TaxClassID INT,
                      GoodsItemID INT, GoodsItemClassTypeID INT);

--   'C' used for Closed Reports
IF ( @ReportOn = 'C' )
INSERT INTO @GLEntries
SELECT GL.ID, GL.Amount, GL.EntryDateTime, GL.GLClassificationType, 
     GL.GroupID, GL.GLAccountID, GL.GLAccountClassTypeID, GL.AccountID, 
     GL.TransactionID, GL.TransDetailID, GL.DivisionID, 
     GL.Classification, GL.IsTaxable, GL.TaxClassID,
     GL.GoodsItemID, GL.GoodsItemClassTypeID
FROM   GL WITH(NOLOCK)
WHERE  (GL.GLAccountID <> 53)
     AND EXISTS( SELECT 1 
                 FROM TransHeader 
                 WHERE ClosedDate BETWEEN @StartDateTime and @EndDateTime
                       AND ID = GL.TransactionID )

ELSE
INSERT INTO @GLEntries
SELECT GL.ID, GL.Amount, GL.EntryDateTime, GL.GLClassificationType, 
     GL.GroupID, GL.GLAccountID, GL.GLAccountClassTypeID, GL.AccountID, 
     GL.TransactionID, GL.TransDetailID, GL.DivisionID, 
     GL.Classification, GL.IsTaxable, GL.TaxClassID,
     GL.GoodsItemID, GL.GoodsItemClassTypeID
FROM   GL WITH(NOLOCK)
WHERE  (GL.GLAccountID <> 53)
     AND GL.EntryDateTime between @StartDateTime and @EndDateTime
;



SELECT Orders.*, Totals.*,
   ( SELECT -Sum(Amount) 
     FROM   @GLEntries GL1 
            LEFT JOIN TransHeader TH ON (TH.ID = GL1.TransHeaderID)
     WHERE  GL1.GLClassificationType = 2005)

    as TotalTaxes,

    @StartDateTime  as theStartDate,
    @EndDateTime as theEndDate,
   -- Selects Group1 based on @Group1
   CASE
     WHEN @Group1 = 'Product'  THEN Product 
     WHEN @Group1 = 'None'     THEN 'None'
     WHEN @Group1 = 'Due Date' THEN 
       CASE
         WHEN @Date1Grouping = 'Daily'   THEN Convert( VARCHAR(25),   Orders.DueDate, 112)
         WHEN @Date1Grouping = 'Weekly'  THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw,Orders.DueDate) + 1, Orders.DueDate), 112 )
         WHEN @Date1Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.DueDate) * 100 + DatePart(mm,Orders.DueDate) AS VARCHAR(7) )
         WHEN @Date1Grouping = 'Yearly'  THEN Cast( DatePart(yyyy, Orders.DueDate) AS VARCHAR(4) )
       END
     WHEN @Group1 = 'Sales Date' THEN 
       CASE
         WHEN @Date1Grouping = 'Daily'   THEN Convert( VARCHAR(25), Orders.EntryDateTime, 112 )
         WHEN @Date1Grouping = 'Weekly'  THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw,Orders.EntryDateTime) + 1, Orders.EntryDateTime), 112 )
         WHEN @Date1Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.EntryDateTime) * 100 + DatePart(mm, Orders.EntryDateTime) AS VARCHAR(7) )
         WHEN @Date1Grouping = 'Yearly'  THEN Cast( DatePart(yyyy, Orders.EntryDateTime) AS VARCHAR(4) )
       END
     WHEN @Group1 = 'Primary Salesperson'     THEN Salesperson1LastName + Salesperson1FirstName 
     WHEN @Group1 = 'Customer Origin'         THEN OriginName
     WHEN @Group1 = 'Industry'                THEN IndustryName
     WHEN @Group1 = 'Order Origin'            THEN OrderOriginName
     WHEN @Group1 = 'Postal Code'             THEN PostalCode
     WHEN @Group1 = 'Postal Code 3 Digit'     THEN PostalCode3Digit
     WHEN @Group1 = 'Product Category'        THEN ProductCategory
     WHEN @Group1 = 'Account'                 THEN AccountName
     WHEN @Group1 = 'Company Name'            THEN CompanyName
     WHEN @Group1 = 'Company Frequency'       THEN CompanyName
     WHEN @Group1 = 'Company Volume'          THEN CompanyName
     WHEN @Group1 = 'GL Department'           THEN GLDepartment
     WHEN @Group1 = 'Industry (Parent)'       THEN IndustryParent
     WHEN @Group1 = 'Company Origin (Parent)' THEN CompanyOriginParent
     WHEN @Group1 = 'Order Origin (Parent)'   THEN OrderOriginParent
     WHEN @Group1 = 'Region'                            THEN RegionName
   END AS Group1,

   CASE 
     WHEN @Group2 = 'Product'  THEN Product
     WHEN @Group2 = 'None'     THEN 'None'
     WHEN @Group2 = 'Due Date' THEN 
       CASE
         WHEN @Date2Grouping = 'Daily'   THEN Convert( VARCHAR(25), Orders.DueDate, 112 )
         WHEN @Date2Grouping = 'Weekly'  THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw, Orders.DueDate) + 1, Orders.DueDate), 112 )
         WHEN @Date2Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.DueDate) * 100 + DatePart(mm, Orders.DueDate) AS VARCHAR(7) )
         WHEN @Date2Grouping = 'Yearly'  THEN Cast( DatePart(yyyy, Orders.DueDate) AS VARCHAR(4) )
       END
     WHEN @Group2 = 'Sales Date' THEN 
       CASE
         WHEN @Date2Grouping = 'Daily'   THEN Convert( VARCHAR(25), Orders.EntryDateTime, 112 )
         WHEN @Date2Grouping = 'Weekly'  THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw, Orders.EntryDateTime) + 1, Orders.EntryDateTime), 112 )
         WHEN @Date2Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.EntryDateTime) * 100 + DatePart(mm, Orders.EntryDateTime) AS VARCHAR(7) )
         WHEN @Date2Grouping = 'Yearly'  THEN Cast( DatePart(yyyy, Orders.EntryDateTime) AS VARCHAR(4) )
       END
     WHEN @Group2 = 'Primary Salesperson' THEN Salesperson1LastName + Salesperson1FirstName 
     WHEN @Group2 = 'Customer Origin'     THEN OriginName
     WHEN @Group2 = 'Industry'            THEN IndustryName
     WHEN @Group2 = 'Order Origin'        THEN OrderOriginName
     WHEN @Group2 = 'Postal Code'         THEN PostalCode
     WHEN @Group2 = 'Postal Code 3 Digit' THEN PostalCode3Digit
     WHEN @Group2 = 'Product Category'    THEN ProductCategory
     WHEN @Group2 = 'Account'             THEN AccountName
     WHEN @Group2 = 'Company'             THEN CompanyName
     WHEN @Group2 = 'GL Department'       THEN GLDepartment
     WHEN @Group2 = 'Region'                            THEN RegionName
   END AS Group2 

FROM ( SELECT 1 AS StoreID,
          fx_Return.DivisionID,
          EmployeeGroup.DivisionName,
          fx_Return.TransHeaderID,
          fx_Return.OrderNumber,
          fx_Return.InvoiceNumber,
          fx_Return.CompanyName,
          -fx_Return.GLAmount AS Amount,
          GLAccount.AccountName,
          fx_Return.StatusText,
          fx_Return.Classification,
          fx_Return.GLClassificationType,
          fx_Return.Description,
          fx_Return.EntryDateTime,
          fx_Return.DiscountPrice,
          fx_Return.SaleDate,
          fx_Return.DueDate,
          fx_Return.BuiltDate,
          fx_Return.OrderCreatedDate,
          fx_Return.ClosedDate,
          fx_Return.AccountID,
          fx_Return.GLDepartment,
          fx_Return.SalesPerson1ID,
          SalesPerson1.FirstName AS SalesPerson1FirstName,
          SalesPerson1.LastName AS SalesPerson1LastName,
          Origin.ItemName AS OriginName,        
          CASE
            WHEN Origin.ParentID = 11 THEN Origin.ItemName
            ELSE CompanyOriginParent.ItemName
          END AS CompanyOriginParent,
          Industry.ItemName AS IndustryName,
          CASE
            WHEN IndustryParent.ID = 10 THEN Industry.ItemName
            ELSE IndustryParent.ItemName
          END AS IndustryParent,
          fx_Return.OrderOriginName,
          CASE 
            WHEN OrderOrigin.ParentID = 13 THEN OrderOrigin.ItemName
            ELSE OrderOriginParent.ItemName
          END AS OrderOriginParent,
          Address.PostalCode,
          SUBSTRING(Address.PostalCode,1,3) AS PostalCode3Digit,
          Product.ItemName AS Product,
          ProductCategory.ElementName AS ProductCategory,
          ProductSubCategory.ElementName AS ProductSubCategory,
          Region.ItemName AS RegionName

    FROM ( SELECT GL2.EntryDateTime, 
                 SUM( CASE
                        WHEN @ReportOn NOT IN  ('P','B') THEN GL2.Amount
                        WHEN GL2.GLAccountID IN (11, 12) THEN -GL2.Amount
                        WHEN GL2.GLClassificationType IN (4000, 2005) THEN  GL2.Amount
                        ELSE 0
                      END ) AS GLAmount,
                 GL2.GLAccountID,
                 CASE
                   WHEN GL2.GLClassificationType IN (4000,5002)
                   AND COALESCE(GL2.Classification,-1) NOT IN (100,200,300,400,500) THEN 100
                   ELSE GL2.Classification
                 END AS Classification,
                 GL2.GLClassificationType,
                 GL2.DivisionID,
                 GL2.IsTaxable,
                 GL2.TaxClassID,
                 GL2.GroupID,
                 TransHeader.StatusText,
                 CAST(TransHeader.Description AS VARCHAR(30)) AS Description,
                 TransHeader.DueDate,
                 TransHeader.OrderOriginName,

                 -- TransHeader Related Fields
                 GL2.TransHeaderID, 
                 TransHeader.OrderNumber,
                 COALESCE( TransHeader.InvoiceNumber, TransHeader.OrderNumber) as InvoiceNumber,
                 TransHeader.CreditMemoOrderID,
                 TransHeader.Salesperson1ID, 
                 TransHeader.OrderOriginID,
                 TransHeader.InvoiceAddressID as OrderInvoiceAddressID,
                 TransHeader.PromotionID,
                 Transheader.DiscountPrice,
                 TransHeader.PricingLevelID, 
                 TransHeader.ClosedDate,
                 TransHeader.OrderCreatedDate,
                 TransHeader.SaleDate,
                 TransHeader.BuiltDate,

                 -- TransDetail Related Fields
                 GL2.TransDetailID,
                 TransDetail.LineItemNumber,
                 TransDetail.Quantity,
                 GL2.GoodsItemID,
                 GL2.GoodsItemClassTypeID,
                 TransDetail.GoodsItemCode,

                 Station.StationName as GLDepartment,

                 -- Account Related Fields
                 GL2.AccountID,
                 ParentAccount.CompanyName,
                 ParentAccount.AccountNumber,
                 ParentAccount.PricingPlanTypeID,
                 ParentAccount.IndustryID,
                 ParentAccount.RegionID,
                 ParentAccount.OriginID as CompanyOriginID,
                 ParentAccount.Marketing3ID,
                 ParentAccount.BillingAddressID as CompanyBillingAddressID

          FROM   @GLEntries GL2
                 LEFT JOIN TransHeader WITH(NOLOCK) ON (TransHeader.ID = GL2.TransHeaderID)
                 LEFT JOIN Account WITH(NOLOCK) ON (Account.ID = GL2.AccountID)
                 LEFT JOIN Account ParentAccount WITH(NOLOCK) ON ParentAccount.ID = ( CASE WHEN @GroupSubsidiaries = 1 THEN COALESCE(Account.ParentID, Account.ID) ELSE Account.ID END )
                 LEFT JOIN TransDetail WITH(NOLOCK) ON (TransDetail.ID = GL2.TransDetailID)
                 LEFT JOIN Station WITH(NOLOCK) ON TransDetail.GLDepartmentID = Station.ID

          WHERE  ( GL2.GLClassificationType IN (4000) 
                   OR ( @ReportOn = 'B' AND GL2.GLAccountID in (12) )
                   OR ( @ReportOn = 'P' AND GL2.GLAccountID in (11,12) )
                 )


          GROUP BY GL2.EntryDateTime, 
                   GL2.GLAccountID,
                   GL2.GLAccountClassTypeID,
                   GL2.Classification,
                   GL2.GLClassificationType,
                   GL2.DivisionID,
                   GL2.IsTaxable,
                   GL2.TaxClassID,
                   GL2.GroupID,

                   -- TransHeader Related Fields
                   GL2.TransHeaderID, 
                   TransHeader.OrderNumber,
                   COALESCE( TransHeader.InvoiceNumber, TransHeader.OrderNumber ),
                   TransHeader.CreditMemoOrderID,
                   TransHeader.Salesperson1ID,
                   TransHeader.OrderOriginID,
                   TransHeader.InvoiceAddressID,
                   TransHeader.PromotionID,
                   TransHeader.PricingLevelID, 
                   Transheader.DiscountPrice,
                   TransHeader.OrderCreatedDate,
                   TransHeader.SaleDate,
                   TransHeader.ClosedDate,
                   TransHeader.BuiltDate,
                   TransHeader.StatusText,

                   TransHeader.DueDate,
                   TransHeader.OrderOriginName,
                   CAST(TransHeader.Description AS VARCHAR(30)),

                   -- TransDetail Related Fields
                   GL2.TransDetailID,
                   TransDetail.LineItemNumber,
                   TransDetail.Quantity,
                   GL2.GoodsItemID,
                   GL2.GoodsItemClassTypeID,
                   TransDetail.GoodsItemCode,

                   Station.StationName,

                   -- Account Related Fields
                   GL2.AccountID,
                   ParentAccount.CompanyName,
                   ParentAccount.AccountNumber,
                   ParentAccount.PricingPlanTypeID,
                   ParentAccount.IndustryID,
                   ParentAccount.RegionID,
                   ParentAccount.OriginID,
                   ParentAccount.Marketing3ID,
                   ParentAccount.BillingAddressID ) AS fx_Return

        LEFT JOIN EmployeeGroup WITH(NOLOCK) ON EmployeeGroup.ID = fx_Return.DivisionID 
        LEFT JOIN MarketingListItem AS Origin WITH(NOLOCK) ON Origin.ID = fx_Return.CompanyOriginID
        LEFT JOIN MarketingListItem AS CompanyOriginParent WITH(NOLOCK) ON Origin.ParentID = CompanyOriginParent.ID     
                                                                           AND Origin.ParentClassTypeID = CompanyOriginParent.ClassTypeID
        LEFT JOIN MarketingListItem AS Industry WITH(NOLOCK) ON Industry.ID = fx_Return.IndustryID
        LEFT JOIN MarketingListItem AS IndustryParent WITH(NOLOCK) ON Industry.ParentID = IndustryParent.ID 
                                                                      AND Industry.ParentClassTypeID = IndustryParent.ClassTypeID 
        LEFT JOIN MarketingListItem AS OrderOrigin WITH(NOLOCK) ON fx_Return.OrderOriginID = OrderOrigin.ID 
        LEFT JOIN MarketingListItem AS OrderOriginParent WITH(NOLOCK) ON OrderOrigin.ParentID = OrderOriginParent.ID
                                                                         AND OrderOrigin.ParentClassTypeID = OrderOriginParent.ClassTypeID
        LEFT JOIN Address WITH(NOLOCK) ON fx_Return.CompanyBillingAddressID = Address.ID
        LEFT JOIN CustomerGoodsItem AS Product WITH(NOLOCK) ON fx_Return.GoodsItemID = Product.ID
                                                               AND Product.ClassTypeID=fx_Return.GoodsItemClassTypeID
        LEFT JOIN PricingElement AS ProductCategory WITH(NOLOCK) ON Product.CategoryID = ProductCategory.ID
                                                                    AND Product.CategoryClassTypeID = ProductCategory.ClassTypeID
        LEFT JOIN PricingElement AS ProductSubCategory WITH(NOLOCK) ON ProductCategory.ParentID = ProductSubCategory.ID
                                                                       AND ProductCategory.ParentClassTypeID = ProductSubCategory.ClassTypeID
        LEFT JOIN Employee AS Salesperson1 WITH(NOLOCK) ON fx_Return.SalesPerson1ID = Salesperson1.ID 
        LEFT JOIN GLAccount WITH(NOLOCK) ON fx_Return.GLAccountID = GLAccount.ID 
        LEFT JOIN MarketingListItem AS Region WITH(NOLOCK) ON  fx_Return.RegionID = Region.ID
) AS Orders

 LEFT JOIN ( SELECT SUM( CASE 
                           WHEN @ReportOn <> 'P' THEN -GL3.Amount
                           WHEN GL3.GLAccountID IN (11, 12, 51) THEN GL3.Amount
                           WHEN GL3.GLClassificationType IN (4000, 2005)   THEN -GL3.Amount
                           ELSE 0
                         END ) AS CompanyTotalAmount,
                    COUNT(DISTINCT TransHeader.ID) AS CompanyOrderCount,
                    GL3.AccountID AS TotalsAccountID
             FROM   @GLEntries GL3 
                    LEFT JOIN TransHeader WITH(NOLOCK) ON (TransHeader.ID = GL3.TransHeaderID)

             WHERE  ( GL3.GLClassificationType in (4000) -- 5002) --Kyle/Scott, we have no clue why this was included, can't think of any expense accounts you'd ever show as a sale
                      OR
                      ( @ReportOn = 'B' AND GL3.GLAccountID in (12) )
                      OR
                      ( @ReportOn = 'P' AND GL3.GLAccountID in (11, 12) )
                    )


             GROUP BY GL3.AccountID ) AS Totals ON Totals.TotalsAccountID =  Orders.AccountID

WHERE OrderNumber IS NOT NULL

阿米特·苏克拉里亚（Amit Sukralia）

1）确定-因此，您需要创建一个与选择列表中的列和数据类型相同的表（SELECT Orders.*, Totals.*...）
2）您将必须insert在select-之前添加该语句-像这样
insert StagingTableName select Orders.*, Totals.*...
3）创建存储过程包含所有代码。
4）使用SQL Server代理作业执行存储过程。该链接应该有所帮助。