Saturday, May 4, 2013

[T-SQL] How to create a frequency report

[T-SQL] How to create a frequency report

How to create a frequency report

Posted: 03 May 2013 04:50 AM PDT

Hi, I have two tables containing test results in categorical scale.Table one contains PCR results for some factors (P1, P2, ....). The assigned values are (0='Not tested', 1='Positive', 2='Negative')[code="sql"]IF object_id('tempdb..#TestTb1') IS NOT NULLBEGINDROP TABLE #TestTb1ENDCREATE TABLE #TestTb1 ([Sample_ID] VARCHAR(10), [P1] INT, [P2] INT, [P3] INT, [P4] INT)INSERT INTO #TestTb1 SELECT 'Sample1', 1, 1, 0, 2 UNION ALLSELECT 'Sample2', 0, 0, 2, 1 UNION ALLSELECT 'Sample3', 1, 2, 0, 1 UNION ALLSELECT 'Sample4', 0, 1, 0, 1 UNION ALLSELECT 'Sample5', 1, 1, 1, 1 UNION ALLSELECT 'Sample6', 2, 1, 1, 2 UNION ALLSELECT 'Sample7', 1, 2, 1, 2 UNION ALLSELECT 'Sample8', 0, 2, 0, 1 UNION ALLSELECT 'Sample9', 1, 1, 0, 0 UNION ALLSELECT 'Sample10', 2, 2, 2, 1 UNION ALLSELECT 'Sample11', 1, 1, 0, 0 UNION ALLSELECT 'Sample12', 2, 1, 2, 0 [/code]Table two includes antibiotic resistance test results for different antibiotics (AB1, AB2, ....). The values are (0='Not tested', 1='Sensitive', 2='Intermediate', 3='Resistant')[code="sql"]IF object_id('tempdb..#TestTb2') IS NOT NULLBEGINDROP TABLE #TestTb2ENDCREATE TABLE #TestTb2 ([Sample_ID] VARCHAR(10), [AB1] INT, [AB2] INT, [AB3] INT, [AB4] INT)INSERT INTO #TestTb2SELECT 'Sample1', 3, 1, 0, 2 UNION ALLSELECT 'Sample2', 2, 1, 3, 3 UNION ALLSELECT 'Sample3', 3, 3, 3, 3 UNION ALLSELECT 'Sample4', 0, 2, 0, 3 UNION ALLSELECT 'Sample5', 1, 3, 1, 2 UNION ALLSELECT 'Sample6', 3, 1, 2, 3 UNION ALLSELECT 'Sample7', 1, 1, 1, 2 UNION ALLSELECT 'Sample8', 3, 2, 0, 1 UNION ALLSELECT 'Sample9', 1, 1, 0, 3 UNION ALLSELECT 'Sample10', 2, 3, 2, 1 UNION ALLSELECT 'Sample11', 3, 1, 0, 2 UNION ALLSELECT 'Sample12', 2, 1, 3, 0 [/code]Now, I need to build a report to show the frequency of positive (=1) PCR factors (P1,P2,...) for each antibiotic (AB1,AB2, ....) when the sample is resistant (=3) for the antibiotic. The result report supposes to be like this :[code="plain"]Antibiotic Factor Freq. (=3) (Factor=1) ---------- ------- -------------AB1 P1 3AB1 P2 3AB1 P3 1AB1 P4 2AB2 P1 2AB2 P2 1AB2 P3 1AB2 P4 3AB3 P1 1AB3 P2 1AB3 P3 0AB3 P4 2AB4 P1 2AB4 P2 3AB4 P3 1AB4 P4 3[/code]As we have around 20 antibiotics, 20 PCR factors and thousands of samples, any suggestions will be a great help and will be appreciated.Thanks in advance.

HOW TO PASS UPDATE QUERY WITH EXECUTE COMMAND

Posted: 03 May 2013 10:36 AM PDT

HI,I have small confusion send the "update" query in execute statement.My requirement is:Without using either variable or set quoted_identifier offhow can i sent the query through procedure.my usage as below. its not working for two queries below.Exec proce_Name[b] 'update table set column='TEST' where id=1'[/b]or Exec proce_Name[b] "update table set column='TEST' where id=1"[/b]-------Giri

Multiple reference to a single column

Posted: 03 May 2013 09:14 PM PDT

I have created 3 table in which in T3 table I am referencing the ID columns of table T1 and T2.I have created two separate Foreign Key constraints on ID3 column but its not working.Please find the info below:create table t1( ID int primary key identity(1,1), Name varchar(100))Create table T2( ID2 int primary key identity(1,1), Name2 varchar(100))Insert into T1 values('A'),('B'),('E'),('G')INERT INTO T2 values ('L'),('K'),('M')Create table t3( ID3 int, Name3 varchar(100))alter table t3ADD constraint FK_ID4 foreign key (ID3) references t1(ID)alter table t3ADD constraint FK_ID3 foreign key (ID3) references t2(ID2)Insert into t3 values (4,'G')Error:The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ID3". The conflict occurred in database "dbTester", table "dbo.T2", column 'ID2'.

select the output of stored procedure with count of returned rows

Posted: 03 May 2013 05:52 PM PDT

I want to select the output of stored procedure and the count of the row affectedSo am trying to do something like that [code="other"]select * , count(*) (if @checker is not null begin exec [dbo].[GetEmployees] @parameter1, @parameter2 end else begin exec [dbo].[GetEmployees] @parameter3, @parameter4 end)T[/code]I always get error like this Incorrect syntax near the keyword 'if'.Incorrect syntax near ')'.

T-SQL help

Posted: 03 May 2013 09:06 AM PDT

For the below SQL code, I need the output as:[b]person dob Age[/b]Frank 3/20/1990 23 YearsJoey 12/31/2000 12 YearsSue 2/6/2012 1 YearsMary 4/1/2013 1 MonthsBil 5/3/2012 1 YearsBob 1/1/2013 4 MonthsWill 10/1/2012 7 Months[code="sql"]declare @people as table ( person varchar(20), dob date) declare @SDate as date = '5/3/2013';insert into @people(person,dob)values ('Frank','3/20/1990'), ('Joey','12/31/2000'), ('Sue','2/6/2012'), ('Mary','4/1/2013'), ('Bil','5/3/2012'), ('Bob','1/1/2013'), ('Will','10/1/2012') SELECT person, dob, DATEDIFF(YEAR, dob, @SDate) - CASE WHEN((MONTH(dob)*100 + DAY(dob)) > (MONTH(@SDate)*100 + DAY(@SDate))) THEN 1 ELSE 0 END ageFROM @people [/code]

Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries

Posted: 03 May 2013 06:36 AM PDT

Is it possible to use a stored procedure to kill all queries running longer than 2 hours AND are blocking? Couls someone help me figure out how to do this?

Unpivot Multiple Columns

Posted: 03 May 2013 05:13 AM PDT

This is my first shot at an trying to unpivot multiple columns, and I'm not getting any results.I have a table that has an id, and then 20 columns, 10 values and 10 exp dates. I want to unpivot it and create a table with rows of 3 columns each row (pk_value, date, exp value).here's the code to recreate the unpivot. Unpivoting by one column works fine, when I try to unpivot by the 2nd column and then include the where clause, i don't get any records returned.[code="sql"]USE tempdbGOCREATE TABLE [dbo].[ERFiles]( [pk_value] [int] NOT NULL, [ER_1] [decimal](14, 7) NULL, [ER_1_ExpDate] [date] NULL, [ER_2] [decimal](14, 7) NULL, [ER_2_ExpDate] [date] NULL, [ER_3] [decimal](14, 7) NULL, [ER_3_ExpDate] [date] NULL, [ER_4] [decimal](14, 7) NULL, [ER_4_ExpDate] [date] NULL, [ER_5] [decimal](14, 7) NULL, [ER_5_ExpDate] [date] NULL, [ER_6] [decimal](14, 7) NULL, [ER_6_ExpDate] [date] NULL, [ER_7] [decimal](14, 7) NULL, [ER_7_ExpDate] [date] NULL, [ER_8] [decimal](14, 7) NULL, [ER_8_ExpDate] [date] NULL, [ER_9] [decimal](14, 7) NULL, [ER_9_ExpDate] [date] NULL, [ER_10] [decimal](14, 7) NULL, [ER_10_ExpDate] [date] NULL,)INSERT INTO [dbo].[ERFiles]([pk_value], [ER_1], [ER_1_ExpDate], [ER_2], [ER_2_ExpDate], [ER_3], [ER_3_ExpDate], [ER_4], [ER_4_ExpDate], [ER_5], [ER_5_ExpDate], [ER_6], [ER_6_ExpDate], [ER_7], [ER_7_ExpDate], [ER_8], [ER_8_ExpDate], [ER_9], [ER_9_ExpDate], [ER_10], [ER_10_ExpDate])SELECT 1, 40.3399000, '20051231 00:00:00.000', 40.3399000, '20101231 00:00:00.000', 40.3399000, '20111231 00:00:00.000', 40.3399000, '20121231 00:00:00.000', 40.3399000, '20131231 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 2, 18.3705000, '19950930 00:00:00.000', 18.3705000, '19951231 00:00:00.000', 18.3705000, '20010101 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 3, 1.2355000, '20120930 00:00:00.000', 1.2672000, '20121031 00:00:00.000', 1.2807000, '20121130 00:00:00.000', 1.2774000, '20121231 00:00:00.000', 1.3126000, '20130131 00:00:00.000', 1.3222000, '20130228 00:00:00.000', 1.3567000, '20130331 00:00:00.000', 1.3271000, '20130430 00:00:00.000', 1.3469000, '20130531 00:00:00.000', 0.0000000, NULL UNION ALLSELECT 4, 1.2041000, '20121231 00:00:00.000', 1.2096000, '20130131 00:00:00.000', 1.2385000, '20130228 00:00:00.000', 1.2347000, '20130331 00:00:00.000', 1.2222000, '20130430 00:00:00.000', 1.2149000, '20130531 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 5, 1.3440000, '20090831 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 6, 1.9558300, '20051231 00:00:00.000', 1.9558300, '20101231 00:00:00.000', 1.9558300, '20111231 00:00:00.000', 1.9558300, '20121231 00:00:00.000', 1.9558300, '20131231 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 7, 0.8149900, '19950930 00:00:00.000', 0.8149900, '19951231 00:00:00.000', 0.8149900, '20010101 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 8, 7.4574000, '20121231 00:00:00.000', 7.4608000, '20130131 00:00:00.000', 7.4629000, '20130228 00:00:00.000', 7.4604000, '20130331 00:00:00.000', 7.4532000, '20130430 00:00:00.000', 7.4562000, '20130531 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 9, 3.5310730, '19950930 00:00:00.000', 3.5310730, '19951231 00:00:00.000', 3.5310730, '20010101 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL UNION ALLSELECT 10, 0.1400000, '20100630 00:00:00.000', 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULL, 0.0000000, NULLSELECT pk_value ,ERValue ,ERExpDateFROM ( SELECT [pk_value] ,[ER_1] ,[ER_1_ExpDate] ,[ER_2] ,[ER_2_ExpDate] ,[ER_3] ,[ER_3_ExpDate] ,[ER_4] ,[ER_4_ExpDate] ,[ER_5] ,[ER_5_ExpDate] ,[ER_6] ,[ER_6_ExpDate] ,[ER_7] ,[ER_7_ExpDate] ,[ER_8] ,[ER_8_ExpDate] ,[ER_9] ,[ER_9_ExpDate] ,[ER_10] ,[ER_10_ExpDate] FROM [dbo].[ERFiles] ) M UNPIVOT ( ERExpDate FOR ERExpDates IN ( ER_1_ExpDate, ER_2_ExpDate, ER_3_ExpDate, ER_4_ExpDate, ER_5_ExpDate, ER_6_ExpDate, ER_7_ExpDate, ER_8_ExpDate, ER_9_ExpDate, ER_10_ExpDate ) ) U1 UNPIVOT ( ERValue FOR ERValues IN ( ER_1, ER_2, ER_3, ER_4, ER_5, ER_6, ER_7, ER_8, ER_9, ER_10 ) ) U2WHERE RIGHT(ERExpDates, 1) = RIGHT(ERValues, 1); [/code]It's probably something simple, but I not seeing it. Any ideas?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog