How to represent 0.5 or 1/2 in a AVG() Function SQL Server

Question

I have 2 columns which have an occupancy column and contains values of 0 and 1.

Here is an example:

After group by based on other columns, these columns will be combined and the average will be 0.5 since 0+1/2 = 0.5 or 1/2.

So how do I do this: case when avg(occupancy) = 1/2 or 0.5 then '1' else '0'? I know something about SQL round down 1/2 to 0 or something along that lines

What I have tried:

case when
Avg(occupancy) = 1.0/2.0

Here is my full view code.


CREATE VIEW [iot].[v_test]
AS
SELECT
    ro.[RoomCode],
    ro.[RoomName],
    ro.[DeviceID],
    ro.[TpID],
    CAST(avg(ro.[Temperature]) AS decimal(10, 1)) AS [Temperature],
    CASE
        WHEN avg(cast(occupancy as decimal)) between 0.01 and 1.0 
            THEN '1'
        WHEN avg(cast(occupancy as decimal)) = 1/2.0
            THEN '1'
        ELSE
            '0'
    END AS Occupancy,
    (DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME)))) AS [Time],
    ctt.[DAY],
    cs.ClassroomStatus
FROM
    [iot].[v_RoomOccupancy] ro
    LEFT OUTER JOIN
        [iot].[ClassTimeTable]  ctt
            ON ro.RoomCode = ctt.ROOMID
                AND (ro.LocalTime
                BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE
                    )
    CROSS APPLY
    (
        SELECT
            CASE
                WHEN (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
                        AND (cast(occupancy as decimal) between 0.1 and 1.0 )
                    THEN 'Booked And Occupied'
                WHEN ((ro.LocalTime NOT BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE) OR ( ctt.CLASSSTARTDATE IS NULL AND ctt.CLASSENDDATE IS NULL))
                        AND (cast(occupancy as decimal) between 0.1 and 1.0 )
                    THEN 'Not Booked but Occupied'
                WHEN (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
                        AND cast(occupancy as decimal) <= 0.0
                    THEN 'Booked but Not Occupied'
                WHEN ((ro.LocalTime NOT BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE) OR (ctt.CLASSSTARTDATE IS NULL AND ctt.CLASSENDDATE IS NULL))
                        AND cast(occupancy as decimal) <= 0.0 
                    THEN 'Not Booked and Not Occupied'
                ELSE
                    'Null'
            END AS ClassroomStatus
    )AS cs
GROUP BY
    ro.[RoomCode],
    ro.[RoomName],
    ro.[DeviceID],
    ro.[TpID],

    (DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME)))),
    ctt.[DAY],
    cs.ClassroomStatus;

What? You ONLY want the value to be 1 when the average occupancy is *exactly* 0.5? For all other average occupancies you want the value to be 0? — avery_larry, Nov 20 '19 at 03:31
Do you perhaps want every value always to round up, since you cant occupy part of a room? If so use Ceiling(). — TomC, Nov 20 '19 at 03:42
there are other conditions also, such as if avg(occupancy) >0 or =1 then '1' else '0' @avery_larry — James Franco Hadi, Nov 20 '19 at 03:42
@JamesFrancoHadi, you can use `IN` keyword -> `avg(cast(occupancy as decimal)) in (.5, 1, 0)` — Ed Bangga, Nov 20 '19 at 03:45
how do i implement that with the answer u posted @ϻᴇᴛᴀʟ possible to update ur answer? thanks — James Franco Hadi, Nov 20 '19 at 03:46
Ultimately I think your issue is that the average of INT values returns an INT. Thus you likely could do the rest yourself once you `cast(occupancy as decimal)`. — avery_larry, Nov 20 '19 at 04:01

Ed Bangga · Accepted Answer · 2019-11-20T04:00:41.953

2

try this.

select case when avg(cast(occupancy as decimal)) in (.5, 1.0, 0) then 1 else 0 end

or

select case when avg(cast(occupancy as decimal)) between 0.0 and 1.0 then '1' else 'Error' end

edited Nov 20 '19 at 04:00

answered Nov 20 '19 at 03:29

Ed Bangga

12,879
4
16
30

Thanks for the quick reply but what does the -.5 =0.00 do ? – James Franco Hadi Nov 20 '19 at 03:31
it means that it satisfies your condition – Ed Bangga Nov 20 '19 at 03:33
I too am confused why you would subtract .5 instead of comparing directly to .5. Though clearly the comparison returns the same result either way. – avery_larry Nov 20 '19 at 03:35
@avery_larry, that's the way I'm building my expression – Ed Bangga Nov 20 '19 at 03:36
I was just wondering if you had a reason to do it that way over the more simple `select case when avg(cast(occupancy as decimal)) = 0.5 then 1 else 0 end ` – avery_larry Nov 20 '19 at 03:38
so what does in do? i have not used it before. what i want to achieve is CASE WHEN avg(occupancy) > 0 or avg(occupancy)<=1 or avg(occupancy) =0.5 then '1' CASE WHEN avg(occupancy)<=0 then '1' else 'error' – James Franco Hadi Nov 20 '19 at 03:55
you can use `between` 0 and 1 – Ed Bangga Nov 20 '19 at 04:00
everything u sent me all works,just that when do the cast(avg(occupancy as decimal)) for 2 rows with the value of 1 and 0. the answer will be 1/2 in which sql will treat it as 0. Here is what i am referring to : https://stackoverflow.com/questions/33517276/why-does-sql-server-return-0-for-1-2 . – James Franco Hadi Nov 21 '19 at 01:26
@JamesFrancoHadi, i tried its giving .5 http://sqlfiddle.com/#!18/686ec/1 – Ed Bangga Nov 21 '19 at 01:32
ok i saw i thanks. anyways i think the avg is not the problem anymore. now i am doing a case statement. Case when cast(occupancy as decimal) between 0.01 and 1 THEN 'room occupied' when cast(occupancy as decimal) <= 0.0 then 'room unoccupied'. i did something like this however it does not register the avg 1/2 as 0.5. i think – James Franco Hadi Nov 21 '19 at 01:51
you still need to add avg to get .5 `case when avg(cast(cast(occupancy as int) as decimal)) between 0.01 and 1 then 'room occupied' when avg(cast(cast(occupancy as int) as decimal)) <= 0.0 then 'room unoccupied' else '' end ` – Ed Bangga Nov 21 '19 at 02:55
ive updated my question with the full view code. cant have an aggregate in on clause. Do take a look – James Franco Hadi Nov 21 '19 at 05:39

How to represent 0.5 or 1/2 in a AVG() Function SQL Server

1 Answers1