3 Replies Latest reply on Mar 17, 2016 1:49 PM by jamison.jennings

    Report Group

    taís

      Hello,

       

      Where do I change and how do I change the report below to the value of Group Status Info show in decimal? Example: Ex: 99.978%

       

      Report:

       

      DECLARE @BeginDate FLOAT, @EndDate FLOAT;

      SET @BeginDate = FLOOR(CAST(DATEADD(MONTH, -1, DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE())) AS FLOAT));

      SET @EndDate = CAST(DATEADD(MONTH, 1, @BeginDate) AS FLOAT);

       

       

      SELECT

        c.[ContainerID] AS [GroupID],

        c.[Name]  AS [GroupName],

        CAST(csc.[Date] AS DATETIME) AS [Date],

        si.[ShortDescription] +' (' + CAST(ROUND(csc.[Count] / csc.[Total], 2) * 100 AS VARCHAR(30)) + '%)' AS [GroupStatusInfo],

        si.[Ranking]

      FROM Containers AS c

      JOIN (

        SELECT

        hcs.[GroupID],

        hcs.GroupStatus,

        FLOOR(CAST(hcs.[DateTime] AS FLOAT)) AS [Date],

        CAST(COUNT(hcs.GroupStatus) AS FLOAT) AS [Count],

        cst.[Total]

        FROM Containers_HistoricalContainerStatus AS hcs

        JOIN (

        SELECT [GroupID], FLOOR(CAST([DateTime] AS FLOAT)) AS [Date], CAST(COUNT(GroupStatus) AS FLOAT) AS [Total]

        FROM Containers_HistoricalContainerStatus

        WHERE [DateTime] BETWEEN @BeginDate AND @EndDate

        GROUP BY [GroupID], FLOOR(CAST([DateTime] AS FLOAT))

        ) AS cst ON

        cst.[GroupID] = hcs.[GroupID] AND cst.[Date] = FLOOR(CAST(hcs.[DateTime] AS FLOAT))

        WHERE hcs.[DateTime] BETWEEN @BeginDate AND @EndDate

        GROUP BY

        hcs.[GroupID], FLOOR(CAST(hcs.[DateTime] AS FLOAT)), hcs.GroupStatus, cst.[Total]

      ) AS csc ON csc.[GroupID] = c.[ContainerID]

      JOIN [StatusInfo] AS si ON si.[StatusId] = csc.GroupStatus

      ORDER BY c.[ContainerID], csc.[Date], si.[Ranking]

       

      Thanks ^^