Complex substring query and value conversion help...

Ok, I have to convert the dollar values of some of my fields in a
query, if the field to the left (in the left column) has a word that
part of the word has "debit" in it. Like this:
if(t5.description = "TransferDebit")
{
DECLARE @.Num1 int
SET @.Num1 = t6.amount
SELECT -@.Num1
}
Thanks,
TrintIt is not clear from your post what you are exactly trying to do. What are
t5 and t6 in your sample code? Are you trying to extract a portion of the
string value in some column?
Perhaps you can use SUBSTRING function to extract a part of a string. To
find if "debit" is a part of the value in description column, you can use
CHARINDEX or PATINDEX. Details of all these functions, with examples, can be
found in SQL Server Books Online.
Anith|||Please provide actual DDL, sample data, and expected results
[http://www.aspfaq.com/etiquette.asp?id=5006]. how are T5 and T6 related? we
need to see the structure and data you are actually working with.
Without better specification, I am going to work from the premise that all
this information is actually in a single table. Do you want to actually
change the values as they are stored, or do you just want to display them in
a negative format?
If the former:
UPDATE T5 SET amount = -amount WHERE description LIKE '%Debit%'
If the latter:
SELECT
CASE WHEN description LIKE '%Debit%' THEN -amount ELSE amount END AS
Amount,
<other columns to display...>
FROM T5
"trint" <trinity.smith@.gmail.com> wrote in message
news:1122047085.356296.239470@.o13g2000cwo.googlegroups.com...
> Ok, I have to convert the dollar values of some of my fields in a
> query, if the field to the left (in the left column) has a word that
> part of the word has "debit" in it. Like this:
> if(t5.description = "TransferDebit")
> {
> DECLARE @.Num1 int
> SET @.Num1 = t6.amount
> SELECT -@.Num1
> }
> Thanks,
> Trint
>|||Jeremy,
the "lookup" for this description is in a table called "amount_type".
It somehow needs to be in this code here:
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount END) AS Purchase
thanks,
Trint|||Jeremy,
the "lookup" for this description is in a table called "amount_type".
It somehow needs to be in this code here:
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount END) AS Purchase
thanks,
Trint|||Jeremy,
Actually, here is the code:
SELECT t1.MemberId, t1.PeriodID,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount) END) AS
Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount END) AS
QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id
WHERE (t4.TravelDetailMemberTypeId = 1) AND (t1.MemberId = 12391)
AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID
thanks,
Trint