CREATE TABLE [dbo].[calls](
	[cl_customer] [int] NOT NULL,
	[cl_datetime] [datetime] NOT NULL,
	[cl_type] [nvarchar](1) NOT NULL,
	[cl_length] [int] NOT NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX [CX_DATETIME] ON [dbo].[calls] 
(
	[cl_datetime] ASC
)

CREATE TABLE [dbo].[report](
	[rp_in] [int] NOT NULL CONSTRAINT [DF_report_rp_in]  DEFAULT ((0)),
	[rp_out] [int] NOT NULL CONSTRAINT [DF_report_rp_out]  DEFAULT ((0)),
	[rp_total] [int] NOT NULL CONSTRAINT [DF_report_rp_total]  DEFAULT ((0)),
	[rp_customer] [int] NOT NULL CONSTRAINT [DF_report_rp_customer]  DEFAULT ((0)),
	[rp_period] [datetime] NOT NULL
) ON [PRIMARY]

declare @i as int
declare @currenttime as datetime
declare @secs as int

select	@i = 0,
		@currenttime = '2009-06-11 07:00:00'

while @i < 43200
begin
	if round((rand() * 20) + 1, 0) = 20
	begin
		insert into calls values (1, @currenttime, 'o', round((rand() * 1800) + 1, 0))
	end	

	set @currenttime = dateadd(s, 1, @currenttime)
	set @i = @i + 1
end

declare @startdate as datetime, @enddate as datetime
select	@startdate = '2009-06-11 00:00:00',
	@enddate   = '2009-06-11 23:59:59.997'

declare @bits as int
select  @bits = datediff(s, @startdate, @enddate) / 1

insert into report
select 
	isnull(sum([in]),0)    as [in],
	isnull(sum(out),0)     as [out],
	isnull(sum(total),0)   as total, 
	isnull(cl_customer, 0) as [customer],
	number
from 
(
	select
		number,
		[in],
		[out],
		([in] + [out]) as total, 
		cl_customer
	from 
	(
		select dateadd(s, number, @startdate) as number from dbo.F_TABLE_NUMBER_RANGE(1,@bits)
	) as tints
	left join 
	(
		select
			case when cl_type = 'i' then 1 else 0 end as [in],
			case when cl_type = 'o' then 1 else 0 end as [out],
			cl_datetime as start,
			cl_dateend  as finish,
			cl_customer
		from
		(
			select 
				cl_customer,
				cl_datetime, 
				dateadd(s, cl_length, cl_datetime) as cl_dateend, 
				cl_type
			from calls
			where
				cl_datetime between dateadd(d, -1, @startdate) and @enddate
		) as calls
		where
			cl_datetime between @startdate and @enddate or 
			cl_dateend  between @startdate and @enddate
	) tt on (tints.number between start and finish) 
) ttt
group by 
	number,
	cl_customer
order by 
	number

select dateadd(s, number, @startdate) as number from number where number between 1 and @bits

CREATE TABLE	TallyNumbers
		(
			Number INT PRIMARY KEY CLUSTERED
		)

INSERT		TallyNumbers
		(
			Number
		)
SELECT		Number
FROM		dbo.F_TABLE_NUMBER_RANGE(0, 86399)
ORDER BY	Number

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '20090611',
	@EndDate = '20090612'

SELECT		DATEADD(SECOND, s.Number, @StartDate) AS theMinute,
		w.cl_Customer,
		SUM(CASE WHEN w.cl_Type = 'i' THEN 1 ELSE 0 END) AS [in],
		SUM(CASE WHEN w.cl_Type = 'o' THEN 1 ELSE 0 END) AS [out],
		COUNT(w.theSecond) AS Total
FROM		TallyNumbers AS s
LEFT JOIN	(
			SELECT	cl_Customer,
				cl_Type,
				DATEDIFF(SECOND, DATEDIFF(DAY, 0, cl_DateTime), cl_DateTime) AS theSecond,
				cl_Length
			FROM	Calls
			WHERE	cl_DateTime < @EndDate
				AND DATEADD(SECOND, cl_Length - 1, cl_DateTime) >= @StartDate
		) AS w ON w.theSecond <= s.Number
			AND w.theSecond + w.cl_Length > s.Number
GROUP BY	DATEADD(SECOND, s.Number, @StartDate),
		w.cl_Customer