A complicated situation
-
Hi,
I am working on a project where the resistance to some antibiotics is studied. We have a database of patients checked for resistance. The result of test for each antibiotic comes back as either "1" for "not-resistant" and "2" for "resistant". A simplified table is something like this :
DECLARE @Test_TBL TABLE(ID VARCHAR(3), City VARCHAR(10),AntiBiotic VARCHAR(20), Result INT)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('1', 'Denver', 'AMP', 1)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('1', 'Denver', 'TET', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('2', 'Denver', 'SPT', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('3', 'New York', 'AMP', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('3', 'New York', 'SPT', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'AMP', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'TET', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'STR', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('5', 'New York', 'AMP', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('5', 'New York', 'STR', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'TET', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'SPT', 1)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'STR', 2)
Now, I need to make a report that looks like this :
I appreciate for any help.
-
RZ52 (2/28/2013)
Hi,I am working on a project where the resistance to some antibiotics is studied. We have a database of patients checked for resistance. The result of test for each antibiotic comes back as either "1" for "not-resistant" and "2" for "resistant". A simplified table is something like this :
DECLARE @Test_TBL TABLE(ID VARCHAR(3), City VARCHAR(10),AntiBiotic VARCHAR(20), Result INT)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('1', 'Denver', 'AMP', 1)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('1', 'Denver', 'TET', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('2', 'Denver', 'SPT', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('3', 'New York', 'AMP', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('3', 'New York', 'SPT', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'AMP', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'TET', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'STR', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('5', 'New York', 'AMP', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('5', 'New York', 'STR', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'TET', 2)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'SPT', 1)
INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'STR', 2)
Now, I need to make a report that looks like this :
I appreciate for any help.
If you're determined to do that in T-SQL, then I'm sure that you'll get some help. But you'd be using the wrong tool for the job. What you should be using is SSRS.
--EDIT--Just so you know, this format would be easy: -
City AMP Number Of Test AMP Number Of Resistants TET Number Of Test TET Number Of Resistants SPT Number Of Test SPT Number Of Resistants STR Number Of Test STR Number Of Resistants---------- ------------------ ------------------------ ------------------ ------------------------ ------------------ ------------------------ ------------------ ------------------------
Boston 1 1 1 1 0 0 1 1
Denver 1 0 2 2 2 1 1 1
New York 2 2 0 0 1 1 1 1
You'd just do it like this: -
SELECT City,SUM(CASE WHEN AntiBiotic = 'AMP' THEN 1 ELSE 0 END) AS [AMP Number Of Test],
SUM(CASE WHEN AntiBiotic = 'AMP' AND Result = 2 THEN 1 ELSE 0 END) AS [AMP Number Of Resistants],
SUM(CASE WHEN AntiBiotic = 'TET' THEN 1 ELSE 0 END) AS [TET Number Of Test],
SUM(CASE WHEN AntiBiotic = 'TET' AND Result = 2 THEN 1 ELSE 0 END) AS [TET Number Of Resistants],
SUM(CASE WHEN AntiBiotic = 'SPT' THEN 1 ELSE 0 END) AS [SPT Number Of Test],
SUM(CASE WHEN AntiBiotic = 'SPT' AND Result = 2 THEN 1 ELSE 0 END) AS [SPT Number Of Resistants],
SUM(CASE WHEN AntiBiotic = 'STR' THEN 1 ELSE 0 END) AS [STR Number Of Test],
SUM(CASE WHEN AntiBiotic = 'STR' AND Result = 2 THEN 1 ELSE 0 END) AS [STR Number Of Resistants]
FROM @Test_TBL
GROUP BY City;
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I guess you will need something more like that:
SELECT AntiBiotic
,COUNT(CASE City WHEN 'Boston' THEN 1 ELSE NULL END) as [Boston No. Of Tests]
,COUNT(CASE City WHEN 'Boston' THEN NULLIF(Result,1) ELSE NULL END ) as [Boston No. Of Resistants]
,COUNT(CASE City WHEN 'Denver' THEN 1 ELSE NULL END) as [Denver No. Of Tests]
,COUNT(CASE City WHEN 'Denver' THEN NULLIF(Result,1) ELSE NULL END ) as [Denver No. Of Resistants]
,COUNT(CASE City WHEN 'New York' THEN 1 ELSE NULL END) as [New York No. Of Tests]
,COUNT(CASE City WHEN 'New York' THEN NULLIF(Result,1) ELSE NULL END ) as [New York No. Of Resistants]
,COUNT(*) AS [Total No. Of Tests]
,COUNT(NULLIF(Result,1)) AS [Total No. Of Resistants]
FROM @Test_TBL
GROUP BY AntiBiotic
Then you nca build you report in SSRS, Crystal, Excel or whatever tool you're using for reporting
-
Dear Eugene,
This is exactly what I was looking for.
Thanks a lot.
-
Others have responded with good solutions. I have used both with good results, but want to point out that Crystal Reports can do this tidily. It has more built in features than SSRS and you'll get done faster. The case statement would require that you get into your code and add new categories as they are created.
PS if you use CASE be sure to create an ELSE to instruct users to contact you for modifications. (Maybe I missed that in the sample.)
-
SQLKnitter (2/28/2013)
Others have responded with good solutions. I have used both with good results, but want to point out that Crystal Reports can do this tidily. It has more built in features than SSRS and you'll get done faster. The case statement would require that you get into your code and add new categories as they are created.PS if you use CASE be sure to create an ELSE to instruct users to contact you for modifications. (Maybe I missed that in the sample.)
Actually, quite often in a RAD environment, no one will wait for good looking Crystal or SSRS report. Just something in Excel or directly in email, formatted just enough for understanding will do good enough.
Actually, this code is easily can be changed to dynamic SQL which will support unknown number of groups (cities).
-
Dear SQLKnitter,
I appreciate for advice. The reason I stay with SELECT is because I use it as SP to fill a datagridview in my application.
Thanks again
-
RZ52 (2/28/2013)
Dear SQLKnitter,I appreciate for advice. The reason I stay with SELECT is because I use it as SP to fill a datagridview in my application.
Thanks again
Yeap, it's another good reason...
Actually, here is dynamic SQL which doen't care how many cities are in the table,
(Please note, table variable would not work for this, so changed it to #table):
SET NOCOUNT ON;
-- setup sample table
CREATE TABLE #Test_TBL (ID VARCHAR(3), City VARCHAR(10),AntiBiotic VARCHAR(20), Result INT);
INSERT #Test_TBL(ID,City,Antibiotic, Result)
VALUES('1', 'Denver', 'AMP', 1),('1', 'Denver', 'TET', 2),('2', 'Denver', 'SPT', 2)
,('3', 'New York', 'AMP', 2),('3', 'New York', 'SPT', 2)
,('4', 'Boston', 'AMP', 2),('4', 'Boston', 'TET', 2),('4', 'Boston', 'STR', 2)
,('5', 'New York', 'AMP', 2),('5', 'New York', 'STR', 2)
,('6', 'Denver', 'TET', 2),('6', 'Denver', 'SPT', 1),('6', 'Denver', 'STR', 2);
-- Get required output
DECLARE @SQL NVARCHAR(4000) = 'SELECT AntiBiotic';
SELECT @SQL = @SQL + '
,COUNT(CASE City WHEN ''' + City + ''' THEN 1 ELSE NULL END) as [' + City + ' No. Of Tests]
,COUNT(CASE City WHEN ''' + City + ''' THEN NULLIF(Result,1) ELSE NULL END ) as [' + City + ' No. Of Resistants]'
FROM (SELECT DISTINCT City FROM #Test_TBL) Q;
SELECT @SQL = @SQL + '
,COUNT(*) AS [Total No. Of Tests]
,COUNT(NULLIF(Result,1)) AS [Total No. Of Resistants]
FROM #Test_TBL
GROUP BY AntiBiotic';
EXEC (@SQL);
-
Makes sense.
-
Dear Eugene,
You've completed my day.
Thanks a lot.
-
RZ52 (2/28/2013)
Dear Eugene,You've completed my day.
Thanks a lot.
You are welcome!
-
Eugene Elutin (2/28/2013)
RZ52 (2/28/2013)
Dear SQLKnitter,I appreciate for advice. The reason I stay with SELECT is because I use it as SP to fill a datagridview in my application.
Thanks again
Yeap, it's another good reason...
Actually, here is dynamic SQL which doen't care how many cities are in the table,
(Please note, table variable would not work for this, so changed it to #table):
SET NOCOUNT ON;
-- setup sample table
CREATE TABLE #Test_TBL (ID VARCHAR(3), City VARCHAR(10),AntiBiotic VARCHAR(20), Result INT);
INSERT #Test_TBL(ID,City,Antibiotic, Result)
VALUES('1', 'Denver', 'AMP', 1),('1', 'Denver', 'TET', 2),('2', 'Denver', 'SPT', 2)
,('3', 'New York', 'AMP', 2),('3', 'New York', 'SPT', 2)
,('4', 'Boston', 'AMP', 2),('4', 'Boston', 'TET', 2),('4', 'Boston', 'STR', 2)
,('5', 'New York', 'AMP', 2),('5', 'New York', 'STR', 2)
,('6', 'Denver', 'TET', 2),('6', 'Denver', 'SPT', 1),('6', 'Denver', 'STR', 2);
-- Get required output
DECLARE @SQL NVARCHAR(4000) = 'SELECT AntiBiotic';
SELECT @SQL = @SQL + '
,COUNT(CASE City WHEN ''' + City + ''' THEN 1 ELSE NULL END) as [' + City + ' No. Of Tests]
,COUNT(CASE City WHEN ''' + City + ''' THEN NULLIF(Result,1) ELSE NULL END ) as [' + City + ' No. Of Resistants]'
FROM (SELECT DISTINCT City FROM #Test_TBL) Q;
SELECT @SQL = @SQL + '
,COUNT(*) AS [Total No. Of Tests]
,COUNT(NULLIF(Result,1)) AS [Total No. Of Resistants]
FROM #Test_TBL
GROUP BY AntiBiotic';
EXEC (@SQL);
Dear Eugene,
I checked back my database and I realized for some antibiotics we have three possibilities "1" for "Sensitive", "2" for "Resistant" and "3" for "Intermediate". Based on current solution you proposed this part
NULLIF(Result,1)
will take out only "Sensitives" and in some cases the command returns wrong value because the "Resistant" value is actually both "Resistant" and "Intermediate".
How could I solve this problem ?
Thanks in advance and sorry to ask again.
-
replace:
NULLIF(Result,1)
with:
CASE WHEN Result = 2 THEN Result ELSE NULL END
-
Eugene Elutin (3/1/2013)
replace:NULLIF(Result,1)
with:
CASE WHEN Result = 2 THEN Result ELSE NULL END
Dear Eugene,
You solved my puzzle.
Thanks a lot.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply