January 17, 2012 at 11:50 pm
i Have a report that contains parameters one for customer and one to decide what to include in the report summary. In this case I'm using in the second parameter The following options
@pandcparam = Just, Exclude or Include
The second parameter decides what to display in the query summary:
If Exclude is selected: Credit should not be part of the summary line
If Include is selected: Credit + Payments Must be part of the summary line
If Just is selected: Only credit should be part of the summary line
the output should be:
for exclude : 123 ----- 300.00
for include : 123 ----- 253.00
for Just : 123 ----- -47.00
Table:
Customer, Amount, Type
123 -------- 250.00 -------P
123--------- -25.00 ------C
123--------- 10.00 ------P
123--------- 40.00-------p
123--------- -22.00 -------C
select statement:
Select Account_Number = Customer,
AR_Amount = sum(isnull(Amount,0))
where 1 = 1
and Customer = @account
and type in (case when @pandcparam = 'Just' then 'C'
when @pandcparam = 'Exclude' then 'P'
else
'%' end)
group by Customer
My question is why it doesn't like the Case command. what am i doing wrong? any suggestions: Than you in advanced:-)
January 18, 2012 at 12:03 am
peseta30 (1/17/2012)
Select Account_Number = Customer,AR_Amount = sum(isnull(Amount,0))
where 1 = 1
and Customer = @account
and type in (case when @pandcparam = 'Just' then 'C'
when @pandcparam = 'Exclude' then 'P'
else
'%' end)
group by Customer[/code]
My question is why it doesn't like the Case command. what am i doing wrong? any suggestions: Than you in advanced:-)
Well judging by the last '%' option im guessing LIKE would be more usefull. So drop the IN for a LIKE and remove the () around the CASE.
Oh and your exemple lacks FROM
/T
January 18, 2012 at 3:38 am
This shows three ways to solve this problem:
Example table and data:
CREATE TABLE #Example
(
Customer integer NOT NULL,
Amount money NOT NULL,
TranType character(1) NOT NULL
);
CREATE CLUSTERED INDEX c ON #Example (Customer, TranType)
INSERT #Example
(Customer, Amount, TranType)
VALUES
(123, $250.00, 'P'),
(123, $-25.00, 'C'),
(123, $010.00, 'P'),
(123, $040.00, 'P'),
(123, $-22.00, 'P');
DECLARE
@Customer integer = 123,
@PandC varchar(7) = 'exclude';
Separate statements
IF @PandC = 'include'
BEGIN
SELECT
@Customer,
SUM(e.Amount)
FROM #Example AS e
WHERE
e.Customer = @Customer
AND e.TranType IN ('P', 'C')
END
ELSE IF @PandC = 'exclude'
BEGIN
SELECT
@Customer,
SUM(e.Amount)
FROM #Example AS e
WHERE
e.Customer = @Customer
AND e.TranType = 'P'
END
IF @PandC = 'just'
BEGIN
SELECT
@Customer,
SUM(e.Amount)
FROM #Example AS e
WHERE
e.Customer = @Customer
AND e.TranType = 'C'
END;
Recompile
-- Requires at least SQL Server 2008 SP1 CU5
SELECT
@Customer,
SUM(e.Amount)
FROM #Example AS e
WHERE
e.Customer = @Customer
AND
(
(@PandC IN ('include', 'exclude') AND e.TranType = 'P')
OR
(@PandC IN ('include', 'just') AND e.TranType = 'C')
)
OPTION (RECOMPILE)
Dynamic SQL
DECLARE @SQL nvarchar(max) =
N'
SELECT
@Customer,
SUM(e.Amount)
FROM #Example AS e
WHERE
e.Customer = @Customer
AND e.TranType ' +
CASE
WHEN @PandC = 'include'
THEN N'IN (''P'', ''C'')'
WHEN @PandC = 'exclude'
THEN N'= ''P'''
WHEN @PandC = 'just'
THEN N'= ''C'''
ELSE NULL
END
IF @SQL IS NOT NULL
BEGIN
EXECUTE sys.sp_executesql
@statement = @SQL,
@params = N'@Customer integer',
@Customer = @Customer;
END
DROP TABLE #Example
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 18, 2012 at 4:09 am
oooppps sorry i forgot the FROM command
I will try some of the exmples to see how everything works and I will post
thanks
January 18, 2012 at 10:28 am
since I have sql server 2008 R2 the recompile sample worked like a charm
thanks a lot guys
JD:w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply