June 3, 2008 at 4:16 pm
I need to be able to take the following recordset generated by this code:
SELECT Distinct B.OrderNumber
,B.[OrderGroupNumber]
,(SELECT OrderGroupNumber from orders.OrderForm WHERE OrderNumber = @OrderNumber) AS OrigOGN
,A.PaymentTypeXID
,'PaymentType' = Case
WHEN a.PaymentTypeXID IN (0,7,9,16) THEN A.[CCName] COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN a.PaymentTypeXID IN (2) THEN 'Certficate'
ELSE c.PaymentType
END
,C.PaymentType
,C.PaymentCategory
,A.PaymentID
,S.XID
,'PaymentMsg' = CASE
WHEN B.OrderNumber < 5000000 THEN 'For payment information, please contact the Consultant Order Services Team.'
WHEN B.OrderNumber >=5000000 AND A.PaymentTypeXID =3 THEN 'Please refer to your Account Activity report for payment information.'
WHEN B.OrderNumber >=5000000 AND A.PaymentTypeXID =8 THEN 'Please refer to your Account Activity report for payment information.'
ELSE ''
END
,'AccountID' = CASE
WHEN A.[PaymentTypeXID]=0 THEN a.[CClastfour]
WHEN A.[PaymentTypeXID]=1 THEN A.BankAccount--Bank Draft
WHEN A.[PaymentTypeXID]=5 THEN a.[CheckNumber]-- Personal Check
WHEN A.[PaymentTypeXID]=2 THEN a.[GCCode]-- Gift Certficate
WHEN A.[PaymentTypeXID]=3 THEN ''
WHEN A.[PaymentTypeXID]=6 THEN a.[CheckNumber]-- Cashier Check
WHEN A.[PaymentTypeXID]=7 THEN a.[CClastfour]-- Credit Card Credit
WHEN A.[PaymentTypeXID]=8 THEN ''-- Consultant Account
WHEN A.[PaymentTypeXID]=13 THEN ''-- Credit Personal Check
WHEN A.[PaymentTypeXID]=14 THEN '' -- Credit Money Order
WHEN A.[PaymentTypeXID]=4 THEN a.[CheckNumber]-- Money Order
WHEN A.[PaymentTypeXID]=16 THEN a.[CCLastFour] -- Re-occuring Payment
WHEN A.[PaymentTypeXID]=9 THEN a.[CClastFour] -- Blind Credit
WHEN A.[PaymentTypeXID]=11 THEN 'Pay #'+ CONVERT(VARCHAR(20),b.ordernumber) COLLATE SQL_Latin1_General_CP1_CI_AS + CONVERT(VARCHAR(20),a.[ConsultantID]) COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN A.[PaymentTypeXID]=10 THEN a.[BankAccount] -- Re-occuring Payment
WHEN A.[PaymentTypeXID]=12 THEN a.[GCCode]-- Gift Certficate Credit
WHEN A.[PaymentTypeXID]=15 THEN '' -- Credit Cashier Check
END
--,A.[BankAccount]
,'ApprovalCode' = CASE
WHEN A.[PaymentTypeXID]=0 AND P.ResponseAuthorizationCode IS NOT NULL THEN p.ResponseAuthorizationCode
WHEN A.[PaymentTypeXID]=0 AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=2 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=1 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=6 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=5 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=7 AND p.ResponseAuthorizationCode IS NOT NULL THEN p.ResponseAuthorizationCode
WHEN a.[PaymentTypeXID]=7 AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=8 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=13 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=14 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=4 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=16 AND p.ResponseAuthorizationCode IS NOT NULL THEN p.ResponseAuthorizationCode
WHEN a.[PaymentTypeXID]=16 AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=9 AND p.ResponseAuthorizationCode IS NOT NULL THEN p.ResponseAuthorizationCode
WHEN a.[PaymentTypeXID]=9 AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=10 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=11 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=12 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=15 THEN 'APPROVED'
END
,A.[CCName]
,A.[CCLastFour]
,a.CCAuthNumber AS AuthorizationCode
,A.[CheckNumber]
,A.TransactionTotal
,A.[ConsultantID]
INTO #Temp1 FROMdbo.uvw_OrderGroupPayment AS A With (NoLock)
INNER JOIN orders.OrderForm AS B With (NoLock) ON
A.ORderGroupNumber = B.OrderGroupNumber
LEFT OUTER JOIN SharedDimension.dbo.DimPaymentType AS C With (NoLock) ON
A.PaymentTypeXID = C.XID
LEFT OUTER JOIN orders.CCLog AS P With (noLock) ON
A.OrderGroupNumber = P.OrderGroupNumber
LEFT OUTER JOIN SharedDimension.dbo.DimCCTranType AS S WITH (NOLock) ON
P.[TransactionTypeXID] = S.[XID]
WHEREB.OrderNumber = @OrderNumber
I have it going to a temp table. I need to take what is in the temp table and using the following logic to return the correct records:
1. If the only record in temp table has a PaymentXID of 3 then Return PaymentMsg
2. If the only record in temp table has a PaymentXID of 8 then Return PaymentMsg
3. If multiple records returned and any of them have a paymentxid of 3 or 8 just return the remaining records excluding the paymentxid 3 and 8 records. Hope that makes sense.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
June 4, 2008 at 6:35 am
The only way I found that it might work is like this:
DECLARE @rc INT
SELECT @rc = Count(*) from Temp1
IF @rc =1 AND(Select PaymentTypeXID from #Temp1 )= 3
BEGIN
Select PaymentMsg from #Temp1
end
IF @rc =1 AND(Select PaymentTypeXID from #Temp1 )= 8
BEGIN
Select PaymentMsg from #Temp1
end
IF @rc >=2
BEGIN
Select * from #Temp1 WHERE PaymentTypeXID NOT iN (3,8)
end
Drop TABLE #Temp1
Is there a simplistic or better way to do this?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply