CODEWITHSUNDEEP
t-sqlreporting-services
Phi Bert
Phi Bert

Reputation: 13

Percentages in SRSS

I´ve got the following problem with the T-SQL and SRSS.

Situation: E-Mail Ticket System. Each incoming Mail gets an ID. also, each E-Mail gets an "VIP"-Flag, if the user is a VIP in our database. What I´m trying to do now is to get the following 4 Values.

So far I´ve got the following Statement:

WITH T1 AS (
SELECT 
    increcsystem.registrationtime,
    count(*) AS [Total],
    increcsystem.viplevel,
FROM
    increcsystem 
WHERE
GROUP BY increcsystem.registrationtime, increcsystem.viplevel
)
Select 
    (Select count(*) From T1) AS Total,
    (Select count(*) From T1 WHERE viplevel = '100629') AS VIP_Tickets,
    registrationtime AS Date
FROM T1

But I can´t get the percentages correct :(
Can somebody see the error?

best Regards, PhiBu

Upvotes: 0

Views: 51

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

Here's a simple query that should work:

select registrationtime
  , TotalMails = count(1)
  , VIPMails = sum(case when viplevel = '100629' then 1 else 0 end)
  , VIPPercent = sum(case when viplevel = '100629' then 1.0 else 0.0 end) / count(1)
from increcsystem
group by registrationtime
order by registrationtime

Here is a SQL Fiddle demo showing the working query.

Upvotes: 1

Related Questions