Question

我提出以下意见,并试图将其用作一种储存程序;然而,我的储存程序并没有像我这样退回。

我的看法有46个段落:

SELECT   
   NDC_Text, Trade_Name, Vendor_Code, SUM(Quantity_Received) AS QTY, 
   SUM(CASE WHEN Cost_Center = 7070 THEN Cost_Center_Dollars ELSE 0 END) AS [7070], 
   SUM(CASE WHEN Cost_Center = 7071 THEN Cost_Center_Dollars ELSE 0 END) AS [7071], 
   SUM(CASE WHEN Cost_Center = 7700 THEN Cost_Center_Dollars ELSE 0 END) AS [7700], 
   SUM(CASE WHEN Cost_Center = 7701 THEN Cost_Center_Dollars ELSE 0 END) AS [7701], 
   SUM(CASE WHEN Cost_Center = 7702 THEN Cost_Center_Dollars ELSE 0 END) AS [7702], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7703], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7705],
   SUM(Cost_Center_Dollars) AS Total, Program_Type
FROM  dbo.VDP_Master_Drug_Receipts_Log 
WHERE Date_Entered BETWEEN  10/1/2011  AND  11/1/2011 
   AND (Program_Type =  VDP-BULK )
GROUP BY NDC_Text, Trade_Name, Vendor_Code, Program_Type
ORDER BY Trade_Name

https://i.stack.imgur.com/WicF2.gif”rel=“nofollow”>Results

我储存的程序

GO ALTER PROCEDURE [dbo].[ShowProductByCategory](@StartDate DateTime, @EndDate DateTime, @Type Varchar(15))
AS
Return 
SELECT NDC_Text, Trade_Name, Vendor_Code, SUM(Quantity_Received) AS QTY, 
   SUM(CASE WHEN Cost_Center = 7070 THEN Cost_Center_Dollars ELSE 0 END) AS [7070], 
   SUM(CASE WHEN Cost_Center = 7071 THEN Cost_Center_Dollars ELSE 0 END) AS [7071], 
   SUM(CASE WHEN Cost_Center = 7700 THEN Cost_Center_Dollars ELSE 0 END) AS [7700], 
   SUM(CASE WHEN Cost_Center = 7701 THEN Cost_Center_Dollars ELSE 0 END) AS [7701], 
   SUM(CASE WHEN Cost_Center = 7702 THEN Cost_Center_Dollars ELSE 0 END) AS [7702], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7703], 
   SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7705],
   SUM(Cost_Center_Dollars) AS Total, Program_Type
FROM dbo.VDP_Master_Drug_Receipts_Log 
WHERE Date_Entered BETWEEN @StartDate AND @StartDate
   AND (Program_Type =  Type )
GROUP BY NDC_Text, Trade_Name, Vendor_Code, Program_Type
ORDER BY Trade_Name

Answer 1

页: 1 这里的条款!

为此:

ALTER PROCEDURE [dbo].[ShowProductByCategory]
    (@StartDate DateTime, @EndDate DateTime, @Type Varchar(15))
AS
  -- Return    NO RETURN HERE !!
    SELECT 
       NDC_Text, Trade_Name, Vendor_Code, SUM(Quantity_Received) AS QTY, 
       SUM(CASE WHEN Cost_Center = 7070 THEN Cost_Center_Dollars ELSE 0 END) AS [7070], 
       SUM(CASE WHEN Cost_Center = 7071 THEN Cost_Center_Dollars ELSE 0 END) AS [7071], 
       SUM(CASE WHEN Cost_Center = 7700 THEN Cost_Center_Dollars ELSE 0 END) AS [7700], 
       SUM(CASE WHEN Cost_Center = 7701 THEN Cost_Center_Dollars ELSE 0 END) AS [7701], 
       SUM(CASE WHEN Cost_Center = 7702 THEN Cost_Center_Dollars ELSE 0 END) AS [7702], 
       SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7703], 
       SUM(CASE WHEN Cost_Center = 7703 THEN Cost_Center_Dollars ELSE 0 END) AS [7705],
       SUM(Cost_Center_Dollars) AS Total, Program_Type
    FROM dbo.VDP_Master_Drug_Receipts_Log 
    WHERE Date_Entered BETWEEN @StartDate AND @EndDate
    AND (Program_Type =  Type )
    GROUP BY 
        NDC_Text, Trade_Name, Vendor_Code, Program_Type
    ORDER BY 
        Trade_Name

Answer 2

页: 1 条款

WHERE Date_Entered BETWEEN @StartDate AND @StartDate

That should probably be @EndDate

还有一个不同的<代码>Progam_Type,这将产生不同的结果。

Answer 3

问题载于WHERE条款。

WHERE Date_Entered BETWEEN @StartDate AND @StartDate
   AND (Program_Type =  Type )

我认为,这必须

WHERE Date_Entered BETWEEN @StartDate AND @EndDate
   AND (Program_Type = @Type)

友情链接