/* SqlServer 2005 Standard */
CREATE FUNCTION [dbo].[generate_series] (
@BeginData datetime,
@EndDate datetime,
@DatePart varchar(32) = '1 day'
)
RETURNS @Serie TABLE ( item datetime ) AS BEGIN
declare @arg varchar(32);
declare @len integer;
declare @pos integer;
declare @Data datetime;
declare @DatePartType varchar(11);
declare @DatePartIncrement integer;
SET @arg = lTRIM(rTRIM(@DatePart));
SET @pos = CHARINDEX(' ', @arg );
SET @len = LEN(@arg);
SET @DatePartIncrement = CAST(LEFT(@arg, @pos) as integer);
SET @DatePartType = lower(RIGHT(@arg, @len - @pos));
SET @Data = @BeginData;
WHILE (@Data <= @EndDate)
BEGIN
INSERT INTO @Serie(item) VALUES( @Data );
SET @Data = CASE @DatePartType
WHEN 'year' THEN dateadd(year, @DatePartIncrement, @Data )
WHEN 'quarter' THEN dateadd(quarter, @DatePartIncrement, @Data )
WHEN 'month' THEN dateadd(month, @DatePartIncrement, @Data )
WHEN 'day' THEN dateadd(day, @DatePartIncrement, @Data )
WHEN 'week' THEN dateadd(week, @DatePartIncrement, @Data )
WHEN 'weekday' THEN dateadd(weekday, @DatePartIncrement, @Data )
WHEN 'hour' THEN dateadd(hour, @DatePartIncrement, @Data )
WHEN 'minute' THEN dateadd(minute, @DatePartIncrement, @Data )
WHEN 'second' THEN dateadd(second, @DatePartIncrement, @Data )
ELSE dateadd( day, @DatePartIncrement, @Data ) END
END;
RETURN
-- SELECT * FROM vw_generate_series_TEST
END
/* View de Tests */
ALTER VIEW vw_generate_series_TEST AS
SELECT
1 as Test,
'2001-01-01 00:00:00.000' as BeginDateArg,
'2010-12-31 00:00:00.000' as EndDateArg,
'1 year ' as DatePartArg,
10 OK,
Count(*) QT,
CASE WHEN (10 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2001-01-01 00:00:00.000', '2010-12-31 00:00:00.000', '1 year ')
union
SELECT
2 as Test,
'2010-01-01 00:00:00.000' as BeginDateArg,
'2010-12-31 00:00:00.000' as EndDateArg,
'1 quarter ' as DatePartArg,
4 OK,
Count(*) QT,
CASE WHEN ( 4 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 00:00:00.000', '2010-12-31 00:00:00.000', '1 quarter ')
union
SELECT
3 as Test,
'2010-01-01 00:00:00.000' as BeginDateArg,
'2010-12-31 00:00:00.000' as EndDateArg,
'1 month ' as DatePartArg,
12 OK,
Count(*) QT,
CASE WHEN (12 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 00:00:00.000', '2010-12-31 00:00:00.000', '1 month ')
union
SELECT
4 as Test,
'2010-01-01 00:00:00.000' as BeginDateArg,
'2010-01-31 00:00:00.000' as EndDateArg,
'1 day ' as DatePartArg,
31 OK,
Count(*) QT,
CASE WHEN (31 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 00:00:00.000', '2010-01-31 00:00:00.000', '1 day ')
union
SELECT
5 as Test,
'2010-01-01 00:00:00.000' as BeginDateArg,
'2010-01-31 00:00:00.000' as EndDateArg,
'1 week ' as DatePartArg,
5 OK,
Count(*) QT,
CASE WHEN (5 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 00:00:00.000', '2010-01-31 00:00:00.000', '1 week ')
union
SELECT
6 as Test,
'2010-01-01 00:00:00.000' as BeginDateArg,
'2010-01-01 23:59:59.000' as EndDateArg,
'1 hour ' as DatePartArg,
24 OK,
Count(*) QT,
CASE WHEN (24 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 00:00:00.000', '2010-01-01 23:59:59.000', '1 hour ')
union
SELECT
7 as Test,
'2010-01-01 23:00:00.000' as BeginDateArg,
'2010-01-01 23:59:59.000' as EndDateArg,
'1 minute ' as DatePartArg,
60 OK,
Count(*) QT,
CASE WHEN (60 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 23:00:00.000', '2010-01-01 23:59:59.000', '1 minute ')
union
SELECT
8 as Test,
'2010-01-01 23:00:01.000' as BeginDateArg,
'2010-01-01 23:01:00.000' as EndDateArg,
'1 second ' as DatePartArg,
60 OK,
Count(*) QT,
CASE WHEN (60 =Count(*)) THEN ' OK ' ELSE 'FALHOU' END Status
FROM lib.dbo.generate_series('2010-01-01 23:00:01.000', '2010-01-01 23:01:00.000', '1 second ')