I have the following query...


SELECT     PassengerSerialNo, PassengerName, Insurer, Price
FROM         (SELECT     I.PassengerSerialNo, I.PassengerName, I.Insurer,
I.Price, 'I' AS Source
                       FROM          dbo.InsuranceTransactions I
                       WHERE      (I.BookingSerialNo = 3)
                       UNION ALL
                       SELECT     P.PassengerSerialNo, P.PassengerName,
P.Insurer, P.Price, 'P' AS Source
                       FROM         dbo.Passengers P
                       WHERE     (P.BookingSerialNo = 3)) SubQuery
GROUP BY PassengerSerialNo, PassengerName, Insurer, Price
HAVING      (COUNT(*) = 1)


Is it possible to return the negative value of Price if Source = I otherwise
leave it as the original.  If I try adding a CASE statement on my Price
field in the outer query it says I must add Source to the Group By.  But I
don't want to add Source to the Group By because it will return the wrong
records.


Thanks,
Chris

SELECT     PassengerSerialNo, PassengerName, Insurer, Price
FROM         (SELECT     I.PassengerSerialNo, I.PassengerName,
I.Insurer,
I.Price * -1, 'I' AS Source
                       FROM          dbo.InsuranceTransactions I
                       WHERE      (I.BookingSerialNo = 3)
                       UNION ALL
                       SELECT     P.PassengerSerialNo, P.PassengerName,
P.Insurer, P.Price, 'P' AS Source
                       FROM         dbo.Passengers P
                       WHERE     (P.BookingSerialNo = 3)) SubQuery
GROUP BY PassengerSerialNo, PassengerName, Insurer, Price
HAVING      (COUNT(*) = 1)


*** Sent via Developersdex http://www.developersdex.com ***

Hierarchically arranged attributes...mission impossible?
problem: select Max(date) which is less than actual Max(date) ?
Parameterize table name without constructing dynamic query?
Max date in a row
Constraints?