October 25, 2012 at 11:15 am
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 don't want to match the Quantity but I need to be ensure that StateStreet.SharesParValue is positive or negative
because depending upon them I'm calculating Mark like following
MAX(
(CASE
WHEN Securities.Quantity < 0 THEN
100 + (100 - LocalPriceAmount)
ELSE
LocalPriceAmount
END
) /100) Mark
Now my concern is how could I change this
"(CASE WHEN StateStreet.SecurityName LIKE '% R V %' THEN StateStreet.SharesParValue * -1 ELSE StateStreet.SharesParValue END) = Securities.Quantity"
without considering Securities.Quantity i dont need to check and match wether it is equal or not i just need to ensure that whether the StateStreet.SharesParValue is positive or negative
How could I change that case statement need technical help ? I'm new to sql
October 25, 2012 at 11:21 am
I'm not clear on what you're asking. You need help rewriting the Case statement, but I'm not sure what you need it to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2012 at 11:25 am
Hi yes i need help in CASE statement my need is i need to only check wether that StateStreet.SharesParValue is positive or negative.
I was trying to put this case statement in WHERE clause (CASE WHEN StateStreet.SecurityName LIKE '% R V %' THEN StateStreet.SharesParValue * -1 ELSE StateStreet.SharesParValue END) but getting error: An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'
October 25, 2012 at 11:34 am
That error means you have something in the Where clause that doesn't compare two things.
In the first copy, you have "= Securities.Quantity" after the Case statement. That makes it a "boolean operator" (look up "boolean" online if you aren't sure what that means).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2012 at 11:40 am
ok but now the situation is i dont want to check with quantity i only need to know that quantity is '-' or '+'
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/201248795055 CDS1.0487189900000000-5000000.0000
BID R V 10/23/201248795055 CDS1.0487189900000000-2000000.0000
R F Fores 10/23/201248795055 CDS0.95128101000000003000000.0000
now in my original sql query it is checking for 'R V' if yes then quantity * -1 for other table i.e StateStreet.SharesParValue and check quantity matching or not
but because of that i missed some data which is having 'R V' but quantity not matching
so i need to only ensure for 'R V' stuff please help me
October 25, 2012 at 11:47 am
Why are you putting the case statement in a where clause. Maybe bring back relevant values and then pick and choose with the case statement in select clause. You can have more than one "when" in a CASE statement. I would work on cleanin up your code as well. Another note, I don't recommend putting details about your data structure on a forum, why not create a simple table to outline your needs. Your query is very hard to read and it is hard to help you.
SELECT
18 BrokerDealerID, ---Are you aliasing column 18 as BrokerdealerID????
5 PortfolioID,
PeriodEndDate DATE,
SecurityIdentifier_All.SecurityId,
Mark = MAX((CASE WHEN Securities.Quantity < 0 THEN 100 + (100 - LocalPriceAmount) ELSE LocalPriceAmount END) /100)
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 StateStreet.SharesParValue <> 0 -----
WHERE
CONVERT(DATETIME, StateStreet.PeriodEndDate) = '2012-10-23' --@PositionDate
GROUP BY
PeriodEndDate,
SecurityIdentifier_All.SecurityId
October 25, 2012 at 11:55 am
thanks for help
I'm getting all the records correctly there are 3 records for same security id and because of this MERGE statement is not updating my all 3 statements MERGE is only taking 1st statement
but i want all of them then what should i do?
48795055CDS131.0487189900000000-5000000.0000
48795055CDS131.0487189900000000-2000000.0000
48795055CDS130.95128101000000003000000.0000
October 25, 2012 at 12:01 pm
I don't understand, are you trying to insert or update these values?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply