Common table exp

Question

Post reply

Common table exp

PSB

SSChampion

Points: 13195
More actions
March 8, 2011 at 2:52 pm

#236252

WITH Sales_CTE (terminalid, LastLoadDate, LoadValue)
AS
(
SELECT tid, MIN(schedloaddate) AS LastLoadDate, SUM(amount) as LoadValue
FROM [dbo].[A]
WHERE schedloaddate < GETDATE()
GROUP BY tid
)
declare @loaddate as datetime
select @loaddate = activitytime
FROM [ATMManagerPro_TW].[dbo].[T_TxnDetail]
where txnTYpeid in ( 9, 107 )
and activitydate >= LastLoadDate
if @@rowcount > 0
begin
SELECT
T,
@loaddate AS LastLoadDate,
LoadValue,
COUNT(Amount),
--SUM (
--CASE WHEN TxnTypeID IN (1,50) AND ResponseCodeID = 1 AND RejectCodeID = 1 THEN 1 ELSE 0 END
--) AS TransactionSinceLoad,
SUM(Amount) AS TotalDispensed,
(LoadValue - SUM(Amount)) AS TBalance
FROM [dbo]. txn FULL OUTER JOIN Sales_CTE ON txn.t = Sales_CTE.tid
WHERE ActivityDate >=LastLoadDate
AND TxnTypeID IN (1,50) AND ResponseCodeID = 1 AND RejectCodeID = 1
GROUP BY T, LastLoadDate, LoadValue
end
ELSE
BEGIN
SELECT
T,
LastLoadDate,
LoadValue,
COUNT(Amount),
--SUM (
--CASE WHEN TxnTypeID IN (1,50) AND ResponseCodeID = 1 AND RejectCodeID = 1 THEN 1 ELSE 0 END
--) AS TransactionSinceLoad,
SUM(Amount) AS TotalDispensed,
(LoadValue - SUM(Amount)) AS TBalance
FROM [dbo]. txn FULL OUTER JOIN Sales_CTE ON txn.t = Sales_CTE.tid
WHERE ActivityDate >=LastLoadDate
AND TxnTypeID IN (1,50) AND ResponseCodeID = 1 AND RejectCodeID = 1
GROUP BY T, LastLoadDate, LoadValue
END
ORDER BY T;
I cannot get the If else part of the query working.
Please help.
Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

CTE's are only good for the immediately following statement, which in this case is the SELECT for the @loaddate.

Might I recommend a view in this case?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Wow! Pretty good, Joe. Great post and you didn't call the user any names or infer any insults. I'm impressed. Seriously. Nice change.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)