Find 'MAX' of an bit type - Further adventures in report writing

• Stueyd

  Ten Centuries

  Points: 1315

  More actions

  January 19, 2017 at 9:33 am

  #401428

  I hate the person who 'designed' a database I've inherited..... 🙂

  I'm producing a server status report in SSRS. One of the values I now need to return from the dataset select is from a bit data type that stores a true (1) or false (0) value.
  There are many rows of data and during the period of time i'm looking at the value may have turned from true to false and possibly back again.
  All i'm interested in is if at any point it's value has been true to display that, otherwise display false and I should end up with 1 row for each server name.
  I was hoping that as 1 is higher than 0 I could cast the bit as a numeric and then get the MAX value. Eg, do something like :

  SELECT DISTINCT
  (ServerName),
  MAX(CPU),
  MAX(CAST(MyBITColumn AS TINYINT))
  FROM, etc, etc...

  But that doesn't work. In my test data which has both values, rather than returning the max 'true' value of '1', I get 2 rows returned with 1 row showing '0' and  the other row'1'.
  Is it possible to display the max numeric value in an elegantly simple way similar to what doesn't work above? Or will I need some sort of convoluted subquery ?

  Thanks!

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  January 19, 2017 at 9:51 am

  #1923769

  Stueyd - Thursday, January 19, 2017 9:33 AM

  I hate the person who 'designed' a database I've inherited..... 🙂

  I'm producing a server status report in SSRS. One of the values I now need to return from the dataset select is from a bit data type that stores a true (1) or false (0) value.
  There are many rows of data and during the period of time i'm looking at the value may have turned from true to false and possibly back again.
  All i'm interested in is if at any point it's value has been true to display that, otherwise display false and I should end up with 1 row for each server name.
  I was hoping that as 1 is higher than 0 I could cast the bit as a numeric and then get the MAX value. Eg, do something like :

  SELECT DISTINCT
  (ServerName),
  MAX(CPU),
  MAX(CAST(MyBITColumn AS TINYINT))
  FROM, etc, etc...

  But that doesn't work. In my test data which has both values, rather than returning the max 'true' value of '1', I get 2 rows returned with 1 row showing '0' and  the other row'1'.
  Is it possible to display the max numeric value in an elegantly simple way similar to what doesn't work above? Or will I need some sort of convoluted subquery ?

  Thanks!
  

  It sounds like you've included your MyBITColumn in the GROUP BY clause.  If that's not the issue, then I suggest you give us the whole query and some sample data as outlined in the first link in my signature.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 19, 2017 at 9:52 am

  #1923770

  Stueyd - Thursday, January 19, 2017 9:33 AM

  I hate the person who 'designed' a database I've inherited..... 🙂

  I'm producing a server status report in SSRS. One of the values I now need to return from the dataset select is from a bit data type that stores a true (1) or false (0) value.
  There are many rows of data and during the period of time i'm looking at the value may have turned from true to false and possibly back again.
  All i'm interested in is if at any point it's value has been true to display that, otherwise display false and I should end up with 1 row for each server name.
  I was hoping that as 1 is higher than 0 I could cast the bit as a numeric and then get the MAX value. Eg, do something like :

  SELECT DISTINCT
  (ServerName),
  MAX(CPU),
  MAX(CAST(MyBITColumn AS TINYINT))
  FROM, etc, etc...

  But that doesn't work. In my test data which has both values, rather than returning the max 'true' value of '1', I get 2 rows returned with 1 row showing '0' and  the other row'1'.
  Is it possible to display the max numeric value in an elegantly simple way similar to what doesn't work above? Or will I need some sort of convoluted subquery ?

  Thanks!
  

  If your query is returning 2 rows, then you might have a problem with your GROUP BY clause.
  DECLARE @Sample TABLE(ServerName varchar(20), MyBITColumn bit);

  INSERT INTO @Sample(ServerName, MyBITColumn)
VALUES ('Server 1', 0),
   ('Server 1', 1),
   ('Server 1', 1),
   ('Server 2', 1),
   ('Server 2', 0),
   ('Server 3', 0);

  SELECT ServerName,
  MAX( CAST(MyBITColumn AS tinyint))
FROM @Sample
GROUP BY ServerName;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  January 19, 2017 at 9:59 am

  #1923775

  Also, it's pointless to have both a DISTINCT keyword and a GROUP BY in the same section of a query.  The GROUP BY has an implicit DISTINCT in it, and the DISTINCT keyword is ignored.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Stueyd

  Ten Centuries

  Points: 1315

  More actions

  January 19, 2017 at 10:00 am

  #1923776

  Chaps!
  Many thanks. The problem was my GROUP BY.
  Always good to have a second pair of eyes to spot the obvious cock ups 🙂
  I simply removed the MyBITColumn from the group by clause and it now works as intended.