Something Most Likely Simple

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  January 16, 2013 at 8:52 pm

  #268816

  Hello Everyone

  I have a rather odd query that I am trying to figure out the best way to code. I am going to try and explain the best way possible. I want to be able to do this without incorporating a loop.

  I have a table that stores patient information about Diagnosis. There are three distinct hospital locations. Lets Call them LA, NY and Indy. There are three CodeNumbers for each Location : 30.1, 40.1 and 50.1

  Now, what I am after is the count for each CodeNumber, per Each Location per each day.

  These are the counts of each code per each location per each day.

  Counts:

  LA = 402 = Total

  LA - 30.1 = 57

  LA - 40.1 = 278

  LA - 50.1 = 67

  NY = 82 = Total

  NY - 30.1 = 13

  NY - 40.1 = 55

  NY - 50.1 = 14

  Indy = 142 = Total

  Indy - 30,1 = 19

  Indy - 40.1 = 77

  Indy - 50.1 = 46

  The way the data is stored is like this:

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL

  DROP TABLE #Diagnosis

  --===== Create the test table with

  CREATE TABLE #Diagnosis

  (

  RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

  , DateDataRepresents Date

  , CodeNumber DECIMAL(4,1)

  , Location varchar(5)

  )

  INSERT INTO #Diagnosis

  (DateDataRepresents, CodeNumber, Location)

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'NY' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY'

  SELECT

  DateDataRepresents

  , CodeNumber

  , Location

  FROM #Diagnosis;

  DROP TABLE #Diagnosis;

  etc.........

  The table has approx half a million rows

  What is the most efficient way to get the counts?

  Thank You in advance for your help, suggestions and advice

  Andrew SQLDBA

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  January 17, 2013 at 1:53 am

  #1578143

  Something like the following?

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL

  DROP TABLE #Diagnosis

  --===== Create the test table with

  CREATE TABLE #Diagnosis

  (

  RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

  , DateDataRepresents Date

  , CodeNumber DECIMAL(4,1)

  , Location varchar(5)

  )

  INSERT INTO #Diagnosis

  (DateDataRepresents, CodeNumber, Location)

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'NY' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY'

  ;with cte as

  (

  SELECT

  DateDataRepresents

  , Location

  , CodeNumber

  ,COUNT(RowID) AS Count

  FROM #Diagnosis

  GROUP BY DateDataRepresents, CodeNumber,Location

  with cube

  )

  select

  DateDataRepresents,

  Location,

  ISNULL(CONVERT(VARCHAR,CodeNumber),'Total') AS CodeNumber,

  Count

  from cte where datedatarepresents is not null and location is not null order by Location,CodeNumber,DateDataRepresents

  DROP TABLE #Diagnosis;

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  January 17, 2013 at 4:47 am

  #1578233

  Anthony

  Thanks is perfect.

  Thank You. I greatly appreciate that. I never thought to use cube.

  Andrew SQLDBA

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  January 17, 2013 at 4:49 am

  #1578235

  If also not familiar lookup WITH ROLLUP, its another one to look at along with WITH CUBE, both produce different outputs so need to ensure you choose the right one, ROLLUP wont work in this instance but CUBE does.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 17, 2013 at 5:06 am

  #1578247

  Also, since this is SQL 2008, have a read up on Grouping Sets. They allow a lot more flexibility than cube and rollup do.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  January 17, 2013 at 10:16 am

  #1578411

  GilaMonster (1/17/2013)

  ---

  Also, since this is SQL 2008, have a read up on Grouping Sets. They allow a lot more flexibility than cube and rollup do.

  Especially since WITH CUBE and WITH ROLLUP are being deprecated.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  January 17, 2013 at 12:59 pm

  #1578447

  Thank You Everyone

  Ok, I have been reading up on Grouping Sets, but I still am not getting it. How would one go about writing a query to work correctly with the same data that was given and not use Cube or Rollup?

  Thanks in advance

  Andrew SQLDBA

• Steve Thompson-454462

  SSCrazy

  Points: 2624

  More actions

  January 18, 2013 at 7:53 am

  #1578735

  AndrewSQLDBA (1/17/2013)

  ---

  Thank You Everyone

  Ok, I have been reading up on Grouping Sets, but I still am not getting it. How would one go about writing a query to work correctly with the same data that was given and not use Cube or Rollup?

  You could always "roll your own" Totals. Performs fine with the small sample data set; if your production set is much larger, you'd want to compare metrics to see if this code scales.

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL

  DROP TABLE #Diagnosis

  --===== Create the test table with

  CREATE TABLE #Diagnosis

  (

  RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

  , DateDataRepresents Date

  , CodeNumber DECIMAL(4,1)

  , Location varchar(5)

  )

  INSERT INTO #Diagnosis

  (DateDataRepresents, CodeNumber, Location)

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'NY' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 40.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY' UNION ALL

  SELECT '2012-12-16', 30.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 40.1, 'NY' UNION ALL

  SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'NY' UNION ALL

  SELECT '2012-12-16', 50.1, 'LA' UNION ALL

  SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

  SELECT '2012-12-16', 50.1, 'NY'

  ;with cte as

  (

  SELECT

  DateDataRepresents,

  Location,

  CodeNumber,

  COUNT(RowID) AS Count_nb

  FROM

  #Diagnosis

  GROUP BY

  DateDataRepresents,

  CodeNumber,

  Location

  )

  --Totals by CodeNumber

  SELECT

  DateDataRepresents,

  Location,

  CAST(CodeNumber as CHAR(4)) AS CodeNumber,

  Count_nb

  FROM

  cte

  WHERE

  DateDataRepresents IS NOT NULL

  AND Location IS NOT NULL

  UNION ALL

  --Totals by Location

  SELECT

  DateDataRepresents,

  Location,

  'Total' AS CodeNumber,

  SUM(Count_nb) AS Count_nb

  FROM

  cte

  WHERE

  DateDataRepresents IS NOT NULL

  AND Location IS NOT NULL

  GROUP BY

  DateDataRepresents,

  Location

  ORDER BY

  Location,

  CodeNumber,

  DateDataRepresents

  DROP TABLE #Diagnosis;