Thursday, March 28, 2013

[T-SQL] Combine multiple combinations to one

[T-SQL] Combine multiple combinations to one


Combine multiple combinations to one

Posted: 28 Mar 2013 01:25 AM PDT

Hi, The following test condition :[code="sql"]IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGINDROP TABLE #testEnvironment;END;CREATE TABLE #testEnvironment ([Sample_ID] INT, [Rep_ID] INT, [Result] VARCHAR(20))INSERT INTO #testEnvironment SELECT 1, 1, 'O152' UNION ALL SELECT 1, 2, 'O2' UNION ALLSELECT 1, 3, 'O157' UNION ALL SELECT 1, 4, 'O154' UNION ALLSELECT 2, 1, 'O5' UNION ALL SELECT 2, 2, 'Negative' UNION ALLSELECT 2, 3, 'O1' UNION ALL SELECT 3, 1, 'O157' UNION ALLSELECT 3, 2, 'O1' UNION ALL SELECT 3, 3, 'O1' UNION ALLSELECT 4, 1, 'O157' UNION ALL SELECT 4, 2, 'Negative' UNION ALLSELECT 4, 3, 'O2' UNION ALL SELECT 4, 4, 'O152' UNION ALL SELECT 5, 1, 'Negative' UNION ALLSELECT 5, 2, 'Negative' UNION ALL SELECT 5, 3, 'Negative' UNION ALLSELECT 6, 1, 'O154' UNION ALL SELECT 6, 2, 'O157' UNION ALL SELECT 6, 3, 'O152' UNION ALLSELECT 7, 1, 'Negative' UNION ALL SELECT 7, 2, 'O152' UNION ALL SELECT 7, 3, 'O157';WITH SampleData AS (SELECT [Sample_ID], ISNULL([Interim],[Profile]) AS [Profile]FROM (SELECT [Sample_ID], CAST(CASE WHEN MAX([Result]) = MIN([Result]) AND MAX([Result]) = 'Negative' THEN 'Negative' ELSE NULL END AS NVARCHAR(MAX)) FROM #testEnvironment GROUP BY [Sample_ID] )a([Sample_ID],[Interim])CROSS APPLY (SELECT ISNULL(STUFF((SELECT ';'+[Result] FROM #testEnvironment WHERE a.Sample_ID = Sample_ID AND CHARINDEX('O15',[Result]) > 0 FOR XML PATH(''), TYPE ).value('.','NVARCHAR(MAX)'),1,1,'') ,'Non-pathogen') )b([Profile]))SELECT [Profile], COUNT([Profile]) AS [Count]FROM SampleDataGROUP BY [Profile][/code]Returns :[code="plain"]Profile Count-------------------- -------Negative 1Non-pathogen 1O152;O157 1O152;O157;O154 1O154;O157;O152 1O157 1O157;O152 1[/code]As you see different variations of same combination are counted separately. How can I avoid it and have : [code="plain"]Profile Count-------------------- -------Negative 1Non-pathogen 1O152;O157 2O152;O157;O154 2O157 1[/code]Thanks in advance for any suggestion.

Just a random observation with 'WITH'

Posted: 27 Mar 2013 10:46 PM PDT

Following is my observation with 'WITH'I executed the below query[code="sql"]SELECT * FROM Sales.SalesOrderDetail (NOLOCK)[/code]Which run sucessfullynow i execute the below query[code="sql"]UPDATE Sales.SalesOrderDetail_BACKUP (ROWLOCK)SET UNITPRICE=1WHERE PRODUCTID=707[/code](I know the query does not make sense but just a trail)It gives me the error as [b]Incorrect syntax near '('[/b].If i change it to [code="sql"]UPDATE Sales.SalesOrderDetail_BACKUP WITH(ROWLOCK)SET UNITPRICE=1WHERE PRODUCTID=707[/code]It Works. Just out of curiosity i would like to know why is WITH not mandatory with (NOLOCK).

How do i line up the weeks in T-SQL ?

Posted: 27 Mar 2013 06:52 AM PDT

How do i line up the weeks ('Weeks-2013' and 'Weeks-2012') in T-SQL ?Below is sample data to test with:SELECT DISTINCT 'International' Campus,28 'Weeks-2013',0 'Weeks-2012',2 'Student-2013',0 'Student-2012',0 StudentPrevYrTotalUNIONSELECT DISTINCT 'International' Campus,32 'Weeks-2013',0 'Weeks-2012',1 'Student-2013',0 'Student-2012',0 StudentPrevYrTotalUNIONSELECT DISTINCT 'International' Campus,0 'Weeks-2013',32 'Weeks-2012',0 'Student-2013',1 'Student-2012',1 StudentPrevYrTotalUNIONSELECT DISTINCT 'International' Campus,29 'Weeks-2013',0 'Weeks-2012',6 'Student-2013',0 'Student-2012',0 StudentPrevYrTotalUNIONSELECT DISTINCT 'International' Campus,27 'Weeks-2013',0 'Weeks-2012',5 'Student-2013',0 'Student-2012',0 StudentPrevYrTotalUNIONSELECT DISTINCT 'International' Campus,24 'Weeks-2013',0 'Weeks-2012',4 'Student-2013',0 'Student-2012',0 StudentPrevYrTotalUNIONSELECT DISTINCT 'International' Campus,0 'Weeks-2013',24 'Weeks-2012',0 'Student-2013',3 'Student-2012',4 StudentPrevYrTotalORDER BYCampus,'Weeks-2013','Weeks-2012'The idea is to achieve following:Campus / 'Weeks-2013' / 'Weeks-2012' / 'Student-2013' / 'Student-2012'International / 24 / 24 / 4 / 3International / 27 / 0 / 5 / 0International / 28 / 0 / 2 / 0International / 29 / 0 / 6 / 0International / 32 / 32 / 1 / 124,27,28,29,32 are the week numbers.

Update Help..

Posted: 27 Mar 2013 03:47 PM PDT

Hi Guys, Need favor. Here is the e.g. I am using TABLEA.ID (as an e.g) in my SP.I am not sure how many SP I am using TABLEA.ID. What i want to update TABLEA.ID TO TABLEB.ID from ALL SPs in my database. Please let me know if my question is not clear. Any help would be great help.Thank You.

can't view all db_users when select * from sys.databaseprincipals

Posted: 27 Mar 2013 07:02 AM PDT

Per msdn, there is a permissions restrictions where,,,Permissions--------------------------------------------------------------------------------Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role. Since I don't know who the db_users area, I need a query that I can run to see ALL db_users? Anyone know?

how to show results in pivot in sql please?

Posted: 27 Mar 2013 07:44 AM PDT

Hello All, how to show results in pivot format,please assit me to get the desired output as attached--create table #MedMediaTemp1 (PatientJoinDate Date, VisitType varchar(25), Priority varchar(12), Roller int, Total int)insert into #MedMediaTemp1 select '04/11/2013','Emergency Visit','Priority 1',1,34unionselect '04/11/2013','Emergency Visit','Priority 2',1,21unionselect '04/11/2013','Emergency Visit','Priority 3',1,67unionselect '04/11/2013','Emergency Visit','Priority 2',2,9unionselect '04/11/2013','Emergency Visit','Priority 3',2,21unionselect '04/11/2013','Emergency Visit','Priority 1',2,18unionselect '04/11/2013','Appointment Visit','Priority 2',1,7unionselect '04/11/2013','Appointment Visit','Priority 1',1,9unionselect '04/11/2013','Appointment Visit','Priority 3',1,217unionselect '04/11/2013','Appointment Visit','Priority 3',2,16unionselect '04/11/2013','Appointment Visit','Priority 2',2,1unionselect '04/11/2013','Appointment Visit','Priority 1',2,22unionselect '03/14/2013','Emergency Visit','Priority 3',1,64unionselect '03/14/2013','Emergency Visit','Priority 2',1,27unionselect '03/14/2013','Emergency Visit','Priority 1',1,33unionselect '03/14/2013','Emergency Visit','Priority 3',2,68unionselect '03/14/2013','Emergency Visit','Priority 2',2,68unionselect '03/14/2013','Emergency Visit','Priority 1',2,43unionselect '03/14/2013','Appointment Visit','Priority 2',1,10unionselect '03/14/2013','Appointment Visit','Priority 3',1,17unionselect '03/14/2013','Appointment Visit','Priority 1',1,11unionselect '03/14/2013','Appointment Visit','Priority 1',2,14unionselect '03/14/2013','Appointment Visit','Priority 3',2,56unionselect '03/14/2013','Appointment Visit','Priority 2',2,30unionselect '02/11/2013','Emergency Visit','Priority 2',1,56unionselect '02/11/2013','Emergency Visit','Priority 3',1,69unionselect '02/11/2013','Emergency Visit','Priority 1',1,352unionselect '02/11/2013','Appointment Visit','Priority 1',1,10unionselect '02/11/2013','Appointment Visit','Priority 2',1,54unionselect '02/11/2013','Appointment Visit','Priority 3',1,175unionselect '01/12/2013','Emergency Visit','Priority 2',1,20unionselect '01/12/2013','Emergency Visit','Priority 3',1,389unionselect '01/12/2013','Emergency Visit','Priority 1',1,642unionselect '01/12/2013','Appointment Visit','Priority 1',1,76unionselect '01/12/2013','Appointment Visit','Priority 2',1,48unionselect '01/12/2013','Appointment Visit','Priority 3',1,98unionselect '12/24/2012','Emergency Visit','Priority 2',1,5unionselect '12/24/2012','Emergency Visit','Priority 3',1,74unionselect '12/24/2012','Emergency Visit','Priority 1',1,36unionselect '12/24/2012','Appointment Visit','Priority 1',1,10unionselect '12/24/2012','Appointment Visit','Priority 2',1,10unionselect '12/24/2012','Appointment Visit','Priority 3',1,201unionselect '11/11/2012','Emergency Visit','Priority 1',1,26unionselect '11/11/2012','Emergency Visit','Priority 3',1,58unionselect '11/11/2012','Emergency Visit','Priority 2',1,3unionselect '11/11/2012','Appointment Visit','Priority 3',1,129unionselect '11/11/2012','Appointment Visit','Priority 2',1,15unionselect '11/11/2012','Appointment Visit','Priority 1',1,9unionselect '10/12/2012','Emergency Visit','Priority 1',1,20unionselect '10/12/2012','Emergency Visit','Priority 3',1,383unionselect '10/12/2012','Emergency Visit','Priority 2',1,38unionselect '10/12/2012','Appointment Visit','Priority 3',1,104unionselect '10/12/2012','Appointment Visit','Priority 1',1,7unionselect '10/12/2012','Appointment Visit','Priority 2',1,10unionselect '09/13/2012','Emergency Visit','Priority 2',1,35unionselect '09/13/2012','Emergency Visit','Priority 3',1,64unionselect '09/13/2012','Emergency Visit','Priority 1',1,32unionselect '09/13/2012','Appointment Visit','Priority 1',1,86unionselect '09/13/2012','Appointment Visit','Priority 2',1,116unionselect '09/13/2012','Appointment Visit','Priority 3',1,156unionselect '08/11/2012','Emergency Visit','Priority 2',1,60unionselect '08/11/2012','Emergency Visit','Priority 1',1,3unionselect '08/11/2012','Emergency Visit','Priority 3',1,69unionselect '08/11/2012','Appointment Visit','Priority 2',1,61unionselect '08/11/2012','Appointment Visit','Priority 1',1,5unionselect '08/11/2012','Appointment Visit','Priority 3',1,100unionselect '07/20/2012','Emergency Visit','Priority 2',1,298unionselect '07/20/2012','Emergency Visit','Priority 3',1,175unionselect '07/20/2012','Emergency Visit','Priority 1',1,52unionselect '07/20/2012','Appointment Visit','Priority 3',1,64unionselect '07/20/2012','Appointment Visit','Priority 1',1,27unionselect '07/20/2012','Appointment Visit','Priority 2',1,49unionselect '06/10/2012','Emergency Visit','Priority 2',1,147unionselect '06/10/2012','Emergency Visit','Priority 3',1,618unionselect '06/10/2012','Emergency Visit','Priority 1',1,20unionselect '06/10/2012','Appointment Visit','Priority 2',1,12unionselect '06/10/2012','Appointment Visit','Priority 1',1,9unionselect '06/10/2012','Appointment Visit','Priority 3',1,88SELECT * FROM #MedMediaTemp1 from this table how to produce the below results through sql please assist me April 2013, Roller 1 Aprill 2013 Roler 2 April 2013 Total March 2013 Roller 1 March 2013 Roler 2 March 2013 TotalAppointment Visit - ALL 233 39 272 38 100 138Appointment Visit - Priority 1 9 22 31 11 14 25Appointment Visit - Priority 2 7 1 8 10 30 40Appointment Visit - Priority 3 217 16 233 17 56 73Emergency Visit - ALL 122 48 170 124 179 303Emergency Visit - Priority 1 34 18 52 33 43 76Emergency Visit - Priority 2 21 9 30 27 68 95Emergency Visit - Priority 3 67 21 88 64 68 132Thanks in advancedhani

How to concatenate group of rows

Posted: 27 Feb 2013 10:28 AM PST

Hi,I have a data structure as followed : [Name], [Task] and the data is :John, task1John, task2Joe, task1Joe, task3Joe, task4Jane, task1Jane, task4..........I wonder how to use the 'SELECT' to make the following result :John, task1;task2Joe, task1;task3;task4Jane, task1;task4.....Thanks for any help.

How to create a calendar table for 65 weeks (not the default of 53 weeks) ?

Posted: 27 Mar 2013 07:27 AM PDT

I have a cte script to create a calendar table for 53 weeks, starts at 2011-01-01 (week 1, Saturday) and ends at 2011-12-31 (week 53, Saturday).The average weeks starts on a Sunday (2011-01-02) and ends on a Saturday (2011-01-08). I use 'SELECT datepart(ww, [Date])' to generate the weeks, the problem is that datepart week only generates 53 weeks (since there is only 53 weeks in a year). Customer wants weeks from 01 January 2011 to 31 March 2012 (65 weeks).The calendar table i use spans over 15 yrs (2011-01-01 to 2011-12-31, 2012-01-01 to 2012-12-31, etc.)Below is the cte scriptdeclare @StartDate datetime = '2011-01-01',@EndDate datetime = '2012-01-01'; -- for testing purposes I only selected one yearwith Calendaras(SELECT cast(@StartDate as datetime) [Date]union allSELECT [Date] + 1 FROM Calendar WHERE [Date] + 1 < @EndDate)SELECT [Date],datepart(dy, [Date]) [day of year],datepart(dd, [Date]) [day of month],datepart(dw, [Date]) [day of week],datename(dw, [Date]) [dayname],datepart(ww, [Date]) [week] ,datepart(mm, [Date]) [month],datepart(yy, [Date]) [year]--into dbo.CalendarTable -- uncomment to populate CalendarTableFROM CalendarOPTION (MAXRECURSION 0)

SELECT FROM Multiple tables with names in a table

Posted: 27 Mar 2013 05:34 AM PDT

I am trying to do a query that I am not sure can be written without using a cursor and building dynamically. But if someone can help me figure out how to do it without having to use cursors that would be awesome.So I have a table called AppSystem.ApplianceTypes. It has a varchar column named ApplianceTypeTableName that contains the name of other tables in the form of schema.tablename. There are 71 rows of tablenames in the AppSystem.ApplianceTypes table. Each of the 71 tables have a column inside them called MFG. What I want to do is do a query that lists the MFG values in all 71 tables in the same query (like a UNION). By the way, the 71 rows in AppSystem.ApplianceTypes will grow in the future as we add new tables.If someone can figure this out, I will be praising them as a SQL King/Queen. :) If you don't think it can be done without cursors just let me know and I will figure that part out using the cursor.Thanks,David

No comments:

Post a Comment

Search This Blog