Lowest Ratio from Group[solved]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Lowest Ratio from Group[solved]

Author

Topic

puvpul
Starting Member

6 Posts

Posted - 2011-11-23 : 04:16:30

Dear Experts,
I've the following three tables prices, cData, securities. I need to get lowest ratio from different sectors.

#Prices Table
Ticker           date_       close_
---------------- ---------- --------
ABBANK           2010-01-02  15.00
ABBANK           2010-01-03  16.00
ABBANK           2010-01-04  13.00
AC               2010-01-02  10.00
AC               2010-01-03  15.00
AC               2010-01-04  8.00
AG               2010-01-02  30.00
AG               2010-01-03  40.00
AG               2010-01-04  20.00
ALARA            2010-01-02  6.00
ALARA            2010-01-03  7.00
ALARA            2010-01-04  10.00
AT               2010-01-02  10.00
AT               2010-01-03  9.00
AT               2010-01-04  8.00
BASIA            2010-01-02  20.00
BASIA            2010-01-03  30.00
BASIA            2010-01-04  50.00
#securities table
Ticker           name            sector
---------------- -----------------------
ABBANK           abbank          Bank
AC               AC              Bank
AG               ag              Retail
ALARA            alara           IT
AT               at              IT
BASIA            basia           Retail

#cData table
Ticker  ShareNums Nprofit
ABBANK	100.00	10.00
AC	200.00	300.00
AG	600.00	300.00
ALARA	90.00	15.00
AT	50.00	500.00
BASIA	80.00	20.00

i have created SP that gives follwing result:

Ticker           sector         Latest     PE
---------------- --------------------------------
AC               Bank          2010-01-04  5.33
ABBANK           Bank          2010-01-04  130.0
AT               IT            2010-01-04  0.80
ALARA            IT            2010-01-04  60.0
AG               Retail        2010-01-04  40.0
BASIA            Retail        2010-01-04  200.0

I actually need the following result:

Ticker           sector         Latest     PE
---------------- --------------------------------
AC               Bank          2010-01-04  5.33
AT               IT            2010-01-04  0.80
AG               Retail        2010-01-04  40.0

Thx in advance for your help.

quote:
StoredProc:select
c.Ticker, s.sector, l.Latest,
(c.ShareNums * p.Close_)/(c.NProfit) as PE
from cData c
join (
select Ticker, max(date_) as Latest
from prices
group by Ticker
) l
on c.Ticker=l.Ticker
join prices p
on l.Ticker=p.Ticker
and l.latest=p.date_
join securities s
on c.ticker=s.ticker
order by s.sector;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-23 : 04:30:20

Using ctes because it can just be dumped on top of what you have and I suspect there will be more to come

;with cte as
(
c.Ticker, s.sector, l.Latest,
(c.ShareNums * p.Close_)/(c.NProfit) as PE
from cData c
join (
select Ticker, max(date_) as Latest
from prices
group by Ticker
) l
on c.Ticker=l.Ticker
join prices p
on l.Ticker=p.Ticker
and l.latest=p.date_
join securities s
on c.ticker=s.ticker
) ,
cte2 as
(select *, seq = ROW_NUMBER() over (partition by sector order by PE) from cte)
select *
from cte2
where seq = 1
order by sector

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

puvpul
Starting Member

6 Posts

Posted - 2011-11-23 : 06:52:13

Thanks a lot nigelrivett. i never didn't use cte. guess i've to learn now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources