SQL Select on Date Dimension - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Select on Date Dimension

Author

Topic

Wardy_Wonderland
Starting Member

3 Posts

Posted - 2009-07-30 : 10:25:05

All,

I have a date dimension with all dates up to 2015. I have a working day column in this table which is populated with 1 for a working day and 0 for non-working days.

My question is this:-
How could I get the correct date for the 5th working day for every month up to 2015?

If I had any hair left I would be tearing it out!

Many thanks for your halp in advance.

Wardy.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-30 : 10:27:08

Can you show us some sample data from your table.

EDIT: I don't know your data...but maybe this?

declare @t table (workdate datetime, workingday bit)
insert @t
select '2009-07-01', 1 union all
select '2009-07-02', 1 union all
select '2009-07-03', 0 union all
select '2009-07-04', 0 union all
select '2009-07-05', 0 union all
select '2009-07-06', 1 union all
select '2009-07-07', 1 union all
select '2009-07-08', 1 union all
select '2009-07-09', 1 union all
select '2009-08-01', 1 union all
select '2009-08-02', 0 union all
select '2009-08-03', 0 union all
select '2009-08-04', 1 union all
select '2009-08-05', 1 union all
select '2009-08-06', 1 union all
select '2009-08-07', 1 union all
select '2009-08-08', 0 union all
select '2009-08-09', 0

select * from 
(
select row_number() over(partition by convert(varchar(6),workdate,112) order by workdate) as seq
,* from @t where workingday = 1
) t
where t.seq = 5

Wardy_Wonderland
Starting Member

3 Posts

Posted - 2009-07-30 : 10:55:12

Excellent vijayisonly that's just what I'm after - thanks for your very quick response and help

Wardy

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-30 : 11:08:39

welcome...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources