TSQL Query that contains consecutive weekdays as a column

daforga
daforga used Ask the Experts™
on
I would like to write a select statement that fetches records from a table along with a column containing sequential weekdays with the current date as the first value in that column.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT col1, col2, seq_week_days from your_table order by date_col desc;

Author

Commented:
As it stands, there is no sequential seq_week_days column in the table.  I want to select col1,col2 and create a col3 that contains a sequentially incremented date beginning with a value of getdate().

Author

Commented:
Sorry if my origional question was confusing.  Let me give a better example.  As it stands, there is no sequential seq_week_days column in the table.  I want to select col1,col2 and create a col3 that contains a sequentially incremented date beginning with a value of getdate().
If I wrote(I will use today'sdate in the example):
SELECT col1,col2,getdate() as seq_date from mytable and there were 25 rows in the table iwould get
col1                 col2                     seq_date
-----------------------------------------------------------
col1_value1    col2_value1      10-21-2009
col1_value2    col2_value2      10-22-2009
col1_value3    col2_value3      10-23-2009
col1_value4    col2_value4      10-26-2009  --skipped weekend
col1_value5    col2_value5      10-27-2009
etc for  the reamaining rows in the query.


Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi,
How do you know which records belong to which day?


Regards

Author

Commented:
This what the query is to do.  The records in row one would be assigned GetDate(), row 2 would be assigned a next weekday value.  
How do you define 'row one', do you have a row number field?

Author

Commented:
Here is a live example
SELECT sequencenumber as sequence,summary,getdate() as ExpectedCompletionDate from masteractionplanstep where masteractionplan_id = @plan_ID.
This returns 27 rows  with the 3rd column (ExpectedCompletionDate) containing today's date for all  27 rows of the query return.  What is desired is that the each ExpectedCompletionDate column in the table would hold the a value incremented to contain a date that is one weekday larger than the row above it.

Commented:
Hope this sample helps.
if OBJECT_ID('tempdb..#t1') is not null
drop table #t1
 
Create Table #t1 (pkid int identity(1,1), col1 int, col2 int )
 
Insert #t1(col1, col2)
Select 1,1 union
select 2,2 union
select 3,3 union
select 4,4 
 
 
Select
	#t1.col1
,	#t1.col2
,	nested0.SeqDate
from #t1
inner join (
	select
		t1A.pkid
		, [SeqDate] = Getdate() + COUNT(t1b.pkid)
	From #t1 as t1A
	left join #t1 as t1b
	on t1A.pkid > t1b.pkid
	group by t1A.pkid
) as nested0
on #t1.pkid = nested0.pkid

Open in new window

SharathData Engineer

Commented:
daforga -  What is your SQL Server version?

RCM01 -  your query will not skip the weekends.
Is it just weekend you want to skip, what about national/company holidays etc.?
Commented:
do you have to do this in a single TSQL statement or can you use a procedure? I think it's possible to do it in a single query but it would be really complicated, hard to follow and probably not very practical.

Author

Commented:
Thanks everyone

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial