[T-SQL] T-SQl help... |
| Posted: 21 Sep 2013 04:41 AM PDT Hi, I have the sql sample code and the below image has the ouput needed.. thanks in advance...I`m working with RANKING functions but need some expertise...[code="sql"]IF OBJECT_ID('Tempdb..#tTable') IS NOT NULLDROP TABLE #tTableCREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))INSERT INTO #tTable(CID, CDate, Dept)VALUES(111, '2012-10-05 00:00:00.000','A'),(111, '2012-10-18 00:00:00.000','C'),(111, '2012-11-01 00:00:00.000','B'), (111, '2012-11-01 00:00:00.000','C'), (111, '2012-11-20 00:00:00.000','C'), (111, '2012-12-09 00:00:00.000','C'), (111, '2012-12-11 00:00:00.000','A'), (111, '2013-02-21 00:00:00.000','B'), (111, '2013-03-22 00:00:00.000','B'), (111, '2013-03-22 00:00:00.000','C'), (111, '2013-04-12 00:00:00.000','C'), (111, '2013-04-26 00:00:00.000','B'), (111, '2013-04-26 00:00:00.000','C'),(222, '2012-02-13 00:00:00.000','C'), (222, '2012-03-02 00:00:00.000','B'), (222, '2012-06-16 00:00:00.000','C'), (222, '2012-07-12 00:00:00.000','C'), (222, '2013-04-26 00:00:00.000','B'),(222, '2013-05-23 00:00:00.000','C'), (222, '2013-07-11 00:00:00.000','C'), (222, '2013-09-19 00:00:00.000','C'),(222, '2013-09-20 00:00:00.000','A')SELECT * FROM #tTableDROP TABLE #tTable[/code]The Groups are created based on CDate and Dept.For the CID, the CDate is in ASC order and if Dept=C and the next row is Dept=C...and next row is also Dept=C is a Group. This means if Dept=C value comes one after the other it is a Group.[img]http://www.sqlservercentral.com/Forums/Attachment14358.aspx[/img] |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment