I thought I would add a little something to this topic. I love the report, but I didn't want to use a table to store my dates because it uses space and some kind of generation mechanism is needed. The following SQL user function creates the table/dates on the fly. Once the function is created, use a stored procedure to call the function, you can then call the stored procedure from within your Crystal Report. With this, there is no need to maintain a spreadsheet or table for your dates.
Both the Funcation and Procedure are listed below
Brian
Chicago, IL
******* USER FUNCATION *******
Create function RPT_CALENDAR
(@p_year smallint)
returns @tbl table (cal_date smalldatetime)
as
begin
-- 2 --
declare @p_start_date smalldatetime
set @p_start_date = convert(smalldatetime, convert(varchar(4), @p_year) + '0101')
-- 3 --
insert into @tbl
select cal_date
from
(
select
@p_start_date +
n3.num * 100 +
n2.num * 10 +
n1.num as cal_date
from
(
select 0 as num union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
) n1,
(
select 0 as num union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
) n2,
(
select 0 as num union all
select 1 union all
select 2 union all
select 3
) n3
) gencalendar
order by 1
return
end
****** STORED PROCEDURE ******
/*
** This procedure returns dates for calendar reporting
*/
create procedure dbo.rpt_calendar_view
as
DECLARE @current_year INT
SET @current_year = DATEPART(year, GETDATE())
SELECT RPT_CALENDAR.*
FROM dbo.RPT_CALENDAR(@current_year) RPT_CALENDAR
GO
