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
 Ten Centuries Group: General Forum Members Last Login: Wednesday, August 24, 2016 12:43 PM Points: 1,004, Visits: 3,427
 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: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067
 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
 Ten Centuries Group: General Forum Members Last Login: Wednesday, August 24, 2016 12:43 PM Points: 1,004, Visits: 3,427
 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: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067
 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: Saturday, December 3, 2016 5:18 AM Points: 45,619, Visits: 44,147
 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, MVP, M.Sc (Comp Sci)SQL 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
 SSCrazy Group: General Forum Members Last Login: Today @ 11:28 AM Points: 2,401, Visits: 9,496
 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, PAHow to post data/code on a forum to get the best help.Make sure that you include code in the appropriate IFCode tags, e.g. [code="sql"][/code]. You can find the IFCode tags on the left when you are writing a post.How to Post Performance Problems
Post #1408535
 Posted Thursday, January 17, 2013 12:59 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, August 24, 2016 12:43 PM Points: 1,004, Visits: 3,427
 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: Yesterday @ 7:22 AM Points: 287, Visits: 2,042
 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