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.
| Author |
Topic |
|
davenims
Starting Member
21 Posts |
 Posted - 2006-09-22 : 09:09:03
|
Greetings all. I have a bit of a problem that has got me stuck, I wonder if any of you can help.I want to rattle off a list of working days in the next 5 weeks, which is to be used for a calendar-type report. The trouble is that I can't base it on a table, because I want to print the date even if there is no data (i.e. events) associated with that date.I have managed to do this with a stored procedure, but this only outputs a text print - I need it to output the days as records (so that I can use it as the source for an Access report). I suspect the answer is maybe in using either a cursor or a temporary table, but I don't have much experience with either.Here is what I have done to display the dates in a stored procedure:quote:
CREATE PROCEDURE [dbo].[stProductionDays] ASDECLARE @BeginDate DATETIMEDECLARE @CurDate DATETIMESET @BeginDate = dbo.WeekCommencing(GETDATE()) -- Get date of this MondaySET @CurDate = @BeginDateWHILE @CurDate <= DATEADD(dd,-1,DATEADD(wk,5,@BeginDate)) -- Loop through next 5 working weeks from this MondayBEGINIF DATEPART(dw,@CurDate) <> 7 AND DATEPART(dw,@CurDate) <> 1 -- Don't print weekendsBEGINPRINT @CurDateENDSET @CurDate = DATEADD(dd,1,@CurDate)ENDGO
If anyone can point me in the right direction I'd be eternally grateful! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
davenims
Starting Member
21 Posts |
Posted - 2006-09-22 : 09:28:40
|
| Fantastic, thanks! |
 |
|
|
|
|
|
|
|
Displaying a list of dates in a date range