August 5, 2015 at 12:50 am
Hi guys, hope you can help out with this thanks
I have this data below :
isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | DEM | 013 | 2009 | 5 | 8
DEX | XFFA | EUR | 013 | 2014 | 11 | 25
get this data by running the following query :
select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFA') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt
what's needed is to get the earliest record all times, no mater the currency :
isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | EUR | 013 | 2014 | 11 | 25
thanks in advacne
August 5, 2015 at 12:52 am
ERRATUM.
Correct query :
select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFF') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt
August 5, 2015 at 8:05 am
dmartins.dam (8/5/2015)
ERRATUM.Correct query :
select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year),max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFF') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt
It appears you want the most recent event in this query. Your specification of separate MAX functions for each of the components of the date isn't going to get you what you need. It will end up returning the MAX value for each field separately, and the resulting date would not necessarily be valid (think max value for month as 9, and then max value for day being 31, having come from records dated 8/31 and 9/10, with the query being run in early September). You can't get the most recent event by treating the parts of the date separately. You have to look at the date as a whole. If you have a field that contains the entire date, you should use that instead of the construction I provide below:
WITH RAW_VALUES AS (
SELECT b.isin, a.market, a.ccy, a.stock_mkt,
CAST(a.[year] AS char(4)) + '-' + RIGHT(CAST(a.[month] AS varchar(2)), 2)+ RIGHT(CAST(a.[day] AS varchar(2)), 2) AS THE_DATE
FROM market_table AS a
INNER JOIN isin_table AS b
ON a.id = b.id
WHERE a.isin_closing_date = 0
AND a.market IN ('XFFA', 'XFFF')
AND b.isin_type = 'I'
AND b.isin = 'DEX'
)
SELECT RV.isin, RV.market, RV.ccy, RV.stock_mkt, RV.THE_DATE
FROM RAW_VALUES AS RV
WHERE RV.THE_DATE = (SELECT MAX(THE_DATE) FROM RAW_VALUES)
If you still get multiple records, you can use TOP (1) in the SELECT statement and then add an ORDER BY clause to determine how to resolve the tie that occurred.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2015 at 9:36 am
This is one of the reasons to avoid using separate columns for dates. You should use date data types. Not only will you be able to handle dates more efficiently, but you also save space (using 4 int columns will take 12 bytes or using 1 smallint and 2 tinyints will use 4 bytes, while using a date will only take 4 bytes).
Depending on what you want, here are 2 options. One will return the most recent for each grouping and the other will return only one result.
SELECT b.isin,
a.market,
a.ccy,
a.stock_mkt,
CAST( CAST( MAX((a.year*10000) + (a.month * 100) + a.day) AS char(8)) AS date) AS date,
MAX((a.year*10000) + (a.month * 100) + a.day) / 10000 AS year,
(MAX((a.year*10000) + (a.month * 100) + a.day) % 10000)/100 AS month,
MAX((a.year*10000) + (a.month * 100) + a.day) % 100 AS day
FROM market_table a
INNER JOIN isin_table b on a.id = b.id
WHERE a.isin_closing_date=0
AND a.market in ('XFFA', 'XFFF')
AND b.isin_type= 'I'
AND b.isin = 'DEX'
group by b.isin,
a.market,
a.ccy,
a.stock_mkt
SELECT TOP(1)
b.isin,
a.market,
a.ccy,
a.stock_mkt,
CAST( CAST((a.year*10000) + (a.month * 100) + a.day AS char(8)) AS date) AS date,
a.year,
a.month,
a.day
FROM market_table a
INNER JOIN isin_table b on a.id = b.id
WHERE a.isin_closing_date=0
AND a.market in ('XFFA', 'XFFF')
AND b.isin_type= 'I'
AND b.isin = 'DEX'
ORDER BY a.year DESC,
a.month DESC,
a.day DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply