Displaying data of 10 years (only Business Days to be selected)

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

SQL Server 2000 Forums

SQL Server Development (2000)

Displaying data of 10 years (only Business Days to be selected)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-04 : 09:10:01

Srinivas writes "If i have a table that consists of data of prices listed on a daily basis( only for working days), for 10 years and i want to select data of the last business day of each month, how do i query that. I have this problem as there are saturdays and sundays and there might be a holiday ."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-04 : 09:18:23

If you are relying strictly on the data you have, then this should work:

SELECT A.* FROM myTable A
INNER JOIN (SELECT Max(OrderDate) MaxDate FROM myTable GROUP BY Year(OrderDate), Month(OrderDate)) M
ON (A.OrderDate=M.MaxDate)

If you need date values that are NOT included in this table, you'll need to construct of table of valid working days and JOIN it to this query. You can also try this article:

http://www.sqlteam.com/item.asp?ItemID=3332

cgseenu
Starting Member

3 Posts

Posted - 2001-12-04 : 22:07:48

Thanks,
This looks good
Can the same be done to get the first Business day of each week

quote:

If you are relying strictly on the data you have, then this should work:

SELECT A.* FROM myTable A
INNER JOIN (SELECT Max(OrderDate) MaxDate FROM myTable GROUP BY Year(OrderDate), Month(OrderDate)) M
ON (A.OrderDate=M.MaxDate)

If you need date values that are NOT included in this table, you'll need to construct of table of valid working days and JOIN it to this query. You can also try this article:

http://www.sqlteam.com/item.asp?ItemID=3332

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-05 : 04:55:08

1900-01-01 was a Monday.


SELECT A.* FROM myTable A
INNER JOIN (
  SELECT MIN(OrderDate) FirstWeekDay
  FROM myTable
  GROUP BY DATEDIFF(d, '1900-01-01', OrderDate) / 7) M
ON A.OrderDate = M.FirstWeekDay

Edited by - Arnold Fribble on 12/05/2001 04:59:34

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-05 : 08:41:02

You can also use:

SELECT A.* FROM myTable A
INNER JOIN (SELECT MIN(OrderDate) FirstWeekDay FROM myTable GROUP BY DATEPART (wk,OrderDate)) M
ON A.OrderDate = M.FirstWeekDay

All these solutions assume that you don't have non-working days in the table, which I forgot to ask about last time.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-05 : 13:41:18

That only returns dates for the first year that that week number has results. Since DATEPART(wk,) gives the week number in the year, it has unpleasant edge effects in this instance:


SELECT CONVERT(varchar, dt, 102), DATEPART(wk, dt),
  DATEPART(dw, dt), DATENAME(dw, dt)
FROM (
  SELECT CONVERT(datetime, '2000-01-01') dt
  UNION ALL SELECT '2000-12-31'
  UNION ALL SELECT '2001-01-01'
  UNION ALL SELECT '2001-12-31'
  UNION ALL SELECT '2002-01-01'
  UNION ALL SELECT '2002-12-31'
) a

results in


2000.01.01   1     7   Saturday
2000.12.31  54     1   Sunday
2001.01.01   1     2   Monday
2001.12.31  53     2   Monday
2002.01.01   1     3   Tuesday
2002.12.31  53     3   Tuesday

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-05 : 14:05:53

quote:
That only returns dates for the first year that that week number has results

oooooooooooooooooops! Thanks Arnold, I missed that.

I added the year to this one, I think it will work:

SELECT A.* FROM myTable A
INNER JOIN (SELECT Year(OrderDate) Yr, MIN(OrderDate) FirstWeekDay FROM myTable
GROUP BY Year(OrderDate), DATEPART (wk,OrderDate)) M
ON (Year(A.OrderDate)=M.Yr AND A.OrderDate=M.FirstWeekDay)

Edited by - robvolk on 12/05/2001 14:14:40

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-05 : 14:52:33

Using that, you still get the effect that both Monday 2001-12-31 (in week 53) and Wednesday 2002-01-02 (in week 1) are likely to be included in the results. This might be what's wanted: businesses don't always define weeks the same way as calendars, but it seems pretty odd to me.

Edited by - Arnold Fribble on 12/05/2001 14:53:29

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources