none
Case statement for other info

    Question

  • Hi guys, I really need your help on a case please.
     
    I need to retrieve info from a column that contains a name instead of a numeric value such as " CASE WHEN EventCodes = 'Registered' THEN COUNT(EventCodes) END AS 'Tournament Registrations'

    How can I find out also the number of people that haven't registered? I can't do a "WHERE NOT" as I also need to obtain info just from one month so I need to populate the WHERE clause with WHERE Date BETWEEN this AND this.

    Any help would be appreciated thanks :) 
    • Edited by PaulSmith86 Saturday, January 25, 2014 5:25 PM
    Saturday, January 25, 2014 5:24 PM

Answers

  • You should have a player ID in the BalanceAudit table to know which player registered. You don't need a Balance after column present because you can always create a derived column based on the data you have.

      CREATE TABLE BalanceAudit (
      EventID bigint IDENTITY(1,1),
      EventDateTime date,
      EventTypeID int CONSTRAINT FK_BalanceAudit_EventTypeID FOREIGN KEY REFERENCES EventType(EventTypeID) ON UPDATE CASCADE, 
      PlayerID int CONSTRAINT FK_BalanaceAudit_PlayerID FOREIGN KEY REFERENCES Player(PlayerID),
      CurrencyCode varchar(3),
      BalanceAdjustments decimal(10,2),
      BalanceAfter decimal(10,2)
      );
    I think your balance audit should look something like this may be, so that you know which player has registered and which hasn't...

    Saturday, January 25, 2014 7:53 PM

All replies

  • COUNT(CASE WHEN EventCodes = 'Registered' THEN EventCodes END) AS 'Tournament Registrations', 
    COUNT(CASE WHEN EventCodes = 'Registered' 
     HEN null else EventCodes END) AS 'Tournament No Registrations'
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Saturday, January 25, 2014 5:32 PM
  •  SUM(CASE WHEN EventCodes = 'Registered' THEN 1 Else 0 END) AS 'Tournament Registrations'
    ,SUM(CASE WHEN EventCodes <> 'Registered' Or EventCodes IS NULL THEN 1 Else 0  END) AS 'Not Registered'
    

    Saturday, January 25, 2014 5:43 PM
  • Do you need only a case statement? How about the below code?

    DECLARE @Events TABLE
    (
    EventID INT,
    EventCode VARCHAR(20)
    )
    
    INSERT INTO @Events VALUES(1, 'Registered'), (2, 'Registered'), (3, NULL), (4, 'Not Registered')
    
    ;WITH CTE AS
    (SELECT ISNULL(EventCode, 'Not Registered') AS EventCode
    FROM @Events
    )
    SELECT EventCode, COUNT(EventCode) AS Count
    FROM CTE
    GROUP BY EventCode

    Good Luck :)

    Visit www.sqlsaga.com for more t-sql code snippets or BI related how to's

    Saturday, January 25, 2014 5:46 PM
  • Sorry guys, forgot to say that  there are no null values in the EventCode column :)  I've tried what Russ Loski suggested but it didn't help as it would count the people that didn't register too when I do the count, it seems to count everyone in the EventCodes column, regardless if they are down as "Registered" or not :(   Something like WHERE NOT EventCode = 'Registered' Would do the trick but I can't do both the registered and not registered in one query this way cause the WHERE clause has to be populated with the date.
    • Edited by PaulSmith86 Saturday, January 25, 2014 5:55 PM
    Saturday, January 25, 2014 5:52 PM
  • Can you post your table DDL  with some sample data?

    The syntax  of SUM(CASE... should work for you.

    Saturday, January 25, 2014 6:00 PM
  • Sorry guys, forgot to say that  there are no null values in the EventCode column :)  I've tried what Russ Loski suggested but it didn't help as it would count the people that didn't register too when I do the count, it seems to count everyone in the EventCodes column, regardless if they are down as "Registered" or not :(   Something like WHERE NOT EventCode = 'Registered' Would do the trick but I can't do both the registered and not registered in one query this way cause the WHERE clause has to be populated with the date.

    I'm not sure why Russ's query won't do the job and why you can't return both counts of registered and not registered due to the date in the WHERE clause.  I think it will better help us help you if you post a CREATE TABLE statement.  Below is an example a complete SELECT query:

    SELECT
    	 SUM(CASE WHEN EventCodes = 'Registered' THEN 1 Else 0 END) AS [Tournament Registrations]
    	,SUM(CASE WHEN EventCodes <> 'Registered' THEN 1 Else 0  END) AS [Not Registered]
    FROM dbo.TournamentInvitee
    WHERE
    	InvitationDate BETWEEN @StartDate AND @EndDate;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, January 25, 2014 6:21 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed; how did you put multiple event codes in a scalar column? The column is simply event_code). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Now we have to read your mind and do all your typing!! 

    CASE is an expression and not a statement. 

    >> I need to retrieve info from a column that contains a name instead of a numeric value such as "CASE WHEN event_code = 'Registered' THEN COUNT(event_code) END AS tournament_registration_cnt” <<

    Not quite; where is the GROUP BY? 

    >> How can I find out also the number of people that haven't registered? I can't do a "WHERE NOT" as I also need to obtain info just from one month so I need to populate the WHERE clause with WHERE Date BETWEEN this AND this. <<

    Without any DDL, we cannot answer this. Here is my guess:

    SELECT event_name,
          SUM(CASE event_code = 'Registered' THEN 1 ELSE 0 END)
           AS tournament_registration_cnt,
          SUM(CASE event_code <> 'Registered' THEN 1 ELSE 0 END)
           AS tournament_NON_registration_cnt,
     FROM Tournaments 
     GROUP BY event_name;

    >> Any help would be appreciated thanks<<

    Minimal Netiquette would be appreciated, too.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, January 25, 2014 7:03 PM
  • My apologies Celko :-) I have included all the necessary info just above your post, thanks.
    Saturday, January 25, 2014 7:12 PM
  • From the above DDL, how do you know which player registered to which event? there is no playerID on BalanceAdjustment table?? anything I am missing...?
    Saturday, January 25, 2014 7:32 PM
  • You need to sort through your table relationship first.

    When you don't have a correct join in your query in the first place, you cannot get a correct count result.

    Saturday, January 25, 2014 7:34 PM
  • thank you for pointing that out, can you please advise what should be the best option to join those tables ?
    Saturday, January 25, 2014 7:42 PM
  • You should have a player ID in the BalanceAudit table to know which player registered. You don't need a Balance after column present because you can always create a derived column based on the data you have.

      CREATE TABLE BalanceAudit (
      EventID bigint IDENTITY(1,1),
      EventDateTime date,
      EventTypeID int CONSTRAINT FK_BalanceAudit_EventTypeID FOREIGN KEY REFERENCES EventType(EventTypeID) ON UPDATE CASCADE, 
      PlayerID int CONSTRAINT FK_BalanaceAudit_PlayerID FOREIGN KEY REFERENCES Player(PlayerID),
      CurrencyCode varchar(3),
      BalanceAdjustments decimal(10,2),
      BalanceAfter decimal(10,2)
      );
    I think your balance audit should look something like this may be, so that you know which player has registered and which hasn't...

    Saturday, January 25, 2014 7:53 PM
  • Thanks very much, that worked ! appreciate the help guys :) 
    Saturday, January 25, 2014 8:05 PM
  • We are always happy to help.. It would be wonderful if you can mark which answer has helped you solve this issue so that, people somewhere else with the same problem doesn't have to ask but just refer to this thread.

    Sunday, January 26, 2014 11:03 PM