CODEWITHSUNDEEP
sqlsql-server
Ruan
Ruan

Reputation: 127

SQL: Working out percentage

I have a proc that returns 

|On time: 1 of 45  |1 |
|Over due: 44 of 45|44|

My Procedure:

CREATE PROCEDURE [dbo].[p_TargetStatus]
AS
BEGIN
       --Try
    Declare @OnTime float
    Declare @Overdue float
    Declare @AllON float
    Declare @AllOV float
    Declare @AnswON float
    Declare @AnswOV float

    SET NOCOUNT ON;
    IF OBJECT_ID('#tmp1') IS NOT NULL
    DROP TABLE #tmp1

        SET NOCOUNT ON

    CREATE TABLE #tmp1 
    (
        AUD_ID BIGINT,
        RowCounter BIGINT,
        DistinctCounter BIGINT,
        NACounter BIGINT,
        Total BIGINT,
        [Status] VARCHAR(MAX)
    )

    INSERT INTO #tmp1 EXEC [p_GetCompleteIncompleteNaOverviewSCORE] 
    --TRY 2 
    Set @OnTime = (Select Count([Status])
    From #tmp1
    Where [Status] = 'OPEN')

    Set @AllON = (SELECT COUNT([Status])
    From #tmp1)

    SET @AnswON  = @OnTime/@AnswON

    Set @Overdue = (Select Count([Status])
    From #tmp1
    Where [Status] = 'CLOSED')

    Set @AllOV = (SELECT COUNT([Status])
    From #tmp1)

    SET @AnswOV  = @Overdue/@AllOV
       --TRY 2

    DECLARE @Total AS INT

    SELECT @Total = COUNT(*)
    FROM (
    SELECT CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,t2.AUD_TargetDate), 101)) < CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,GETDATE()), 101))
            THEN 'Over Due'
            ELSE 'On Time' END AS [Target Status]
            FROM #tmp1 t1 INNER JOIN dbo.Audit t2
            ON t1.AUD_ID = t2.AUD_ID
            WHERE t1.[Status] in ('Closed','Open')) DER 

    DECLARE @TotalTEST Float
    DECLARE @OPEN Float
    DECLARE @CLOSED FLOAT
    DECLARE @PERC FLOAT 

    SELECT ([Target Status] + '' + CAST(COUNT(*) AS NVARCHAR(255))  + ' of ' + CAST(@Total AS NVARCHAR(255))) AS TargetStatus, COUNT(*)/@Total AS [Count]
    FROM (
    SELECT CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,t2.AUD_TargetDate), 101)) < CONVERT(DATETIME,CONVERT(CHAR(10),DATEADD(DAY,0,GETDATE()), 101))
            THEN 'Over Due: '
            ELSE 'On Time: ' END AS [Target Status]
            FROM #tmp1 t1 INNER JOIN dbo.Audit t2
            ON t1.AUD_ID = t2.AUD_ID
            WHERE t1.[Status] in ('Closed','Open')) DER
    GROUP BY [Target Status]

END 
GO

I've tried stuff like:

DECLARE @TotalTEST Float
DECLARE @OPEN Float
DECLARE @CLOSED FLOAT
DECLARE @PERC FLOAT 

Set @TotalTEST = (Select Count(*)from #tmp1)    
   PRint @TotalTEST
   SET @OPEN = (SELECT COUNT(*)from #tmp1 Where [Status] = 'OPEN')
   PRINT @OPEN
   SET @CLOSED = (SELECT COUNT(*)from #tmp1 Where [Status] = 'Closed' AND )
   PRINT @CLOSED

The percentage should(i think) be in this line AS TargetStatus, *COUNT(*)/@Total* AS [Count] The outcome should be something like;

|On time:  1 of 45 |0.02| (Roughly)
|Over due: 44 of 45|0.98|

The code is messy but I've tried just divide with total, I've tried that try2 part to declare and then do something but fail.

Upvotes: 0

Views: 161

Answers (2)

shadowjfaith
shadowjfaith

Reputation: 942

Try CAST((CAST(COUNT(*) AS DECIMAL(5,2)) / CAST(@Total AS DECIMAL(5,2)) AS DECIMAL(5,2)) AS [Count] in your COUNT(*)/@Total* AS [Count]

Upvotes: 1

Sparky
Sparky

Reputation: 15085

If you divide two integers, you'll always get 0.

Try this approach:

declare @x int  
declare @y int
set @x=1
set @y=45
select round(cast(@x as Float)/(CAST(@y as float)),2)

Basically, you need to work with floats or decimals

AS TargetStatus, cast( COUNT(*) as float)/cast(@Total as float) AS totPct

Upvotes: 1

Related Questions