I cannot figure out how to set the variable values (@ts1, @ts2, @key) to a user's input.
This is my WHERE clause for this report:
WHERE
(IC_ProductLots.ProductionDate >= { ts '2015-06-24 00:00:00' }
AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 00:00:00' }
OR IC_ProductLots.ProductionDate IS NULL))
AND ((1=1)
AND AR_Customers.CustomerKey IN (124) )
When the user goes to run the report, they are prompted to choose the production dates and customer key. The WHERE clause fills in with the information they enter for Production Date and the Customer Key. It changes every time the report is ran.
On another thread I got help with creating placeholders for the variables and for where the user input should be stored in the other variables:
declare @ts1 datetime,
@ts2 datetime,
@key int;
set @ts1 = '2015-06-24 00:00:00';
set @ts2 = '2015-06-24 00:00:00';
set @key = 124;
For this report I had to create several variables that are called in the set section. Here is an example of one:
SET @Shrink = @InputWeight - (
SELECT Sum([ICPL].[OriginalQuantity_Stk])
FROM IC_Products [PC]
INNER JOIN DC_Transactions [DCT]
ON [PC].ProductKey = [DCT].ProductKey
INNER JOIN AR_Customers
ON [DCT].CustomerKey = AR_Customers.CustomerKey
INNER JOIN IC_ProductLots [ICPL]
ON [DCT].LotKey = [ICPL].LotKey
LEFT OUTER JOIN IC_ProductCosts [ICP]
ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5
WHERE (ICPL.ProductionDate >= @ts1
AND (ICPL.ProductionDate <= @ts2
OR ICPL.ProductionDate Is Null))
AND ((1=1) AND AR_Customers.CustomerKey IN (@key))
);
The problem is, I am unsure how to set @ts1, @ts2, and @key to what the user inputs.
The only language I am using is SQL on Microsoft SQL Server 2005
Please let me know if I need to provide anymore information or clarify anything.
Aucun commentaire:
Enregistrer un commentaire