Tuesday, May 7, 2013

[T-SQL] GROUP BY question

[T-SQL] GROUP BY question

GROUP BY question

Posted: 06 May 2013 07:55 PM PDT

Morning all,Could someone help me generate the following grouping please? Sample data plus expected output below:Input Table: [b]SiteMatches[/b]Expected Output: [b]SiteMatches_Output[/b][img]http://i.imgur.com/7FGfQPu.jpg[/img][code="sql"]CREATE TABLE dbo.SiteMatches (RetainedRID BIGINT, DroppedRidd BIGINT, Country VARCHAR(100))INSERT INTO dbo.SiteMatchesSELECT 137478, 87728738, 'UK' UNION ALLSELECT 137478, 95615, 'UK' UNION ALLSELECT 851566, 447422, 'SCOTLAND' UNION ALLSELECT 851566, 188827, 'SCOTLAND' UNION ALLSELECT 851566, 326887, 'SCOTLAND' CREATE TABLE dbo.SiteMatches_Output (ID BIGINT, SiteID BIGINT, Country VARCHAR(30))INSERT INTO SiteMatches_OutputSELECT 137478, 1, 'UK' UNION ALLSELECT 87728738, 1, 'UK' UNION ALLSELECT 95615, 1, 'UK' UNION ALLSELECT 851566, 2, 'SCOTLAND' UNION ALLSELECT 447422, 2, 'SCOTLAND' UNION ALLSELECT 188827, 2, 'SCOTLAND' UNION ALLSELECT 326887, 2, 'SCOTLAND'select * from SiteMatchesselect * from SiteMatches_Outputdrop table dbo.SiteMatchesdrop table dbo.SiteMatches_Output[/code]

Tricky Merge Row Question

Posted: 06 May 2013 12:26 PM PDT

Hi all SQL gurus...I need help in creating a query without any funtion to do merge owner's names from multiple row for a property, however when it has the same surname, then it should only show the surname once.as the table is quite big, I think it's best to use result set instead of running function for each property to get the owner's name.I've used the for xml path to join it, but doesn't remove the multiple username.e.g. "Mr TK Wicki & Mrs NE Wicki" instead of "Mr TK & Mrs NE Wicki"Sample data as below:declare @temp table (propkey varchar(15), Title varchar(10), Initial varchar(10), Surname varchar(50))insert into @temp (propkey, title, initial, surname)values ('298213','Mr','M','McArthur'), ('298213','Mrs','D','McArthur'),('301869','Mr','TK','Wicki'),('301869','Mrs','NE','Wicki'),('309048','Mr','RG','Thompson'),('309048','Mr','DJ','Thompson'),('309048','Ms','CJ','Cain'),('309048','Ms','AJ','Cain'),('357308','Mr','JD','Homer'),('357308','Mrs','PG','Homer'),('357308','Ms','ML','Homer'),('378699','Mr','VB','Prince'),('378699','Mrs','KV','Prince')select * from @tempResults needed: '298213','Mr M & Mrs D McArthur''301869','Mr TK & Mrs NE Wicki''309048','Mr RG & Mr DJ Thompson & Ms CJ & Ms AJ Cain ''357308','Mr JD & Mrs PG & Ms ML Homer''378699','Mr VB & Mrs KV Prince'

Transforming an Interative Solution into a Set-Based Solution

Posted: 06 May 2013 04:51 AM PDT

The problem here is that the grpID field is sometimes loaded with multiple values for a single element of the set. The goal of this program is to split those incidences into separate rows for each individual value (whenever an incidence of multiple GroupId values in a single element occurs, there is exactly 10 characters in between then), then additionally eliminate an unnecessary prefix modifer (either "." or "-"). This iterative based solution works, but obviously it is clearly non-standard and will not scale well once put into production. I was hoping someone with experience might be able to point me in the right direction as to how this solution can be transformed into a set-based approach. Thanks.[code="sql"]USE OPMSupportStage;GO DECLARE @counterValue INTDECLARE @numberOfRecords INTDECLARE @subStrStart INTDECLARE @grpID VARCHAR(30)DECLARE @x CHAR(1)SELECT @numberOfRecords = COUNT(*) FROM dbo.ActiveSET @counterValue = 1;DELETEFROM dbo.ActiveFinal;DBCC CHECKIDENT ('dbo.ActiveFinal', RESEED, 1);WHILE @counterValue <= @numberOfRecords + 25 BEGIN SELECT @grpID = LEN(d.MAGrp) FROM dbo.Active As D WHERE d.P_Id = @counterValue SET @subStrStart = 1; WHILE @subStrStart < @grpID BEGIN INSERT INTO dbo.ActiveFinal(GrpName2, MAGrp2, MOSGrp2, EffectiveDt2) SELECT e.GrpName, SUBSTRING(e.MAGrp, @subStrStart, 9), e.MOSGrp, e.EffectiveDt FROM dbo.Active As E WHERE e.P_Id = @counterValue SET @subStrStart = @subStrStart + 10; END SELECT @x = SUBSTRING(MAGrp2, 6, 1) FROM dbo.ActiveFinal WHERE P_Id = @counterValue IF @x = '-' BEGIN UPDATE dbo.ActiveFinal SET MAGrp2 = REPLACE(MaGrp2,'-','') WHERE P_Id = @counterValue END ELSE IF @x = '.' BEGIN UPDATE dbo.ActiveFinal SET MAGrp2 = REPLACE(MaGrp2,'.','') WHERE P_Id = @counterValue END SET @counterValue = @counterValue + 1; ENDGO[/code]

How to Insert/Upadte data based on exist in mathcin table, in single query

Posted: 06 May 2013 06:53 AM PDT

Hello All,please help me with this single select statement,[u]StudentNew[/u] table is exist with below values, (sno and sname is composite primary key)Sno,Sname,LastGameDate,DateofBirth,Points[u]StudentOffers[/u] is the table exist with (sno is primary key)Sno,Sname,DoB,City,Fee,Stateso in [u]studentofferes[/u] is the warehouse table, it has 100K data and it get adds/updates every day,now my question is, i have a new table StudentNew, it has some data in it (about 5k)i would like to insert/update to the studentNew table based on SNO and SNAME columns matchIf [b]no Sno, Sname [/b]matching record in Student Name table, then if Fee colum > 0 insert into studentNew Table (sno,sname,current week friday date,dob,fee) else insert into studentNew Table (sno,sname,null,dob,null)if[b] matching record found [/b](sno, sname exist) in StudentNew table then check LastGameDate column has value and +7 weeks to that value is > today then if points column is null or 0 and Fee from studentOffers is > 0 then update points column with fee column, and update lastgamedate column to current week friday date if points column value is already > 0 and points < Fee column from studentoffere then update points with fee column check LastGameDate column has value and +7 weeks to that value is < today then no actionhow to write this statement in more optimized manner please help me,i do have step by step approach (in procedure., step by step checking)please kindly help me, how to write in very efficient wayKind Regards, thanks a ton in advancedhanvi

Get windows user..

Posted: 06 May 2013 02:44 AM PDT

Hi,Is there a way of getting the windows user name using an application accessing SQL Server with SQL Authentication?The sysprocesses has the hostprocess id but not the windows user that opened it...With a CLR is possible to write code to get a process id user, but is this also possible with "simple" T-SQL?Thanks,Pedro
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog