InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 22:05:35 GMT20http://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx[quote][b]anthony.green (2/13/2013)[/b][hr][quote][b]Lowell (2/13/2013)[/b][hr]side note to mister.magoo: which SSMS add on is that that lets you group totals from the results grid?[/quote]I belive it is the custom one he is writing from this topic [url]http://www.sqlservercentral.com/Forums/Topic1366484-391-1.aspx[/url][/quote]That's right, it is one I am writing. If you want to test it let me know and as soon as it is ready I will PM you about it.Wed, 13 Feb 2013 17:46:34 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxGood to just know you are aware :-DWed, 13 Feb 2013 12:15:58 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx[quote][b]mister.magoo (2/13/2013)[/b][hr]:w00t: You're welcome, but really don't use that for anything important..you will get bitten eventually :cool:[/quote]Already seeing that :-D but we understand the risk.Wed, 13 Feb 2013 12:13:38 GMTU.Thttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx:w00t: You're welcome, but really don't use that for anything important..you will get bitten eventually :cool:Wed, 13 Feb 2013 12:12:02 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx[quote][b]mister.magoo (2/12/2013)[/b][hr]Sean is right, however in this one case you can make it seem like it works....[code="sql"]SELECT *FROM (SELECT *,row_number() OVER(PARTITION by TType ORDER BY TTime,TFunction,TStatus) AS rnFROM MyTransactions) aORDER BY rn,TTime, TType, TFunction[/code]Just don't use it because where you have multiple rows with the same time stamp there is no clear way to be sure of the order....[/quote]Thanks mister.magoo that did the trick for what we are trying to achieve.Wed, 13 Feb 2013 07:35:43 GMTU.Thttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx[quote][b]Lowell (2/13/2013)[/b][hr]side note to mister.magoo: which SSMS add on is that that lets you group totals from the results grid?[/quote]I belive it is the custom one he is writing from this topic [url]http://www.sqlservercentral.com/Forums/Topic1366484-391-1.aspx[/url]Wed, 13 Feb 2013 05:49:20 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxside note to mister.magoo: which SSMS add on is that that lets you group totals from the results grid?Wed, 13 Feb 2013 05:43:42 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxSean is right, however in this one case you can make it seem like it works....[code="sql"]SELECT *FROM (SELECT *,row_number() OVER(PARTITION by TType ORDER BY TTime,TFunction,TStatus) AS rnFROM MyTransactions) aORDER BY rn,TTime, TType, TFunction[/code]Just don't use it because where you have multiple rows with the same time stamp there is no clear way to be sure of the order....[img]http://www.sqlservercentral.com/Forums/Attachment13200.aspx[/img]Tue, 12 Feb 2013 17:20:50 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx[quote][b]U.T (2/12/2013)[/b][hr]Attached are the current and required output screen shots. Note that i have created the required output in excel :-)[/quote]I had a feeling that was what you were looking for. The problem is there is not any kind of sort that will work here.Tue, 12 Feb 2013 16:58:51 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxAttached are the current and required output screen shots. Note that i have created the required output in excel :-)Tue, 12 Feb 2013 14:47:36 GMTU.Thttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspx[quote][b]U.T (2/12/2013)[/b][hr]Thanks Lowell. Unfortunately not, it still does not group them together.There should always be an outgoing after an incoming, and we need to make sure both are of same function status (i.e. if incoming is of "Activation" the corresponding outgonig should be "Activation Outgoing").[/quote]Can you post what the output order should be based on your sample data? (quite nicely posted I might add ;-))Tue, 12 Feb 2013 14:42:18 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxThanks Lowell. Unfortunately not, it still does not group them together.There should always be an outgoing after an incoming, and we need to make sure both are of same function status (i.e. if incoming is of "Activation" the corresponding outgonig should be "Activation Outgoing").Tue, 12 Feb 2013 14:26:50 GMTU.Thttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxUT excellent job with the DDL and sample data; does this give you the results you are looking for?[code]SELECT ID, TTYpe, TTime, COALESCE(NULLIF(TFunction, ''), TStatus) AS FunctionStatusFROM MyTransactionsORDER BY TTime [/code]Tue, 12 Feb 2013 14:15:24 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1419176-392-1.aspxI have some transactional data that i am having hard time to sort properly. CREATE TABLE [MyTransactions]( [Id] [uniqueidentifier] NOT NULL Primary KEY, [TType] [varchar](50) NULL, [TTime] [datetime] NULL, [TFunction] [varchar](50) NULL, [TStatus] [varchar](50) NULL)[b]DON'T CHANGE THE ORDER OF INSERT STATEMENTS BECAUSE THAT'S HOW WE ARE GETTING THE DATA.[/b]INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:24:51.000', 'Activate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:24:52.000', '', 'Activation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:25:00.000', 'Activate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:25:00.000', '', 'Activation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:26:00.000', 'Deactivate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:26:00.000', '', 'Deactivation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:27:17.000', 'Activate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:27:17.000', 'Activate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:27:17.000', '', 'Activation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:27:17.000', '', 'Activation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:29:17.000', 'Activate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:29:17.000', 'Deactivate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:29:17.000', '', 'Activation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:29:17.000', '', 'Dectivation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:30:00.000', 'Dectivate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Incoming', '2012-10-15 11:31:00.000', 'Activate', '')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:31:00.000', '', 'Dectivation Outgoing')INSERT INTO MyTransactions VALUES (NEWID(), 'Outgoing', '2012-10-15 11:32:00.000', '', 'Activation Outgoing')SELECT *FROM MyTransactionsORDER BY TTime, TType, TFunctionAbove sort does not group the Incoming & Outgoing transactions together (see the attached currenoutput.jpg file). For each Activate/Deactivate function I want to group their Incoming/Outgoing transactions together.So the required output should alway will be in this order (i am just showing two columns below to demonstrate the required sorting but actually i will need all columns in the output).[b]TFunction TType[/b]Activate Incoming Activate OutgoingActivate Incoming Activate OutgoingDeactivate Incoming Deactivate OutgoingPlease note that there is no additional information available in the table that can help grouping the data, also we cannot modify table structure so I want a query that can output the data in above order. (See the attached RequiredOutput.jpg)Tue, 12 Feb 2013 13:49:29 GMTU.T