Create a view that calculates member's attendance status

select username,
case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 'Platinum'
    when professional >= 4 and  Community >= 2 and Social >= 2 then 'Gold'
    when professional >= 3 and  Community >= 1 and Social >= 1 then 'Silver'
    else ''
from (
select
  username
  ,sum ( case when event_category = 'Professional' then 1 else 0 end) as professional
  ,sum ( case when event_category = 'Social' then 1 else 0 end) as social
  ,sum ( case when event_category = 'Community' then 1 else 0 end) as community
from yourtable
group by username
) as l

angelIII your query is missing End from outercase statement

jeaton32, never be overhwhelmed by SQL , we have a gross saying in US, there are so many ways you can skin a cat... yechh!!!!, personally I think cat will taste better if BBQed with skin.....heh heh.. just kdding

you can either write your rules in another table (or maybe they are already part of table or view) and then you can write your query several ways. anothe of the ways is to write sub-queries for a column...

for example
select username,
Case When
      (      Select       Sum(Num_Events) Num_Events
            From       YourTable B
            Where       A.UserName=B.USerName and
                  B.event_category='Professional')=5
then .... and so on

at this point I am not going to confuse with some extra queries and certainely dont mean to overstep angelIII so have fun.

rw3admin

rw3admin: thanks for the error tip:

select username,
case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 'Platinum'
    when professional >= 4 and  Community >= 2 and Social >= 2 then 'Gold'
    when professional >= 3 and  Community >= 1 and Social >= 1 then 'Silver'
    else '' end as status
from (
select
  username
  ,sum ( case when event_category = 'Professional' then 1 else 0 end) as professional
  ,sum ( case when event_category = 'Social' then 1 else 0 end) as social
  ,sum ( case when event_category = 'Community' then 1 else 0 end) as community
from yourtable
group by username
) as l

jeaton32 I dont want to confuse you but I couldnt resist here is my take on this query
** angelIII please dont hate me I am not looking for points or anything I would be happy if I can help

Select UserName,
Case Professional
      when 5
      Then
            Case
            When (Social>=2 and Community>=1) or (Social>=1 and Community>=2)
            Then 'Platinum'
            When (Social=2 and Community=2)
            Then 'Gold'
            When (Social=1 and Community=1)
            Then 'Silver'
            Else ''
            End
      When 4
      Then
            Case
            When (Social=2 and Community=2)
            Then 'Gold'
            When (Social=1 and Community=1)
            Then 'Silver'
            Else ''
            End
      When 3
      Then
            Case
            When (Social=1 and Community=1)
            Then 'Silver'
            Else ''
            End
      Else
      ''
End            
(      Select      A.UserName,
            Professional=
            (      Select       Sum(Num_Events) Num_Events
                  From       YourTable B
                  Where       A.UserName=B.USerName and
                        B.event_category='Professional'),
            'Social'=
            (      Select       Sum(Num_Events) Num_Events
                  From       YourTable B
                  Where       A.UserName=B.USerName and
                        B.event_category ='Social),
            'Community'=
            (      Select       Sum(Num_Events) Num_Events
                  From       YourTable B
                  Where       A.UserName=B.USerName and
                        B.event_category ='Community')
      From YourTable A) as Data

AngelIII,

I changed your query slightly, and now it works:

select username,
case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 'Platinum'
    when professional >= 4 and  Community >= 2 and Social >= 2 then 'Gold'
    when professional >= 3 and  Community >= 1 and Social >= 1 then 'Silver'
    else '' end as status
from (
select
  username
  ,sum ( case when event_category = 'Professional' then num_events else 0 end) as professional
  ,sum ( case when event_category = 'Social' then num_events else 0 end) as social
  ,sum ( case when event_category = 'Community' then num_events else 0 end) as community
from dbo.total_events_by_category
group by username
) as l

I have a few questions:

1. If all I'm doing is grabbing the value of the num_events column for each username and category, are all the sum functions and case/when/else statements necessary? I think I need the sum in there just to group it by username, even though it's not summing different rows.

2. At the end of the query, what does the "as l" do? I took it out and it gave me a syntax error, what is it there for?

3. If I wanted a field in my member table called 'status' and I wanted it to automatically display the members status based on the results of this query, possibly whenever the attendance is updated it would check to see if the status would change, how would I do this?

Thanks for all of your help.

1. yes, because the values are in different rows
2. the " as l " is an alias to the subquery
3. you would create a trigger on the table you query from

create trigger trg_update_status
on yourtable
for insert, update, delete
as
  update usertable
   set status = ( select case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 'Platinum'
    when professional >= 4 and  Community >= 2 and Social >= 2 then 'Gold'
    when professional >= 3 and  Community >= 1 and Social >= 1 then 'Silver'
    else '' end as status
from (
select
  ,sum ( case when event_category = 'Professional' then num_events else 0 end) as professional
  ,sum ( case when event_category = 'Social' then num_events else 0 end) as social
  ,sum ( case when event_category = 'Community' then num_events else 0 end) as community
from dbo.total_events_by_category
where username  = i.username
) as l
from yourtable t
join inserted i
  on i.username = t.username

create trigger trg_update_status
on dbo.attendance
for insert, update, delete
as
  update dbo.member
   set member_level = ( select case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 'Platinum'
    when professional >= 4 and  Community >= 2 and Social >= 2 then 'Gold'
    when professional >= 3 and  Community >= 1 and Social >= 1 then 'Silver'
    else '' end as status
from (
select
   sum ( case when event_category = 'Professional' then num_events else 0 end) as professional
  ,sum ( case when event_category = 'Social' then num_events else 0 end) as social
  ,sum ( case when event_category = 'Community' then num_events else 0 end) as community
from dbo.total_events_by_category

**I tried to put in my own table names, but I don't understand what to do after this point, what is 'i' referring to, and inserted? **

where username  = i.username
) as l)
from attendance
join inserted i
  on i.username = t.username

'i' is referring to the 'inserted' table. This table is created and managed by SQL automatically.

More info form BOL:

Using the inserted and deleted Tables
Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
The inserted and deleted tables are used primarily in triggers to:
Extend referential integrity between tables.
Insert or update data in base tables underlying a view.
Check for errors and take action based on the error.

Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

Note  If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER.

I used the code below to create the trigger. However, when I update the attendance table, it changes the status for each member in the member table to match the new status calculated from the new row inserted into the attendance table. So each member's status is identical.

create trigger trg_update_member_level
on dbo.attendance
for insert, update, delete
as
  update dbo.member
   set member_level = ( select case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 3
    when professional >= 4 and  Community >= 2 and Social >= 2 then 2
    when professional >= 3 and  Community >= 1 and Social >= 1 then 1
    else 0 end as status
from (
select
   sum ( case when event_category = 'Professional' then num_events else 0 end) as professional
  ,sum ( case when event_category = 'Social' then num_events else 0 end) as social
  ,sum ( case when event_category = 'Community' then num_events else 0 end) as community
from dbo.total_events_by_category
where username  = i.username
) as l)
from attendance
join inserted as i
  on i.username = attendance.username

angelIII,

Thanks for your help. The trigger works, on update and insert, but whenever I delete a row from the attendance table, the member_level field does not update. Even if I delete every row in the attendance table, the member_level's remain until another row for that username is inserted. Do you know why this is happening?

I see, that is because the join only is done using the inserted table.


create trigger trg_update_member_level
on dbo.attendance
for insert, update, delete
as
  update dbo.member
   set member_level = ( select case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 3
    when professional >= 4 and  Community >= 2 and Social >= 2 then 2
    when professional >= 3 and  Community >= 1 and Social >= 1 then 1
    else 0 end as status
from (
select
   sum ( case when event_category = 'Professional' then num_events else 0 end) as professional
  ,sum ( case when event_category = 'Social' then num_events else 0 end) as social
  ,sum ( case when event_category = 'Community' then num_events else 0 end) as community
from dbo.total_events_by_category
where username  = i.username
) as l)
from member
join inserted as i
  on i.username = member.username
join attendance
  on i.username = attendance.username


update dbo.member
   set member_level = ( select case when professional >= 5 and  Community + Social >= 5 and Community >= 2 and Social >= 2 then 3
    when professional >= 4 and  Community >= 2 and Social >= 2 then 2
    when professional >= 3 and  Community >= 1 and Social >= 1 then 1
    else 0 end as status
from (
select
   sum ( case when event_category = 'Professional' then num_events else 0 end) as professional
  ,sum ( case when event_category = 'Social' then num_events else 0 end) as social
  ,sum ( case when event_category = 'Community' then num_events else 0 end) as community
from dbo.total_events_by_category
where username  = i.username
) as l)
from member
join deleted as i
  on i.username = member.username
join attendance
  on i.username = attendance.username

Thanks!! It works!