Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Something Most Likely Simple Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, January 16, 2013 8:52 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 3:42 PM Points: 915, Visits: 2,927
 Hello EveryoneI 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.1Now, 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 = TotalLA - 30.1 = 57LA - 40.1 = 278LA - 50.1 = 67NY = 82 = TotalNY - 30.1 = 13NY - 40.1 = 55NY - 50.1 = 14Indy = 142 = TotalIndy - 30,1 = 19Indy - 40.1 = 77Indy - 50.1 = 46The 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 ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY'SELECT DateDataRepresents, CodeNumber, Location FROM #Diagnosis;DROP TABLE #Diagnosis;etc.........The table has approx half a million rowsWhat is the most efficient way to get the counts?Thank You in advance for your help, suggestions and adviceAndrew SQLDBA
Post #1408130
 Posted Thursday, January 17, 2013 1:53 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, August 28, 2013 2:37 AM Points: 5,075, Visits: 4,834
 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 ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY';with cte as(SELECT DateDataRepresents, Location , CodeNumber,COUNT(RowID) AS CountFROM #DiagnosisGROUP BY DateDataRepresents, CodeNumber,Locationwith cube)select DateDataRepresents, Location, ISNULL(CONVERT(VARCHAR,CodeNumber),'Total') AS CodeNumber, Countfrom cte where datedatarepresents is not null and location is not null order by Location,CodeNumber,DateDataRepresentsDROP TABLE #Diagnosis;
Post #1408216
 Posted Thursday, January 17, 2013 4:47 AM
 SSC Eights! Group: General Forum Members Last Login: Today @ 3:42 PM Points: 915, Visits: 2,927
 AnthonyThanks is perfect.Thank You. I greatly appreciate that. I never thought to use cube.Andrew SQLDBA
Post #1408329
 Posted Thursday, January 17, 2013 4:49 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, August 28, 2013 2:37 AM Points: 5,075, Visits: 4,834
 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.
Post #1408332
 Posted Thursday, January 17, 2013 5:06 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:07 PM Points: 40,086, Visits: 32,806
 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 ShawMicrosoft Certified Master: SQL Server 2008, MVPSQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #1408348
 Posted Thursday, January 17, 2013 10:16 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, September 03, 2013 8:24 AM Points: 1,240, Visits: 5,421
 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 AllenBusiness Intelligence AnalystPhiladelphia, PA
Post #1408535
 Posted Thursday, January 17, 2013 12:59 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 3:42 PM Points: 915, Visits: 2,927
 Thank You EveryoneOk, 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 advanceAndrew SQLDBA
Post #1408580
 Posted Friday, January 18, 2013 7:53 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, December 05, 2013 8:28 AM Points: 286, Visits: 1,584
 AndrewSQLDBA (1/17/2013)Thank You EveryoneOk, 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 ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '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 CodeNumberSELECT DateDataRepresents, Location, CAST(CodeNumber as CHAR(4)) AS CodeNumber, Count_nbFROM cte WHERE DateDataRepresents IS NOT NULL AND Location IS NOT NULL UNION ALL-- Totals by LocationSELECT DateDataRepresents, Location, 'Total' AS CodeNumber, SUM(Count_nb) AS Count_nbFROM cte WHERE DateDataRepresents IS NOT NULL AND Location IS NOT NULL GROUP BY DateDataRepresents, Location ORDER BY Location, CodeNumber, DateDataRepresentsDROP TABLE #Diagnosis;
Post #1408927

 Permissions