March 11, 2012 at 7:45 pm
Hi,
Can any one help me why this query don't work when I have A.PandL in SELECT.
SELECT
A.Account,
A.Symbol,
A.PandL,
Min(A.Timee),
CASE
WHEN Min(A.Timee) BETWEEN '00:00:01' AND '09:30:00' THEN 'BeforeMktOpen'
WHEN Min(A.timee) BETWEEN '09:30:00' AND '10:00:00' THEN 'FirstHalfHour'
WHEN Min(A.timee) BETWEEN '10:00:00' AND '15:30:00' THEN 'Day'
WHEN Min(A.timee) BETWEEN '15:30:00' AND '16:00:00' THEN 'LastHalfHour'
ELSE 'AfterMktClose'
END AS buckets
FROM
( SELECT
RPM.Account,
RPM.Symbol,
RPM.PandL,
RMM.Side,
RMM.Creation_Date,
RMM.Timee
FROM
Redi_Position_Monitor RPM,
Redi_Message_Monitor RMM
WHERE
RPM.Position = 0
AND RPM.Symbol = RMM.Symbol
AND RPM.Account = RMM.AccountNumber
AND RPM.Creation_Date = RMM.Creation_Date
) A
GROUP BY A.Account, A.Symbol
Query works fine without A.PandL, but I want PnL as well.
Thank you
March 11, 2012 at 8:42 pm
Try this:
SELECT
A.Account,
A.Symbol,
A.PandL,
Min(A.Timee),
CASE
WHEN Min(A.Timee) BETWEEN '00:00:01' AND '09:30:00' THEN 'BeforeMktOpen'
WHEN Min(A.timee) BETWEEN '09:30:00' AND '10:00:00' THEN 'FirstHalfHour'
WHEN Min(A.timee) BETWEEN '10:00:00' AND '15:30:00' THEN 'Day'
WHEN Min(A.timee) BETWEEN '15:30:00' AND '16:00:00' THEN 'LastHalfHour'
ELSE 'AfterMktClose'
END AS buckets
FROM
( SELECT
RPM.Account,
RPM.Symbol,
RPM.PandL,
RMM.Side,
RMM.Creation_Date,
RMM.Timee
FROM
Redi_Position_Monitor RPM,
Redi_Message_Monitor RMM
WHERE
RPM.Position = 0
AND RPM.Symbol = RMM.Symbol
AND RPM.Account = RMM.AccountNumber
AND RPM.Creation_Date = RMM.Creation_Date
) A
GROUP BY A.Account, A.Symbol, A.PandL
You missed A.PandL in Group By clause.
March 12, 2012 at 12:09 am
When you run this query, you get a really good description in the error of what is wrong...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply