2

I have a table of stock prices and need to get prices for the 1st day of each week. This SQL in the WHERE clause works well,

DATEADD(ww, DATEDIFF(ww,0,PriceDt), 0)

except when the market is closed on Monday. Labor Day is a good example. I thought using COALESCE would give me the price on Tuesday if one were unavailable for Monday, but this didn't work.

coalesce(DATEADD(ww, DATEDIFF(ww,0,PriceDt), 0), DATEADD(ww, DATEDIFF(ww,0,PriceDt), 1)).

Can someone help with this?

declare @t table (PriceDt datetime, Symbol nvarchar(10), OpenPric float, ClosePrice float)

insert @t values ('2010-08-02 00:00:0.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-08-09 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-08-16 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-08-23 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-08-30 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-09-07 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-09-13 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-09-20 00:00:00.000', 'SYM', 15.00, 15.10)
insert @t values ('2010-09-27 00:00:00.000', 'SYM', 15.00, 15.10)

select * from @t
where PriceDt = coalesce(DATEADD(ww, DATEDIFF(ww,0,PriceDt), 0), DATEADD(ww, DATEDIFF(ww,0,PriceDt), 1))

(missing 2010-09-07 00:00:00.000 in the result)

2010-08-02 00:00:00.000 SYM 15  15.1
2010-08-09 00:00:00.000 SYM 15  15.1
2010-08-16 00:00:00.000 SYM 15  15.1
2010-08-23 00:00:00.000 SYM 15  15.1
2010-08-30 00:00:00.000 SYM 15  15.1
2010-09-13 00:00:00.000 SYM 15  15.1
2010-09-20 00:00:00.000 SYM 15  15.1
2010-09-27 00:00:00.000 SYM 15  15.1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3786650
  • 23
  • 2
  • 2
    If you do things like this all the time. I would highly recommend creating a Calendar table if that is an option. There are scripts out there to do this already. The table can include things like isHoldiay, quarter, weekdays, weekends, etc. Makes these types of task much easier. – SQLChao Jun 29 '14 at 01:33
  • What DBMS server ? SQL Server / Sybase ASE ? What version (`SELECT @@VERSION`) ? – Bogdan Sahlean Jun 29 '14 at 05:59
  • 1
    You cannot definitely use COALESCE function here, because the first day of the week from DATEADD(ww, DATEDIFF(ww,0,PriceDt), 0) will not be null. You need another column to decide when to take first day of the week and when to the next day, probably a holiday table – Kiran Hegde Jun 29 '14 at 08:12
  • calendar table like JChao suggests is the way to go. – M.Scherzer Jun 29 '14 at 10:13

1 Answers1

1

This will give you the earliest date that exists in the table for each week (assuming that your week starts on Monday):

select min(Pricedt) Pricedt
from @t
group by DATEADD(ww, DATEDIFF(ww,0,PriceDt), 0)

Now you can just join that result to your table to get the prices for whatever is the first day of the week that has data entered:

select t.Pricedt, t.Symbol, t.OpenPric, t.ClosePrice
from
(
    select min(Pricedt) Pricedt
    from @t
    group by DATEADD(ww, DATEDIFF(ww,0,PriceDt), 0)
) d 
join @t t on d.Pricedt = t.PriceDt
Jerrad
  • 5,240
  • 1
  • 18
  • 23