Tuesday, May 21, 2013

[T-SQL] List out rows from a duplicate record set that has one or more odd values

[T-SQL] List out rows from a duplicate record set that has one or more odd values

List out rows from a duplicate record set that has one or more odd values

Posted: 20 May 2013 08:44 AM PDT

I have a table with data that looks as below...[img]http://www.sqlservercentral.com/Forums/Attachment13718.aspx[/img]Each CaseID has multiple records in the table. Each of these rows is expected to have same data in all columns except "CreatedDate". However, in certain cases the StudentID may be different from the other row(s) of the same CaseID. I need to report data for all such cases. Additionally, the output needs to be arranged in the order of caseID with highest created date. i.e., in the result set all records belonging to the CaseID which was modified most recently should appear at the top.Here is the sample of how the desired result set should look like...[img]http://www.sqlservercentral.com/Forums/Attachment13719.aspx[/img]Following is the code snippet to create sample data[code="sql"]DECLARE @mySampleTable TABLE ( CaseID INT, StudentID VARCHAR (20), StudentSocial VARCHAR (11), StudentName VARCHAR (50), CreatedDate DateTime2 )INSERT @mySampleTable (CaseID, StudentID, StudentSocial, StudentName, CreatedDate) VALUES (1001, 'FTS-2134', '111-22-3333', 'Roger Federer', '5/12/2013'), (1001, 'FTS-2314', '111-22-3333', 'Roger Federer', '5/08/2013'), (1002, 'PTS-5769', '222-33-4444', 'Maria Sharapova', '5/14/2013'), (1002, 'PTS-5769', '222-33-4444', 'Maria Sharapova', '5/2/2013'), (1003, 'FTS-8764', '333-44-5555', 'Serena Williams', '5/17/2013'), (1003, 'FTS-8764', '333-44-5555', 'Serena Williams', '5/06/2013'), (1003, 'FTS-7864', '333-44-5555', 'Serena Williams', '4/30/2013'), (1004, 'FTS-9132', '444-55-6666', 'Novak Djokovic', '5/10/2013'), (1004, 'FTS-9312', '444-55-6666', 'Novak Djokovic', '4/27/2013'), (1005, 'PTS-4722', '555-66-7777', 'Novak Djokovic', '5/19/2013'), (1005, 'PTS-4722', '555-66-7777', 'Novak Djokovic', '5/12/2013')select * from @mySampleTable[/code]For now, this is what I did...[code]SELECT DISTINCT a.CaseID, a.StudentID, b.StudentID, a.StudentSocial, a.StudentName, a.CreatedDateFROM @mySampleTable aINNER JOIN @mySampleTable b ON a.CaseID = b.CaseID AND a.StudentSocial = b.StudentSocial AND a.StudentID <> b.StudentID[/code]It doesn't do the sorting, but seems to do the rest. I am not sure if this is the best way to achieve what I am looking for though. My actual production tables have fairly large amount of data. I would appreciate your help and inputs with this.Thanks in advance...- Rex

Dynamic ranking

Posted: 20 May 2013 09:19 AM PDT

Thanks in advance if you can offer any assistance.I have a table of ranked cities-#CitiesI have a table called #CityGroups with a RankStart and RankEnd field. Example, GroupA should contain the top 3 ranked cities.I have a table #CityGroupmembers which I periodically need to refresh as the data in table #Cities is updated.This would be the classic MERGE scenario but table #CityGroupmembers is accessed through a linked server, so I need to generate an insert and a delete statement based on existence checks, that is I can not delete all members of a group and then repopulate in its entirety.I would like to include ties if there are ties in table #Cities, not shown in my sample data.I guess the rub (at least for me) is how do I extract the rankings from table #CityGroups and use them in my insert and delete statements.I would strongly prefer to not use dynamic SQL. [code]CREATE TABLE #Cities( City varchar (20), CityRank int)INSERT INTO #CitiesSELECT 'Paris',1 UNIONSELECT 'Chicago',2 UNIONSELECT 'Seattle',3 UNIONSELECT 'Hong Kong',4 UNIONSELECT 'New York',5 UNIONSELECT 'Brasilia',6 UNIONSELECT 'Tangier',7 UNIONSELECT 'Berlin',8 UNIONSELECT 'Tokyo',9 UNIONSELECT 'Mexico City',10 CREATE TABLE #CityGroups( CityGroup varchar(10), CityDescription varchar(15), RankStart int, RankEnd int)INSERT INTO #CityGroups SELECT 'GroupA','Top 3 Cities',1,3 UNIONSELECT 'GroupB','Top 10 Cities',1,10 UNIONSELECT 'GroupC','Middle Eight',2,9 CREATE TABLE #CityGroupmembers( CityGroup varchar(10), City varchar (20))--The inserts I would be trying to generate--These are obviously not getting the rankings from the #CityGroups table in any dynamic fashionINSERT INTO #CityGroupmembersSELECT TOP 3 'GroupA', City FROM #Cities ORDER BY CityRank INSERT INTO #CityGroupmembersSELECT TOP 10 'GroupB', City FROM #Cities ORDER BY CityRankINSERT INTO #CityGroupmembersSELECT 'GroupC', CityFROM #CitiesWHERE CityRank BETWEEN 2 AND 9 SELECT * FROM #Cities ORDER BY CityRankSELECT * FROM #CityGroupsSELECT * FROM #CityGroupmembersDROP TABLE #CitiesDROP TABLE #CityGroupsDROP TABLE #CityGroupmembers--Updated list for testing/*INSERT INTO #CitiesSELECT 'Montreal',1 UNIONSELECT 'Chicago',2 UNIONSELECT 'Mexico City',3 UNIONSELECT 'San Diego',4 UNIONSELECT 'New York',5 UNIONSELECT 'Brasilia',6 UNIONSELECT 'Paris',7 UNIONSELECT 'Berlin',8 UNIONSELECT 'Tokyo',9 UNIONSELECT 'Hong Kong',10 */[/code]

Very difficult query, not sure how to handle without a cursor.

Posted: 20 May 2013 07:24 AM PDT

I have a scenario here I have been wrestling over off and on for weeks.We have to tables, one lists Classes, and one lists Entry Codes for certain classes.[code="sql"]DECLARE @Class TABLE (ClassID char(8), CourseID varchar(10), ClassCapacity int)INSERT INTO @Class (ClassID, CourseID, ClassCapacity)SELECT '3520B014','ENGL&101',25UNION ALLSELECT '3525B014','ENGL&101',25UNION ALLSELECT '3530B014','ENGL&101',25DECLARE @EntryCode TABLE (EntryCodeID varchar(9), CourseID varchar(10))INSERT INTO @EntryCode (EntryCodeID, CourseID)SELECT '10515B014','ENGL&101'UNION ALLSELECT '10594B014','ENGL&101'UNION ALLSELECT '10625B014','ENGL&101'UNION ALLSELECT '10727B014','ENGL&101'UNION ALLSELECT '10736B014','ENGL&101'UNION ALLSELECT '10790B014','ENGL&101'UNION ALLSELECT '11092B014','ENGL&101'UNION ALLSELECT '11228B014','ENGL&101'UNION ALLSELECT '11627B014','ENGL&101'UNION ALLSELECT '11826B014','ENGL&101'UNION ALLSELECT '12562B014','ENGL&101'UNION ALLSELECT '12767B014','ENGL&101'UNION ALLSELECT '12777B014','ENGL&101'UNION ALLSELECT '12936B014','ENGL&101'UNION ALLSELECT '13229B014','ENGL&101'UNION ALLSELECT '13451B014','ENGL&101'UNION ALLSELECT '13478B014','ENGL&101'UNION ALLSELECT '13778B014','ENGL&101'UNION ALLSELECT '13907B014','ENGL&101'UNION ALLSELECT '13977B014','ENGL&101'UNION ALLSELECT '14004B014','ENGL&101'UNION ALLSELECT '14010B014','ENGL&101'UNION ALLSELECT '14134B014','ENGL&101'UNION ALLSELECT '14261B014','ENGL&101'UNION ALLSELECT '14273B014','ENGL&101'UNION ALLSELECT '14311B014','ENGL&101'UNION ALLSELECT '14463B014','ENGL&101'UNION ALLSELECT '15060B014','ENGL&101'UNION ALLSELECT '15741B014','ENGL&101'UNION ALLSELECT '15787B014','ENGL&101'UNION ALLSELECT '15892B014','ENGL&101'UNION ALLSELECT '16148B014','ENGL&101'UNION ALLSELECT '16374B014','ENGL&101'UNION ALLSELECT '16673B014','ENGL&101'UNION ALLSELECT '17067B014','ENGL&101'UNION ALLSELECT '17162B014','ENGL&101'UNION ALLSELECT '17201B014','ENGL&101'UNION ALLSELECT '17273B014','ENGL&101'UNION ALLSELECT '17396B014','ENGL&101'UNION ALLSELECT '17624B014','ENGL&101'UNION ALLSELECT '17659B014','ENGL&101'UNION ALLSELECT '17723B014','ENGL&101'UNION ALLSELECT '17894B014','ENGL&101'UNION ALLSELECT '18158B014','ENGL&101'UNION ALLSELECT '18291B014','ENGL&101'UNION ALLSELECT '18308B014','ENGL&101'UNION ALLSELECT '18387B014','ENGL&101'UNION ALLSELECT '18917B014','ENGL&101'UNION ALLSELECT '19046B014','ENGL&101'UNION ALLSELECT '20037B014','ENGL&101'UNION ALLSELECT '20188B014','ENGL&101'UNION ALLSELECT '20204B014','ENGL&101'UNION ALLSELECT '20297B014','ENGL&101'UNION ALLSELECT '20446B014','ENGL&101'UNION ALLSELECT '20526B014','ENGL&101'UNION ALLSELECT '20621B014','ENGL&101'UNION ALLSELECT '20651B014','ENGL&101'UNION ALLSELECT '20963B014','ENGL&101'UNION ALLSELECT '21088B014','ENGL&101'UNION ALLSELECT '21415B014','ENGL&101'UNION ALLSELECT '21517B014','ENGL&101'UNION ALLSELECT '21969B014','ENGL&101'UNION ALLSELECT '21983B014','ENGL&101'UNION ALLSELECT '22154B014','ENGL&101'UNION ALLSELECT '22514B014','ENGL&101'UNION ALLSELECT '22535B014','ENGL&101'UNION ALLSELECT '22552B014','ENGL&101'UNION ALLSELECT '22558B014','ENGL&101'UNION ALLSELECT '22598B014','ENGL&101'UNION ALLSELECT '23037B014','ENGL&101'UNION ALLSELECT '23397B014','ENGL&101'UNION ALLSELECT '23531B014','ENGL&101'UNION ALLSELECT '23567B014','ENGL&101'UNION ALLSELECT '24236B014','ENGL&101'UNION ALLSELECT '24382B014','ENGL&101'UNION ALLSELECT '24617B014','ENGL&101'UNION ALLSELECT '24711B014','ENGL&101'UNION ALLSELECT '24853B014','ENGL&101'UNION ALLSELECT '24963B014','ENGL&101'UNION ALLSELECT '25151B014','ENGL&101'UNION ALLSELECT '25169B014','ENGL&101'UNION ALLSELECT '25939B014','ENGL&101'UNION ALLSELECT '26015B014','ENGL&101'UNION ALLSELECT '26056B014','ENGL&101'UNION ALLSELECT '26147B014','ENGL&101'UNION ALLSELECT '26273B014','ENGL&101'UNION ALLSELECT '26560B014','ENGL&101'UNION ALLSELECT '26891B014','ENGL&101'UNION ALLSELECT '27035B014','ENGL&101'UNION ALLSELECT '27129B014','ENGL&101'UNION ALLSELECT '27448B014','ENGL&101'UNION ALLSELECT '27464B014','ENGL&101'UNION ALLSELECT '28025B014','ENGL&101'UNION ALLSELECT '28068B014','ENGL&101'UNION ALLSELECT '28101B014','ENGL&101'UNION ALLSELECT '28136B014','ENGL&101'UNION ALLSELECT '28873B014','ENGL&101'UNION ALLSELECT '28895B014','ENGL&101'UNION ALLSELECT '28993B014','ENGL&101'UNION ALLSELECT '29228B014','ENGL&101'UNION ALLSELECT '29558B014','ENGL&101'UNION ALLSELECT '29680B014','ENGL&101'UNION ALLSELECT '29711B014','ENGL&101'UNION ALLSELECT '29715B014','ENGL&101'UNION ALLSELECT '29840B014','ENGL&101'[/code]CourseID is the join key between these two tables. EntryCodeID is the code itself.What we would like to do is select ClassCapacity + 10 entry codes to each ClassID. The EntryCodeIDs must be unique, must not overlap into other ClassIDs.Is there a way accomplish this without a cursor? I'm thinking a CTE might work, but I have never been able to completely wrap my head around their syntax.Suggestions? Running on SQL 2008 R2.Thank you in advance!Mike

Finding the total count of ACTIVE Customers and their remaining TOTAL POINTS for each distinct groups of CATEGORY.

Posted: 20 May 2013 02:53 AM PDT

HiI've a CustomerActivity table that records all the activities of the consumers.I've create a simplyfied version of the table with the activities of just two members as below:USE [Sample]GOANSI_PADDING ONGO-- Create a ConsumerActivityTableCREATE TABLE [dbo].[ConsumerActivity]( [ConsumerID] [varchar](10) NOT NULL, [ActivityDate] [datetime] NULL, [Status] [varchar](10) NULL, [Category] [varchar](10) NULL, [PointsEarned] [int] NULL, [PointsUsed] [int] NULL, [Description] [varchar](100) NULL) ON [PRIMARY] GO-- Insert some sample data into this activity tableGOINSERT INTO dbo.ConsumerActivity (ConsumerID, ActivityDate, [Status], Category, PointsEarned, PointsUsed,[Description])SELECT '101', '2013-05-10 00:00:00.000','ACTIVE','PREMIUM',NULL,'500','Upgrage to PREMIUM class'UNION ALLSELECT '101', '2013-05-07 00:00:00.000','ACTIVE','GENERAL',NULL,'-200','Points used'UNION ALLSELECT '101', '2013-02-10 00:00:00.000','ACTIVE','GENERAL','200',NULL,'General purchase'UNION ALLSELECT '101', '2012-10-12 00:00:00.000','ACTIVE','GENERAL','200','100','First time purchase with bonus points'UNION ALLSELECT '102', '2013-05-10 00:00:00.000','ACTIVE','GENERAL','600',NULL,'Account Reactivated'UNION ALLSELECT '102','2013-01-10 00:00:00.000','INACTIVE','GENERAL','600','-600','Account DEACTIVATED and the remaining points are adjusted'UNION ALLSELECT '102','2012-10-20 00:00:00.000','ACTIVE','GENERAL','300',NULL,'General Purchase'UNION ALLSELECT '102','2012-11-20 00:00:00.000','ACTIVE','GENERAL',NULL,'-200','Points used'UNION ALLSELECT '102','2012-09-20 00:00:00.000','ACTIVE','GENERAL','400','100','New purchase with bonus points'GO-- SELECT * from this table to see how the data looks currentlyGOSELECT * FROM dbo.ConsumerActivityORDER BY ConsumerID, ActivityDate DESCGONow, I need to write a query that gives me the "TOTAL COUNT" of ACTIVE customers and their "TOTAL Remaining Points (Rewards + Bonus points)", for each DISTINCT group of "Category" as of @ReportDate(parameter). All these should be based on the latest STATUS and latest CATEGORY on a specified @ReportDate parameter.Here are the two scenarios of how I want to see the result from the given sample:• If @ReportDate = '05/15/2013' Category TotalActiveConsumerCount TotalRemainingPointsPremium 1 800General 1 600Here, for all the consumers whose accounts has been REACTIVATED, we only use the points that they've gathered after they becomes ACTIVE again. The latest CATEGORY of consumerID 101 on the specified date is PREMIMUM.• If @ReportDate = '04/15/2013'ConsumerType TotalActiveConsumerCount TotalRemainingPointsPremium 0 0General 1 500Here, the latest Category of consumerID 101 on the specified date is GENERAL. The latest status of ConsumerID 102 on the specified date is INACTIVE and thus we don't need to count this consumer at all.Thanks for your help in advance

Datetime function help

Posted: 20 May 2013 07:22 AM PDT

Hi, I m trying to get a datetime function for the start day of current week (Sunday). I m using the below function select DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)However, i need to start from 7/5/4 AM every sunday depends on the value of the variable supplied.like : declare @minute int set @minute = -420 select DATEADD(wk,DATEDIFF(wk,0, dateadd(mi,-1*@minute,GETDATE())),-1)I m expecting it to return : 2013-05-19 07:00:00.000but it returns : 2013-05-19 00:00:00.000 Can someone help me on this?THank you

script for database diagrams

Posted: 20 May 2013 05:39 AM PDT

hi all,i want to convert database diagram to script in sql server 2012,how?someone told me to check Definition field in sysdiagrams, but couldn't find anythinghope u can help me

Can't CREATE or ALTER store procedure that has a linked server query

Posted: 20 May 2013 03:23 AM PDT

Ok,This is driving me crazy.The security configuration for Linked Server on the local one is as follow:[b]On Local server login to remote server login mappings:[/b]UserA to UserA[b]For a login not defined in the list above, connections will[/b]"Be made using this security context" .... and remote login is "sa"The Linked Server works fine for regular queries, but a non client database is being moved out to the 2nd server. Now a local store procedure that points to the database that it's being moved cannot be altered, not even by me. When I run the following, using my own DBA account ...[code="sql"]CREATE PROCEDURE ABC AS set nocount on; SELECT TOP 1 [col2] FROM [RemoteServer].[dbName].[dbo].[table] where col1 = something GO[/code]I get this error:[code="plain"]The OLE DB provider "SQLNCLI" for linked server "xxxxxx" does not contain the table "MyTable here". The table either does not exist or the current user does not have permissions on that table.[/code]If I open a query window and connect to local server using "UserA", I can run the query inside and see the remote table and data set. However, I can't locally alter or create the store procedure with that remote query. I know it's because the LinkedServer credentials, but how can I solve this without adding extra logins or myself to the LinkedServer properties? I want to minimize the security risk of connecting or running queries across servers using elevated privileges. "UserA" has limited privileges on the remote server. And "sa" on remote server is not actually "sa". It has limited privileges as well. It uses same name, but that's because it is encoded on the app, so I tricked the system that way.Any hints?

Sum column based on time interval

Posted: 20 May 2013 03:23 AM PDT

Hi all i have a table in which results are stored every 30 minutes.[code="plain"]tblResultsTestName sample_time valueTest1 5/17/2013 12:00 657Test2 5/17/2013 12:30 634Test3 5/17/2013 13:00 576Test4 5/17/2013 13:30 641[/code]I would like to sum results for the sample_time of 1 hour so for 12PM-1PM value = 1867I'm using this query[code="plain"]SELECT sample_time AS sample_time_start , sample_time AS sample_time_end , SUM(VALUE) FROM tblResults WHERE GROUP BY DATEPART(HOUR, SAMPLE_TIME)[/code]but i need to see TestName and sample_time column also like this[code="plain"]tblResultsTestName sample_time_start sample_time_end valueTest1 5/17/2013 12:00 5/17/2013 01:00 1867[/code]any help appreciated.

SQL round,FLOOR

Posted: 20 May 2013 03:03 AM PDT

Hi,I need -13599.99 as 13600162157.36 as 1621577415781.64 AS 7415782How can I achive that ?thanks,

COUNT function during 0 case

Posted: 20 May 2013 01:31 AM PDT

I have two tables [b]1) Product with columns[/b][code="sql"]ProductId Name1 ABC2 DEF [/code][b]2) Sales[/b][code="sql"]TransactionId ProductId ReqTime ResTime1 1 05/20/2013 13:22 05/20/2013 13:232 1 05/20/2013 13:22 05/20/2013 13:233 1 05/20/2013 13:22 05/20/2013 13:23[/code]I want to get an output like this :[code="other"]ProductId TotalSales1 32 0[/code]I am currently doing a query like this :[code="sql"][code="other"]select Distinct(ProductId), Count(TransactionId) from Product LEFT OUTER JOIN Transaction ON Product.ProductId=Transaction.ProductIdwhere ReqTime > @BeginTime and ResTime <@EndTimeGroupby Product.ProductId;[/code][/code][code="sql"]I get an output like this :ProductId TotalSales1 3[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog