How to combine Q1 and Q2 into someting like Q5 (But working!) ? Plse? I'M stuck... SQL Server 2008 R2
Please have a look what I've tried so far:
--Q1 does execute without errors: DECLARE @RfDate date DECLARE @Description nvarchar(250) DECLARE @BAccount int SET @RfDate = '{Rf_Date}' SET @Description = {dbo.BankstatementLine_Description|type=string} SET @BAccount = {dbo.BankAccount_Id}
IF @RfDate <> ''
BEGIN
SELECT *
FROM
dbo.BankStatementLine
WHERE
Date >=@RfDate
AND
FkBankAccount = @BAccount
AND
IsDebit = 'true'
AND
Id NOT IN (select FkBankStatementLine from DocumentBankStatementLine)
END
ELSE
BEGIN
SELECT *
FROM
dbo.BankStatementLine
WHERE
Date <> @RfDate
AND
FkBankAccount =@BAccount
AND
IsDebit = 'true'
AND
Id NOT IN (select FkBankStatementLine from DocumentBankStatementLine)
END
--Q2 does execute without errors: DECLARE @RAmount float SET @RAmount = {Rf_Amount}
IF @RAmount <>''
BEGIN
SELECT Amount
FROM dbo.BankStatementLine
WHERE Amount=@RAmount
END
ELSE
BEGIN
SELECT Amount
FROM dbo.BankStatementLine
WHERE Amount<>@RAmount
END
After the help from X-Zero (imperative describes my character better then my program skills btw), I came up with the following query which actually does what it needs to do, except for the last two AND OR rules. The LIKE does not have any influence, if I enter ‘PEKING’ in the field @RfAccept, the entries whith Description “0111111111 GPPeking” should be presented, but is does not.....
:
DECLARE @RfDate date
DECLARE @BAccount int
DECLARE @RfAmount decimal
DECLARE @RfAcAmount float
DECLARE @RfKenmerk nvarchar(250)
DECLARE @RfAccept nvarchar(250)
SET @RfDate = '{Rf_Date}'
SET @BAccount = {dbo.BankAccount_Id}
SET @RfAmount = {Rf_Amount}
SET @RfAcAmount = {Rf_AccAmount}
SET @RfKenmerk = {Rf_Betalingskenmerk|type=string}
SET @RfAccept = {Rf_Acceptgiro|type=string}
SELECT *
FROM
dbo.BankStatementLine
WHERE -- All statements can have a value or ''. All statements are not mandatory.
isDebit = 1
AND Id NOT IN (select FkBankStatementLine from DocumentBankStatementLine)
AND fkBankAccount = {dbo.bankAccount_Id}
AND ((Date = @RfDate AND @RfDate <> '')
OR (Date <> @RfDate AND @RfDate = ''))
AND ((Amount = @RfAmount AND @RfAmount <> '')
OR (Amount <> @RfAmount AND @RfAmount = ''))
AND ((Amount = @RfAcAmount AND @RfAcAmount <> '')
OR (Amount <> @RfAcAmount AND @RfAcAmount = ''))
AND((Description LIKE '%@RfAccept%' AND @RfAccept<>'')--When Rf_Acceptgiro has a value, the value must be part of the field Description.
OR (Description <> @RfAccept AND @RfAccept ='')) --OR Return all Description rules
AND((Description LIKE '%@RfKenmerk%' AND @RfKenmerk<>'')--When Rf_Kenmerk has a value, the value must be part of the field Description.
OR (Description <> @RfKenmerk AND @RfKenmerk =''))--OR Return all Description rules
