0 Replies Latest reply on Nov 1, 2015 7:20 AM by Vijay Raje

    First day & Last Day Utilization

    Vijay Raje

      I want to create report which shows me only First day & Last Day Utilization for Last month. below is the query, its not giving any error nor output.

      can someone help me

       

      DECLARE @StartDate DATETIME, @EndDate DATETIME

      SET @StartDate = dateadd(mm, -1, getdate())

      SET @StartDate =  dateadd(mm, DATEDIFF(mm,0,GETDATE()) - 1, 0)

      SET @EndDate = dateadd(DAY, -(DAY(GETDATE())), GETDATE())

       

      SELECT  TOP 10000000 VolumeUsage.DateTime AS DateTime,

      Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Volumes.Caption AS Caption, Volumes.VolumeSize AS Volume_Size, AVG(VolumeUsage.AvgDiskUsed) AS AVERAGE_of_AvgDiskUsed,

      AVG((NullIf(VolumeSize,-2)-NullIf(VolumeSpaceUsed,-2))) AS AVERAGE_of_VolumeSpaceAvailable, Volumes.VolumePercentUsed AS Volume_Percent_Used, Nodes.NodeID AS NodeID

       

      FROM Nodes

      INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)

      INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)

       

      WHERE

      VolumeUsage.DateTime = @StartDate AND VolumeUsage.DateTime = @EndDate

      AND 

      (

        (Nodes.ASP30 = 1) AND

        (Nodes.Caption LIKE '%phy%')

      )

       

      GROUP BY VolumeUsage.DateTime, Nodes.Caption, Nodes.IP_Address, Volumes.Caption, Volumes.VolumeSize, Volumes.VolumePercentUsed, Nodes.NodeID

       

      ORDER BY 1 ASC