case statement flips result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case statement flips result

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-10 : 09:27:39

Build test table:
CREATE TABLE [dbo].[a](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RoomsSurveyed] [int] NULL,
[day] [varchar](50) NULL,
[Year] [varchar](50) NULL,
[type] [varchar](50) NULL,
[Total] [int] NULL,
[SurveyTime] [varchar](50) NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Fill with test data:
INSERT INTO a
(RoomsSurveyed,[day],[year],[type],total,SurveyTime)

Select 22,'Thursday','0708','B',23,'12:00'
UNION ALL
Select 10,'Thursday','0708','B',23,'9:00'
UNION ALL
Select 17,'Thursday','0708','B',23,'11:00'
UNION ALL
Select 18,'Thursday','0708','B',23,'10:00'
UNION ALL
Select 8,'Thursday','0708','B',23,'16:00'
UNION ALL
Select 19,'Thursday','0708','B',23,'14:00'
UNION ALL
Select 13,'Thursday','0708','B',23,'13:00'
UNION ALL
Select 14,'Thursday','0708','B',23,'15:00'
UNION ALL
Select NULL,'Thursday','0708','B',23,'Daily'
UNION ALL
Select NULL,'Thursday','0708','B',23,'AM'
UNION ALL
Select NULL,'Thursday','0708','B',23,'PM'

I have a CASE calculated result which seems to be working in reverse. Can anyone see my error? The query below calculates a Daily, an AM and a PM value. But the AM and PM results are in wrong place, i.e. AM Freq gives correct result for PM Freq, and vice versa.

select *,CASE
when SurveyTime='Daily' then avg((((RoomsSurveyed*1.0)/Total)*100.0)) over (partition by [Year],[day],[type])
when SurveyTime='AM' then avg((((RoomsSurveyed*1.0)/Total)*100.0)) over (partition by [Year],[day],[type],
case
when [SurveyTime] in ('9:00','10:00','11:00','12:00') then 'AM'
else null
end )
when SurveyTime='PM' then avg((((RoomsSurveyed*1.0)/Total)*100.0)) over (partition by [Year],[day],[type],
case
when [SurveyTime] in ('13:00','14:00','15:00','16:00') then 'PM'
else null
end )
else
(RoomsSurveyed*1.0/Total)*100.0 end
as Freq
from

dbo.a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 09:29:56

it would be better if you could explain the requirement with some data and give the expected output rather than a query.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-10 : 09:53:33

My requirement is to use a CASE statement inside the PARTITION statement so that the calculation is applied to only rows which match the value in the CASE statement, i.e. particular Survey-times

Here is a worked example using the test data I supplied:

the AM RoomSurvey figures:
09:00 - 10
10:00 - 18
11:00 - 17
12:00 - 22

The avg of these is 16.75. Divided by the constant [total] figure of 23 gives an AM Freq of 72.8

But if you run the query supplied - this is the result in PM row.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-10 : 09:54:45

Maybe
select *,CASE
when SurveyTime='Daily' then avg((((RoomsSurveyed*1.0)/Total)*100.0)) over (partition by [Year],[day],[type])
when SurveyTime='AM' then avg((((case when [SurveyTime] in ('9:00','10:00','11:00','12:00') then RoomsSurveyed else null end *1.0)/Total)*100.0)) over (partition by [Year],[day],[type])
when SurveyTime='PM' then avg((((case when [SurveyTime] in ('13:00','14:00','15:00','16:00') then RoomsSurveyed else null end *1.0)/Total)*100.0)) over (partition by [Year],[day],[type])
else
(RoomsSurveyed*1.0/Total)*100.0 end
as Freq
from
dbo.a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 09:58:11

quote:
Originally posted by darkdusky

My requirement is to use a CASE statement inside the PARTITION statement so that the calculation is applied to only rows which match the value in the CASE statement, i.e. particular Survey-times

Here is a worked example using the test data I supplied:

the AM RoomSurvey figures:
09:00 - 10
10:00 - 18
11:00 - 17
12:00 - 22

The avg of these is 16.75. Divided by the constant [total] figure of 23 gives an AM Freq of 72.8

But if you run the query supplied - this is the result in PM row.

why are you taking avg((((RoomsSurveyed*1.0)/Total)*100.0)) when RoomsSurveyed*1.0)/Total)*100.0 itself gives you average? its like taking avg(avg())

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-10 : 10:03:26

nr - cheers that is exactly what I needed - thanks

Visakh - the 'Total' refers to a TotalAvailable rather than a simple SUM() so it doesn't actually give an average but a Frequency - it is this which needs averaged.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:06:15

ok...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources