April 11, 2023 at 10:20 pm
Hi
I am trying to write a SQL query that returns the number of working days between two dates. The approach I am taking is the following...
Financial markets are open on non-holidays so doing a select distinct on trade dates would give me the list of all non-holiday dates. I could create a separate table etc but I don't think I need that given the data I have. Financial data is only available on non-holidays b/c markets are closed on holidays and when they are closed there are no trade dates for the missing day.
I need to find the number of trade date records that fall between two dates. For example, suppose the following are returned from the select distinct trade date sub query:
2020-01-02
2020-01-03
2020-01-06
2020-01-07
2020-01-08
If I select START_DATE = 2020-01-03 and END_DATE = 2020-01-07 then the expected result is 3 (03, 06, 07). The trade date may or may not fall within the START_DATE and END_DATE. I am not sure how to implement the last part.
Below is my query:
SELECTT4.UNDERLYING_SYMBOL,
T5.TRADE_DATE,
T4.START_DATE,
T4.END_DATE
FROM(
SELECTT1.UNDERLYING_SYMBOL,
T1.QUOTE_DATE,
T1.EXPIRATION AS START_DATE,
T2.EXPIRATION AS END_DATE
FROMDBO.Table1 AS T1 INNER JOIN DBO.Table2 AS T2 ON T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND T1.QUOTE_DATE = T2.QUOTE_DATE
) AS T4 INNER JOIN
(
SELECTDISTINCT T3.UNDERLYING_SYMBOL, T3.QUOTE_DATE AS TRADE_DATE
FROMDBO.Table3 AS T3
) AS T5 ON T4.UNDERLYING_SYMBOL = T5.UNDERLYING_SYMBOL AND T4.QUOTE_DATE = T5.TRADE_DATE
How do I modify this so it returns the number of TRADE_DATE records that fall between START_DATE and END_DATE?
Thank you
April 12, 2023 at 12:38 am
This is not much to go on as the schema of your tables is unknown. I suspect you would benefit from a classic Calendar table which comes with columns for is_weekend, and is_holiday. You can build one yourself
----------------------------------------------------
April 12, 2023 at 2:03 am
drop table if exists #trades;
go
create table #trades(
underlying_symbol varchar(10) not null,
trade_date date not null);
-- add unique pk constraint spanning both columns
insert #trades(underlying_symbol, trade_date)
select distinct t3.underlying_symbol, t3.quote_date
from dbo.table3;
select t1.underlying_symbol,
t1.quote_date,
t1.expiration as start_date,
t2.expiration as end_date,
t.trade_day_count
from dbo.table1 as t1
join dbo.table2 as t2 on t1.underlying_symbol = t2.underlying_symbol
and t1.quote_date = t2.quote_date
outer apply (select count(*)
from #trades t
where t.underlying_symbol=t1.underlying_symbol
and t.trade_date > t1.expiration
and t.trade_date < t2.expiration) t(trade_day_count);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 12, 2023 at 2:48 am
drop table if exists #trades;
go
create table #trades(
underlying_symbol varchar(10) not null,
trade_date date not null);
-- add unique pk constraint spanning both columns
insert #trades(underlying_symbol, trade_date)
select distinct t3.underlying_symbol, t3.quote_date
from dbo.table3;
select t1.underlying_symbol,
t1.quote_date,
t1.expiration as start_date,
t2.expiration as end_date,
t.trade_day_count
from dbo.table1 as t1
join dbo.table2 as t2 on t1.underlying_symbol = t2.underlying_symbol
and t1.quote_date = t2.quote_date
outer apply (select count(*)
from #trades t
where t.underlying_symbol=t1.underlying_symbol
and t.trade_date > t1.expiration
and t.trade_date < t2.expiration) t(trade_day_count);
Thank you so much!
I am getting this error. How do I fix it?
(573 rows affected)
Msg 468, Level 16, State 9, Line 14
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Completion time: 2023-04-11T19:45:31.9180176-07:00
April 12, 2023 at 11:22 am
I am getting this error. How do I fix it?
(573 rows affected)
Msg 468, Level 16, State 9, Line 14
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Completion time: 2023-04-11T19:45:31.9180176-07:00
Carefully. The line 14 equality is: t1.underlying_symbol = t2.underlying_symbol so both columns are from your tables, table1 and table2. For me there's not much to go on. Immediate fix (kludge?) syntax-wise is the collation may be specified "on the fly" in the JOIN. Google the error message and you'll see
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 12, 2023 at 4:59 pm
water490 wrote:I am getting this error. How do I fix it?
(573 rows affected)
Msg 468, Level 16, State 9, Line 14
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Completion time: 2023-04-11T19:45:31.9180176-07:00Carefully. The line 14 equality is: t1.underlying_symbol = t2.underlying_symbol so both columns are from your tables, table1 and table2. For me there's not much to go on. Immediate fix (kludge?) syntax-wise is the collation may be specified "on the fly" in the JOIN. Google the error message and you'll see
I figured it out. here is the fix:
create table #trades(
underlying_symbol varchar(10) COLLATE Latin1_General_CI_AS not null,
trade_date date not null);
-- add unique pk constraint spanning both columns
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply