Frustrated SQL newbie

Transact-SQL
1

Hi All, apologies in advance. I understand basic SQL and am learning. Part of my learning philosophy is the do things right and elegantly. Not so my code (yet).

The challenge - dumbing this down, I am working on a single simply table of constituents of the ASX (Australian Stock Exchange) indices (such as the asx200 (xjo) and asx300 (xko). These are recorded at specific times when an index is updated. Mostly 1st of each month but historically this could happen anytime.

I am trying to measure the impact of a share entering into the asx200 (~top 200 by market cap). I am assuming it is already in the asx300 (~top 300 by market cap).

What I am trying to achieve is to get the market cap off the first 12 months (up to) AND the market cap prior to the asx200 entry. The latter is giving me grief.

To give you some real data:

select * from asx200_entries
where issue='APX'

issue entry_date
APX 2018-07-01 00:00:00.000

So APX entered the ASX200/XJO on 1st of June.

The table holding all data has a form of:

select * from index_constituents
where issue = 'APX'
and market_index in ('XJO','XKO')

market_index record_date exchange issue sector market_cap weight
XJO 2018-07-01 00:00:00.000 ASX APX Information Technology 1422230000.00 0.08
XJO 2018-08-01 00:00:00.000 ASX APX Information Technology 1156040000.00 0.06
XJO 2018-09-01 00:00:00.000 ASX APX Information Technology 1631870000.00 0.09
XJO 2018-10-01 00:00:00.000 ASX APX Information Technology 1487100000.00 0.08
XKO 2017-10-01 00:00:00.000 ASX APX Information Technology 509340000.00 0.03
XKO 2017-11-01 00:00:00.000 ASX APX Information Technology 543556000.00 0.03
XKO 2017-12-01 00:00:00.000 ASX APX Information Technology 698604000.00 0.04
XKO 2018-01-01 00:00:00.000 ASX APX Information Technology 872067000.00 0.05
XKO 2018-02-01 00:00:00.000 ASX APX Information Technology 967057000.00 0.05
XKO 2018-03-01 00:00:00.000 ASX APX Information Technology 1099310000.00 0.06
XKO 2018-04-01 00:00:00.000 ASX APX Information Technology 945229000.00 0.05
XKO 2018-05-01 00:00:00.000 ASX APX Information Technology 1024970000.00 0.06
XKO 2018-06-01 00:00:00.000 ASX APX Information Technology 1085580000.00 0.06
XKO 2018-07-01 00:00:00.000 ASX APX Information Technology 1422230000.00 0.08
XKO 2018-08-01 00:00:00.000 ASX APX Information Technology 1156040000.00 0.06
XKO 2018-09-01 00:00:00.000 ASX APX Information Technology 1631870000.00 0.09
XKO 2018-10-01 00:00:00.000 ASX APX Information Technology 1487100000.00 0.08

The query that extracts up to 12 months (eventually):

select xjo.issue, xko.record_date, xko.market_cap
from asx200_entries xjo
left join index_constituents xko
on xjo.issue=xko.issue
where
xko.record_date >= xjo.entry_date
and xko.market_index='XKO'
and xjo.issue='APX' -- Dev

issue record_date market_cap
APX 2018-07-01 00:00:00.000 1422230000.00
APX 2018-08-01 00:00:00.000 1156040000.00
APX 2018-09-01 00:00:00.000 1631870000.00
APX 2018-10-01 00:00:00.000 1487100000.00

Perfect.

The query that gets the record details of the record_date prior to the entry (from asx300) is:

select top (1) xjo.issue, xko.record_date, xko.market_cap
from asx200_entries xjo
left join index_constituents xko
on xjo.issue=xko.issue
where
xko.record_date < xjo.entry_date
and xko.market_index='XKO'
and xjo.issue='APX' -- Dev
order by xko.record_date DESC

issue record_date market_cap
APX 2018-06-01 00:00:00.000 1085580000.00

Perfect - the month before the asx200 list.

HOWEVER - when I union these the [order by] is applied to the union result and not the second query. Tried bracketing these and all sorts of tricks (like swapping the upper and lower parts of the union.

I know I can hack something to make this happen but would like to do this right and elegantly.

I would really welcome any/all suggestions.

Thanking you in advance.

PS the total query is:

select xjo.issue, xko.record_date, xko.market_cap
from asx200_entries xjo
left join index_constituents xko
on xjo.issue=xko.issue
where
xko.record_date >= xjo.entry_date
and xko.market_index='XKO'
and xjo.issue='APX' -- Dev

UNION

select top (1) xjo.issue, xko.record_date, xko.market_cap
from asx200_entries xjo
left join index_constituents xko
on xjo.issue=xko.issue
where
xko.record_date < xjo.entry_date
and xko.market_index='XKO'
and xjo.issue='APX' -- Dev
order by xko.record_date DESC

--order by xjo.issue, xko.record_date

2

hi

if i understand correctly

data1
union
data2

you want to order by data2 only

am i correct
please let me know then i can work on solution :slight_smile:

3

Hi Harris,
(
(
select dataset1
)
union
(
select top (1) dataset2
order by <<< My nemesis - only want the last record
)
) order by (overall result in chrono order)

4

got it .. working on it

1 Like
5

hi

please check my solution

please let me know if ok

create data 
use tempdb
go

drop table #data1 
go 

create table #data1
(
id int,
name varchar(100),
age int
)
go

insert into #data1 select 1,'har',43
insert into #data1 select 5,'pra',34
insert into #data1 select 3,'sam',12
insert into #data1 select 4,'jef',23
go 

drop table #data2
go 

create table #data2
(
pd int,
address varchar(100),
Wine int
)
go

insert into #data2 select 20,'ok street',12
insert into #data2 select 45,'vr nagar',3
insert into #data2 select 10,'prom blvd',4
insert into #data2 select 30,'thank strt',20
go
SQL 
SELECT * 
FROM   #data1 
UNION 
(SELECT a.* 
 FROM   (SELECT TOP 1 * 
         FROM   #data2 
         ORDER  BY pd DESC) a)
result

image
image.png994×478 7.87 KB

1 Like
6

Just brilliantly simple. That wrapper was the right answer and the missing bit.
Thank you so much!!

7

On a side note - this query can (and should) be rewritten as an inner join. It is already working as an inner join because you have included the outer table (xko) in the filter/where clause.

 Select xjo.issue
      , xko.record_date
      , xko.market_cap
   From dbo.asx200_entries          xjo
  Inner Join dbo.index_constituents xko On xko.issue = xjo.issue
                                       And xko.record_date >= xjo.entry_date
  Where xko.market_index = 'XKO'
    And xjo.issue = 'APX' -- Dev