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 ^^
