[T-SQL] Calculate percentage

---

• Calculate percentage
• Custom sp_who
• how create sql str with special condition

Calculate percentage Posted: 22 Mar 2013 11:59 AM PDT Hi, I need to make a report from a table contains following data (I made a simple structure as the original table includes many columns for other information) :[code="sql"]DECLARE @Test_TBL TABLE (Sample_ID VARCHAR(3),AntiBiotic VARCHAR(20), Result INT)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('1', 'AMP', 1)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('1', 'TET', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('2', 'SPT', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('3', 'AMP', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('3', 'SPT', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('4', 'AMP', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('4', 'TET', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('4', 'STR', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('5', 'AMP', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('5', 'STR', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('6', 'TET', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('6', 'SPT', 1)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('6', 'STR', 2)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('7', '', 0)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('8', '', 0)INSERT INTO @Test_TBL(Sample_ID,Antibiotic, Result) VALUES('9', '', 0)[/code]In this table "Result" column shows the result of antibiotic resistance test (0=not tested, 1=non-resistant, 2=resistant).I need to have report like :[code="plain"]Antibiotic No. of tests No. of resistant sample % of resistant------------ ------------- ---------------------------- ----------------AMP 4 3 75.0.......[/code]Please notice that not all antibiotics are tested for all samples.I appreciate for helps.

Custom sp_who Posted: 15 Mar 2013 08:09 AM PDT Hello All,Couldn't find anything that addresses this specifically, so here goes.I have a group that I want to allow to view process on their dev server, but I don't want to grant view server state permissions and all that it exposes, so I'm trying to write a proc that will execute sp_who as me (the DBA) for users, but it still only returns one row instaed of all of them; what am I missing? MS's docs say this should work.USE [master]GO/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================-- Author: -- Created: 03/14/13-- Desc: sp_who for users-- =============================ALTER PROCEDURE [usp_who]with execute as selfASBEGIN SET NOCOUNT ON; exec sp_who;END

how create sql str with special condition Posted: 22 Mar 2013 04:06 AM PDT create sql str with special conditionhellowI have 2 tableTable a(code, price, check)Table b(code, percent)I want to create a sql string such below:Select a.code, a.price, (price * percent) as new_fieldFrom a,b Where a.code = b.codeAnd this condition must be consider in sql str:If (check = 1){ New_field = price * percent}Else{ New_field = price * percent * 8}Now how can put above condition in sql str?Thanks very much

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610