Dear Paul,
Sorry for the delay. I have been swampled today.
Here is a Stored Procedure that will generate dates for an entire year using derived tables. I found this procedure on SQLTeam and modified it for my needs, here is the original link:
author: Alexander Netrebchenko
link:
Fix the stored procedure to match your table and field names. Comment out the Grant Public after running the first time.
Go into Crystal and connect to the stored procedure, you will be prompted for a date, I always use the first day of the year, but whatever you use it will generate dates from there forward.
Now you can group your report by month and then day as the main report and link to your actual table via a subreport to show data.
/*Code Begins*/
if exists (select * from sysobjects where id = object_id('dbo.sp_DateReport_R') and sysstat & 0xf = 4)
drop procedure dbo.sp_DateReport_R
GO
CREATE PROCEDURE dbo.sp_DateReport_R
@startdate datetime
as
Begin
/*set @startdate = ('01/01/02')commented out for Crystal*/
declare @counter int
set @counter =1
declare @enddate datetime
set @enddate = DATEADD(YYYY, 1, @startdate)-1
declare @month int
set @month = datepart(M,@startdate)
declare @y int
set @y = datepart(Yy,@startdate)
declare @mydate datetime
Set @mydate = @startdate
declare
@start datetime,
@end datetime,
@counter1 int
set @start = @startdate
set @end = @enddate
set @counter1 = 1
select dates.date
from
(
-- This select produces a sequence of dates starting from @start
select DATEADD(dd, seq.id, @start) as date
from
(
-- This select produces a sequence from 0 to 399 using Cartesian product.
select (a0.id + a1.id + a2.id) as id
from
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select
60 union select 70 union select 80 union select 90) a1,
(select 0 id union select 100 union select 200 union select 300) a2
-- ...
) seq
) dates
left join _SMDBA_._TELMASTE_
on dates.date between [DATE OPEN] and coalesce([Closed On], [Closed On], '12/31/9999')
where
dates.date between @start and @end
group by
dates.date
order by 1
END
go
grant execute on MagicTSD.dbo.sp_DateReport_R to Public
go
/*Code Ends - Don't forget to comment out grant after first run*/
I hope this helps, if you need something else let me know.
I also have a stored procedure that I created that is used to populate a calendar style report (thanks to ChelseaTech Newsletter for the how to). The stored procedure gathers open and closed call counts for each "possible square" in a calendar.
Once again, sorry for the delay...
ro
Rosemary Lieberman
rosemary@microflo.com,
Microflo provides expert consulting on MagicTSD and Crystal Reports.
