Case sensitive with ROLLUP

• rash3554

  SSCommitted

  Points: 1984

  More actions

  March 15, 2016 at 3:07 pm

  #312108

  Hi there,

  I have a table(#mytable) that contains basic financial info about companies.

  CREATE TABLE #mytable

  (

  Companyid varchar(50),

  DataDescription varchar(100),

  Value DECIMAL(23,6),

  Department varchar(100),

  CurrencyIS03 varchar(5),

  DateofData datetime

  )

  INSERT INTO #mytable (Companyid, DataDescription, Value, Department, CurrencyIS03, DateofData)

  SELECT

  'A100', 'Revenue', '1000.00', 'Corporate', 'USD', '2014-12-31 00:00:00'

  UNION ALL

  SELECT 'A100','Revenue','2000.00','Banking','USD','2014-12-31 00:00:00'

  UNION ALL

  SELECT 'A100','Revenue','-100.00','banking','USD','2014-12-31 00:00:00'

  UNION ALL

  SELECT 'A100','Revenue','2500.00','Corporate','USD','2013-12-31 00:00:00'

  UNION ALL

  SELECT 'A100','Revenue','3000.00','Banking','USD','2013-12-31 00:00:00'

  UNION ALL

  SELECT 'A100','Operating Income','10000.00','Corporate','USD','2014-12-31 00:00:00'

  UNION ALL

  SELECT 'A100','Operating Income','1000.00','Banking','USD','2014-12-31 00:00:00'

  UNION ALL

  SELECT 'A200','Revenue','1100.00','Corporate','USD','2013-12-31 00:00:00'

  UNION ALL

  SELECT 'A200','Revenue','3000.00','Banking','USD','2013-12-31 00:00:00'

  UNION ALL

  SELECT 'A200','Operating Income','5500.00','Corporate','USD','2014-12-31 00:00:00'

  UNION ALL

  SELECT 'A200','Operating Income','10000.00','Banking','USD','2014-12-31 00:00:00'

  I am supposed to calculate subtotals based on Companyid, DataDescription, Department, CurrencyIS03, DateofData.

  So, I used the query

  SELECT distinct Companyid,

  DataDescription,

  SUM(Value) AS total,

  COALESCE(Department,'Total') Department,

  CurrencyIS03,

  DateofData

  FROM #mytable

  GROUP BY CompanyID,

  Datadescription,

  ROLLUP(Department),

  CurrencyIS03,

  DateofData

  ORDER BY CompanyID ASC,

  Datadescription DESC,

  DateofData DESC,

  Department ASC

  But as you see in the table for CompanyID A100 and Date=2014-12-31 00:00:00, there are 2 rows, one called 'Banking' and another 'banking'.

  How can make the above query case sensitive?

  Thanks

• pietlinden

  SSC Guru

  Points: 63509

  More actions

  March 15, 2016 at 3:21 pm

  #1865476

  add a case sensitive collation to your query.

  Here[/url]'s a really good example.

  here's one I did ...

  SELECT *

  FROM dbo.Patient

  WHERE LastName COLLATE Latin1_General_CS_AS LIKE 'Do%';

  so a last name starting with 'DO' would not match.

• rash3554

  SSCommitted

  Points: 1984

  More actions

  March 15, 2016 at 3:39 pm

  #1865482

  Ok, I used your logic but still cannot do a case sensitive rollup.

  SELECT *

  FROM #mytable

  WHERE CompanyID='A100' and DateOfdata='2014-12-31 00:00:00.000'

  and DataDescription='Revenue'

  and Department COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%%'.

  Returns 3 rows as expected, but If I plug this in the query that I have provided with ROLLUP to calculate subtotal,

  then it treats both 'Banking' and 'banking' as same.

  Here's what I did

  SELECT Companyid,

  DataDescription,

  SUM(Value) AS total,

  COALESCE(Department,'Total') Department,

  CurrencyIS03,

  DateofData

  FROM #mytable

  WHERE CompanyID='A100' and DateOfdata='2014-12-31 00:00:00.000'

  and DataDescription='Revenue'

  and Department COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%%'

  GROUP BY CompanyID,

  Datadescription,

  ROLLUP(Department),

  CurrencyIS03,

  DateofData

  ORDER BY CompanyID ASC,

  Datadescription DESC,

  DateofData DESC,

  Department ASC

• rash3554

  SSCommitted

  Points: 1984

  More actions

  March 16, 2016 at 4:10 am

  #1865539

  I forgot to add ROLLUP(Department COLLATE SQL_Latin1_General_CP1_CS_AS) to the query and COALESCE(Department COLLATE SQL_Latin1_General_CP1_CS_AS,'Total') Department in the select clause ...Now I got it to work

  Thanks so much.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  March 16, 2016 at 9:54 pm

  #1865792

  I have to ask... why do they want to differentiate between a proper case spelling and a low case spelling of the same word? Heh... please tell me they're not placing significance between the two because THAT would be a gross violation of normal form to have two identically spelled English words mean different thing based on the differences in capitalization.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• rash3554

  SSCommitted

  Points: 1984

  More actions

  March 17, 2016 at 11:07 am

  #1865936

  This is the data we got from vendor and there are several thousands of records with this issue. I am guessing somebody who did data entry, fat fingered this... our project manager decided that we shouldn't be deleting those records 🙁 . We are awaiting correction from the vendor which might take couple of months..but until then this was the solution proposed.