Need help on following sql query

Question

This is my current SQL query. I need to modify this

SELECT 
    18 BrokerDealerID,
    5 PortfolioID,
    PeriodEndDate DATE,
    SecurityIdentifier_All.SecurityId,
    MAX(
    (CASE 
        WHEN Securities.Quantity < 0 THEN
        100 + (100 - LocalPriceAmount)
        ELSE
        LocalPriceAmount
     END
        ) /100) Mark
FROM
    Fireball_Reporting..StateStreet_DailyPosition_Second StateStreet 
INNER JOIN
    Fireball_Reporting..SecurityIdentifier_All ON StateStreet.CUSIP = SecurityIdentifier_All.Identifier
INNER JOIN
    Fireball..TradeBySecurityType Securities ON
        Securities.PricingSecurityID = SecurityIdentifier_All.SecurityId AND Securities.Position = 1 AND
(CASE WHEN StateStreet.SecurityName LIKE '% R V %' THEN StateStreet.SharesParValue * -1 ELSE StateStreet.SharesParValue END) = Securities.Quantity
WHERE
    CONVERT(DATETIME, StateStreet.PeriodEndDate) = '2012-10-23' --@PositionDate
GROUP BY
    PeriodEndDate,
    SecurityIdentifier_All.SecurityId

I need to do change in 2nd condition i.e

(CASE WHEN StateStreet.SecurityName LIKE '% R V %' THEN StateStreet.SharesParValue * -1 ELSE StateStreet.SharesParValue END) = Securities.Quantity

I will give you an example.

my select query giving me following output

securityname date       securityid portfolioid type mark               quantity
------------ ---------- ---------- ----------- ---- ------------------ -------------
R V DISH     10/23/2012 4879505    5           CDS  1.0487189900000000 -5000000.0000
R V DISH      10/23/2012 4879505    5           CDS  1.0487189900000000 -2000000.0000
R F DISH    10/23/2012 4879505    5           CDS  0.9512810100000000 3000000.0000

Here above it is giving me 3 records for same security id When I do MERGE using above result it is only taking 1st record directly if i don't check that 2nd condition which checks for Quantity but now i want to check only for When In StateStreet.SecurityName LIKE '% R V %' take the records from above result whose quantity is - if not then take the record whose quantity id +

How could I change that case statement? or directly check for % R V% in select statement where i calculated mark ? Need technical help. I'm new to SQL.

Answer 1

Maybe something like:

(
      (StateStreet.SecurityName LIKE '% R V %' AND Securities.Quantity < 0)
   OR (StateStreet.SecurityName NOT LIKE '% R V %' AND Securities.Quantity >= 0)
)

I was just guessing about which had the "=" (+ could include 0).