Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 A different Distinct Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 06, 2008 5:23 AM
 SSC Journeyman Group: General Forum Members Last Login: Saturday, October 23, 2010 4:51 AM Points: 80, Visits: 58
 I am having a table looks like tbl_salst_no year month sal1 2008 1 10001 2008 2 10001 2008 3 10001 2008 4 10101 2008 5 10101 2008 6 10101 2008 7 10101 2008 8 1020....is it possible to get the year,month,sal for each change in salaryregardsjohn
Post #581008
 Posted Monday, October 06, 2008 5:41 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, December 24, 2013 4:42 AM Points: 460, Visits: 2,523
 does it help?`SET NOCOUNT ON;DECLARE @t TABLE (st_no INT, year INT, month INT, sal MONEY)INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,1,1000INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,2,1000INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,3,1000INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,4,1010INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,5,1010INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,6,1010INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,7,1010INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,8,1020;WITH cte AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY sal ORDER BY year, month, sal) AS seq FROM @t)SELECT year, month, sal, seq FROM cteWHERE seq = 1/*year month sal seq----------- ----------- --------------------- --------------------2008 1 1000.00 12008 4 1010.00 12008 8 1020.00 1*/` .
Post #581016
 Posted Monday, October 06, 2008 7:19 AM
 SSCommitted Group: General Forum Members Last Login: Thursday, May 05, 2011 1:38 AM Points: 1,636, Visits: 604
 When I first looked at Jacob's solution, I thought "what a nice solution". Unfortunately, it is simple, understandable and easily maintainable, but it works only in an ideal life where you will never experience drop in salary to your previous level. In fact, it does not record every CHANGE, it just finds first occurence of every value... which is not always the same thing.So, depending on what you need, you can either do with this, or look for a better solution that will show you every change in salary, no matter whether it is raise or the opposite. That would probably require a self-join, where you join each row of the table to the "previous" row and compare salary. This would be a lot easier with DATETIME column for the date instead on two columns, Year and Month.
Post #581077
 Posted Monday, October 06, 2008 8:19 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, December 24, 2013 4:42 AM Points: 460, Visits: 2,523
 I agree. I had this in mind. I see that most of the times the solutions given in the forums are not consumed entirely in its original form. People take ideas from it and convert it to a form that suites their specific business requirements.Similarly, when people post problems on the forums, they may not be posting the actual problem, but a subset of the specific problem. If they manage to get that solved, they could customize it to their specific requirement and add the additional validations and processing etc.ROW_NUMBER() is a great function and it can be used to solve a number of issues. My previous post attempts to solve the problem described in this post using ROW_NUMBER() with the given details of the problem. But it does not mean that ROW_NUMBER() is the right approach, because the post still does not provide the additional details of the application and specific business line. .
Post #581104
 Posted Monday, October 06, 2008 8:34 AM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 2:48 AM Points: 3,089, Visits: 7,745
 John, if all you want is year, month, and sal, would this not work for you?SELECT MIN(year) as [year], MIN(month) as [month], salFROM YourTableGROUP BY salORDER BY sal Alvin RamardMemphis PASS ChapterAll my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #581111
 Posted Monday, October 06, 2008 8:42 AM
 SSCommitted Group: General Forum Members Last Login: Thursday, May 05, 2011 1:38 AM Points: 1,636, Visits: 604
 Alvin,that's not gonna work... the two MIN() are independent. If you have the same salary of 1000 in 12/2007 and 01/2008 (before and after that it was different), what will be the result for salary 1000?Correct... 01/2007... which is wrong ;)It would work with a proper datetime column though, so one can see how using proper datatypes helps.
Post #581117
 Posted Monday, October 06, 2008 8:51 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, April 08, 2014 6:13 AM Points: 1,694, Visits: 19,550
 Maybe this?`WITH cte AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY st_no ORDER BY year, month) AS seq FROM @t)SELECT a.st_no, a.year, a.month, a.salFROM cte aWHERE NOT EXISTS(SELECT * FROM cte b WHERE a.st_no=b.st_no AND a.sal=b.sal AND a.seq=b.seq+1)` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #581125
 Posted Monday, October 06, 2008 8:55 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, December 24, 2013 4:42 AM Points: 460, Visits: 2,523
 May be it is a good idea to wait till we hear from John about his requirements. If he expects salaries to go down, lets write a query that handles that. .
Post #581129
 Posted Monday, October 06, 2008 9:01 AM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 2:48 AM Points: 3,089, Visits: 7,745
 Someone slap me please! All I keep thing about is using a ...... cursor. Alvin RamardMemphis PASS ChapterAll my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #581138
 Posted Monday, October 06, 2008 5:36 PM
 Grasshopper Group: General Forum Members Last Login: Sunday, October 12, 2008 5:16 PM Points: 12, Visits: 17
 Okay..... SLAP !!! :DSeriously... CTEs can get the job done in SQL2005. Define two: cteA and cteB, both of which have a row number column ordered on month within year. Call the row number [RowNum]. CteA will represent the current month and cteB will represent the prior month. Thenselect cteA.year, cteA.month, cteA.salary from cteA join cteB on cteB.[RowNum] = cteA.[RowNum]-1where cteA.salary <> cteB.Salary
Post #581443

 Permissions