Top 5 From Many Columns

[PCHomepage]

I looked through other postings about this but it wasn't clear to me what I needed to do in this case. I have a single row of data, summed from multiple rows, and I am trying to get only the top 5 from those results. How is that done?

Also, is there a way to substitute the column names on the fly, possibly from an array, list or another table, so that I have the name and the top 5 summed values for printing?

Any ideas? Thanks in advance.

USE OSHPD
DECLARE @YearVal varchar(30);
SET @YearVal = 2010;
SELECT              SUM([INFECT-DISEASE]), 
                    SUM(NEOPLSMS),
                    SUM(ENDOCRN),
                    SUM(BLOOD_DIS),
                    SUM(MENTAL),
                    SUM(NERVIOUS),
                    SUM(CIRCULATORY), 
                    SUM(RESPIRTRY),
                    SUM([DIGEST]),
                    SUM(GENOURINRY),
                    SUM(PREG),
                    SUM(SKIN),
                    SUM(MUSCLSKTAL), 
                    SUM(CONGNTL),
                    SUM(PERNATL),
                    SUM([ILL-DEFND]),
                    SUM(POISON),
                    SUM([HLTH FACTORS]),
                    SUM([DENTAL-DX]), 
                    SUM([FAM PLN S]),
                    SUM([OTH-DX]),
                    SUM(TOTL_DX),
                    SUM([E&M-NEW]),
                    SUM([E&M-ESTAB]),
                    SUM([E&M-HOSP]), 
                    SUM([E&M-CONSLTS]),
                    SUM([E&M-OTH]),
                    SUM([E&M-SNF-REL]),
                    SUM([E&M-CASE MGT]),
                    SUM([E&M-PRVENT-CHLD]), 
                    SUM([E&M-PRVENT-ADLT]),
                    SUM([E&M-CONSUL]),
                    SUM([ANESTHS-PROC]),
                    SUM([INTGMNTRY-PROC]),
                    SUM([MUSCLSKTAL-PROC]), 
                    SUM([RESPIR-PROC]),
                    SUM([CARDIO-PROC]),
                    SUM([LYMPH-PROC]),
                    SUM([DIAPH-PROC]),
                    SUM([DIGEST-PROC]), 
                    SUM([URINARY-PROC]),
                    SUM([MALE-GNTL-PROC]),
                    SUM([INTSEX-SURG]),
                    SUM([FEMALE-GNTL-PROC]),
                    SUM([MATRNL-CARE]),
                    SUM([ENDOCRIN-PROC]),
                    SUM([NERV-PROC]),
                    SUM([EYE-PROC]),
                    SUM([AUDITRY-PROC]),
                    SUM([RADIOLGY-PROC]), 
                    SUM([PATH-PROC]),
                    SUM([SP SRVS-PROC]),
                    SUM([Z CODES]),
                    SUM([DENTAL-PROC]),
                    SUM([CAT III CODES]), 
                    SUM([OTH-PROC]),
                    SUM(TOTL_PROC),
                    SUM(MAMMOGRM),
                    SUM(HIV),
                    SUM([PAP SMR]),
                    SUM([CONTRCPTV MGMT]), 
                    SUM([D-P-T]),
                    SUM([HIB B]),
                    SUM([HEP-A]),
                    SUM([HEP-B]),
                    SUM([HEP-B_HIB]),
                    SUM(FLU),
                    SUM(MMR), 
                    SUM(PNEMOCCAL),
                    SUM(POLIO),
                    SUM(VARICELLA)
FROM [Section 1-2] INNER JOIN
[Section 3-5] ON [Section 1-2].OSHPD_ID = [Section 3-5].OSHPD_ID INNER JOIN
[Section 6-8] ON [Section 1-2].OSHPD_ID = [Section 6-8].OSHPD_ID
WHERE [Section 1-2].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS)
AND  YEAR(END_DATE) = @YearVal
AND   [Section 3-5].REPT_YEAR = @YearVal
AND   [Section 6-8].REPT_YEAR = @YearVal

Thanks in advance

 

[PCHomepage]

Perhaps simplified a bit:

USE OSHPD
DECLARE @YearVal varchar(30);
SET @YearVal = 2010;
SELECT  SUM([INFECT-DISEASE]) [Infectious Disease], 
        SUM(NEOPLSMS) Neoplasms,
        SUM(ENDOCRN) Endocrine,
        SUM(BLOOD_DIS) [Blood Disease],
        SUM(MENTAL) [Mental Disorders],
        SUM(NERVIOUS) [Nervous System],
        SUM(CIRCULATORY) Circulatory, 
        SUM(RESPIRTRY) Respiratory,
        SUM([DIGEST]) [Digestive System],
        SUM(GENOURINRY) [Genitourinary System],
        SUM(PREG) Pregnancy,
        SUM(SKIN) [Skin and Subcutaneous Tissue],
        SUM(MUSCLSKTAL) Musculoskeletal, 
        SUM(CONGNTL) [Congenital Anomalies],
        SUM(PERNATL) Perinatal,
        SUM([ILL-DEFND]) [Ill-defined Conditions],
        SUM(POISON) [Injury and Poisoning],
        SUM([HLTH FACTORS]) [Factors Influencing Health Status],
        SUM([DENTAL-DX]) [Dental Diagnosis], 
        SUM([FAM PLN S]) [Family Planning S-Codes],
        SUM([OTH-DX]) [Other Diagnosis]
    	FROM [Section 1-2] INNER JOIN
[Section 3-5] ON [Section 1-2].OSHPD_ID = [Section 3-5].OSHPD_ID INNER JOIN
[Section 6-8] ON [Section 1-2].OSHPD_ID = [Section 6-8].OSHPD_ID
WHERE [Section 1-2].OSHPD_ID IN (SELECT OSHPD_ID FROM PARTNER_CLINICS)
AND  YEAR(END_DATE) = @YearVal
AND   [Section 3-5].REPT_YEAR = @YearVal
AND   [Section 6-8].REPT_YEAR = @YearVal

 

[Qik3Coder]

You actually need a reverse cross-tab.
You have to put the values into a "tall" table so you can sort them, then bring them back out.

This is going to look nasty (because it is, and I haven't had to do it)

Wrap all of your join logic into a table valued function.
The table valued function will have to execute dynamic sql, because you're passing in the column name you want.

Select each column individually into a table table variable, if you are doing high level reporting across multiple customers you should be able to load each "type" at once. In addition to CustomerID, and Value%, store the explicit name of the column you want.

INSERT INTO @myTempTable(CustomerID, Value, Type)
Select a.CustomerID, SUM(a.Value), 'Infectious Disease'
FROM  dbo.GetMyData('Infectious Disease') a

INSERT INTO @myTempTable(CustomerID, Value, Type)
Select a.CustomerID, SUM(a.Value), 'Neoplasms'
FROM  dbo.GetMyData('Neoplasms') a

Ideally you could load all the column names into a temp table and then walk the list with a loop or a cursor.

You could probably do this with a CTE, but I don't have much experience there, and it requires SQL 2k5 or SQK 2k8.

Lodlaiden



You've got questions and source code. We want both!

 

[PCHomepage]

Thanks for the reply! I do have SQLServer 2008 though have never done this type of SQL before so I was somewhat stumped as to how it worked but your explanation helped immensely.

"CustomerID" isn't one of my fields but aside from that, are you saying that I need to create an insert for each summed value? I can do that but I wanted to be sure that's what was needed first as I have only today to get it done.

Thanks again.

 

[Qik3Coder]

Wait. You're look for the top 5 across the board?
This looks like you have multiple detail tables.

You shouldn't need a table function for this, from what i can gather from the select you posted.
You will have to expand out the INSERT INTO for all your columns, but the end should work.

Declare @_tbl table(
Condition varchar(50),
Value decimal(10,2))

INSERT INTO @_tbl Select 'Infectious Disease', SUM([INFECT-DISEASE])  FROM [Section 1-2] WHERE Year(EndDate) = @YearVal
INSERT INTO @_tbl Select 'NEOPLSMS', SUM(NEOPLSMS) FROM [Section 1-2] WHERE Year(EndDate) = @YearVal

SELECT TOP 5 Condition, Value FROM @_tbl ORDER BY Value Desc

Lodlaiden

You've got questions and source code. We want both!

 

[PCHomepage]

Thanks a bundle as I think you've gotten me onto the right track! After I create it in the SQLServer Analyzer, I'll see how to incorporate the dynamic WHERE that my PHP uses but so far, it seems to work perfectly in the analyzer. Here's a sample of the final code in case it helps anyone else:

Declare @_tbl table(
Condition varchar(50),
Value decimal(10,0))
DECLARE @YearVal varchar(30);
SET @YearVal = 2010;

INSERT INTO @_tbl Select 'Infectious Disease', SUM([INFECT-DISEASE])  FROM [Section 3-5] WHERE REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Neoplasms', SUM(NEOPLSMS) FROM [Section 3-5] WHERE REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Endocrine', SUM(ENDOCRN) FROM [Section 3-5] WHERE REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Blood Disease', SUM(BLOOD_DIS) FROM [Section 3-5] WHERE REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Mental Disorders', SUM(MENTAL) FROM [Section 3-5] WHERE REPT_YEAR = @YearVal
INSERT INTO @_tbl Select 'Nervous System', SUM(NERVIOUS) FROM [Section 3-5] WHERE REPT_YEAR = @YearVal

SELECT TOP 5 Condition, Value FROM @_tbl ORDER BY Value Desc

To answer your question, yes, it's just the top 5 that I need. It is indeed joining multiple tables and, as the WHERE clause is somewhat dynamic in the actual PHP, it is best to not change it. Although all the values needed are in a single table, to be able to fetch them needs access to the other tables tables because the search involves pulling only certain records based on the ClinicID but that part is in a PHP function so, after testing, I should be able to add it fairly easily. Just fetching by year isn't enough, unfortunately, although it will get me by for now.

 

[PCHomepage]

One thing that might help would be if I could also get the OSHPD_ID and REPT_YEAR columns from table [Section 3-5]. Then I can add the conditional to the SELECT TOP 5 query only rather than having it on each of the INSERT INTO lines. It would make @_tbl larger but there's not that much data so shouldn't be a significant problem.

Getting those two additional fields, though, gives the usual aggregate function error and I am not sure how to add GROUP BY to solve it and still get valid data. When I add GROUP BY the resulting data is wrong but I may be doing it incorrectly

However, in PHP, I not sure how to fetch the results. Do I run sqlsrv_query() on only the SELECT portion or do I run the whole thing as a single query? Doing the latter gives no results but I am not sure how to do the former.

 

[markros]

You need to UNPIVOT your results first and get 5 top amounts.

PluralSight Learning Library

 

[Qik3Coder]

You will want to run the whole thing as one batch.
Are you saying the where clause on each of the individual selects might be dynamic?

In that case, you may want to use a global temp table or a permanent table. Wipe it before you start.


Lodlaiden

PS: This thing sounds like a cluster, and while you may not have time today, it would be worthwhile to migrate things like the "client record" filtering into the database.

You've got questions and source code. We want both!