Res.Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.GetDates.Install (Resource)

Element properties:

File Content: Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.GetDates.Install.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'TF' AND name = 'ComtradeMPXAXD_GetDates')

BEGIN

EXECUTE ('CREATE FUNCTION dbo.[ComtradeMPXAXD_GetDates]() RETURNS @Result  TABLE ("DateTime" datetime) AS BEGIN RETURN END')

END

GO





ALTER FUNCTION [dbo].[ComtradeMPXAXD_GetDates]

(

       @StartDate datetime, 

       @EndDate datetime,

       @AggrationType int -- 0: 5minute, 1: hourly, 2: daily

)

RETURNS @Result TABLE ("DateTime" datetime)

AS

BEGIN

  DECLARE @IncDate datetime

  --round times CAST(CAST(@StartDate AS Date) As datetime)

  IF @AggrationType = 0 BEGIN SET @StartDate = dateadd(minute, datediff(minute,0,@StartDate) / 5 * 5, 0) END       --5 minute aggregation

  ELSE IF @AggrationType = 1 BEGIN SET @StartDate = dateadd(hour, datediff(hour, 0, @StartDate), 0) END            --1 hour aggregation

  ELSE BEGIN SET @StartDate = dateadd(day, datediff(day, 0, @StartDate), 0) END                                    --1 day aggregation

  

  INSERT INTO @Result ([DateTime]) VALUES (@StartDate)



  --set incdate

  IF @AggrationType = 0 BEGIN SET @IncDate = DATEADD(minute,+5,(@StartDate)) END       --5 minute aggregation

  ELSE IF @AggrationType = 1 BEGIN SET @IncDate = DATEADD(hour,+1,(@StartDate)) END    --1 hour aggregation

  ELSE BEGIN SET @IncDate = DATEADD(day,+1,(@StartDate)) END                           --1 day aggregation

  





  WHILE (@IncDate < @EndDate)

       BEGIN

              INSERT INTO @Result ([DateTime]) VALUES (@IncDate)



              IF @AggrationType = 0 BEGIN SET @IncDate = DATEADD(minute,+5,(@IncDate)) END       --5 minute aggregation

              ELSE IF @AggrationType = 1 BEGIN  SET @IncDate = DATEADD(hour,+1,(@IncDate)) END   --1 hour aggregation

              ELSE BEGIN SET @IncDate = DATEADD(day,+1,(@IncDate)) END                           --1 day aggregation

              

       END

  RETURN

END



GO



/*     =============================================================== 

       PERMISSIONS 

       ===============================================================  */

       

GRANT SELECT ON dbo.[ComtradeMPXAXD_GetDates] TO OpsMgrReader 



GO