A different Distinct Query

Question

Post reply

A different Distinct Query

johncyriac

SSCommitted

Points: 1542
More actions
October 6, 2008 at 5:23 am

#199300

I am having a table looks like
tbl_sal
st_no year month sal
1 2008 1 1000
1 2008 2 1000
1 2008 3 1000
1 2008 4 1010
1 2008 5 1010
1 2008 6 1010
1 2008 7 1010
1 2008 8 1020
....
is it possible to get the year,month,sal for each change in salary
regards
john

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

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,1000

INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,2,1000

INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,3,1000

INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,4,1010

INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,5,1010

INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,6,1010

INSERT INTO @t (st_no, year, month, sal) SELECT 1,2008,7,1010

INSERT 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 cte

WHERE seq = 1

/*

year month sal seq

----------- ----------- --------------------- --------------------

2008 1 1000.00 1

2008 4 1010.00 1

2008 8 1020.00 1

*/

.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

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.

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

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.

.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

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], sal

FROM YourTable

GROUP BY sal

ORDER BY sal

Alvin Ramard
Memphis PASS Chapter[/url]

All 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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Vladan SSC-Insane Points: 22072 More actions · Answer 5

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.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 6

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.sal

FROM cte a

WHERE NOT EXISTS(SELECT * FROM cte b

WHERE a.st_no=b.st_no

AND a.sal=b.sal

AND a.seq=b.seq+1)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jacob sebastian SSChampion Points: 11822 More actions · Answer 7

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.

.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Someone slap me please! All I keep thing about is using a ...... cursor.

Alvin Ramard
Memphis PASS Chapter[/url]

All 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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

bobhovious SSC Enthusiast Points: 188 More actions · Answer 9

Okay..... SLAP !!! 😀

Seriously... 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. Then

select cteA.year, cteA.month, cteA.salary

from cteA

join cteB on cteB.[RowNum] = cteA.[RowNum]-1

where cteA.salary <> cteB.Salary

johncyriac SSCommitted Points: 1542 More actions · Answer 10

Hi Friends thank you so much for yor overwhelming reposne to my query

after a while i realised that we could solve this query using some simple logic

johncyriac SSCommitted Points: 1542 More actions · Answer 11

Hi Friends

Thank you so much for yor overwhelming reposne to my query

After a while I realised that we could solve this query using a simple logic

First consider this function which returns previous month sal

Now the query looks simple

select * from tbl_sal where sal<> dbo.FnPreviousMonthSal (staff_no,year ,month)

create function FnPreviousMonthSal (@stno int ,@year numeric(4),@month numeric(2))

returns numeric(15)

as

begin

if @month =1

begin

set @year=@year-1

set @month=12

end

else

begin

set @month=@month-1

end

return isnull( (select sal from tbl_sal where staff_no=@stno and year=@year and month=@month ) ,0 )

end

Same function logic may be incorporated in a single query

Scott D-810830 SSC-Addicted Points: 494 More actions · Answer 12

That may be a simple function, but you have just killed your performance. If your table has 1000 rows in it, that function (and the select inside of it) will execute 1000 times. -- very bad --

This problem can be solved with the running sums/tally table concept.

Scott

SET NOCOUNT ON;

CREATE TABLE #t (st_no INT, year INT, month INT, sal MONEY, change int, PRIMARY KEY CLUSTERED (st_no, year, month))

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,1,1000

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,2,1000

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,3,1000

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,4,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,5,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,6,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,7,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,8,1020

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,9,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,10,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 1,2008,11,1010

INSERT INTO #t (st_no, year, month, sal) SELECT 2,2008,1,2000

INSERT INTO #t (st_no, year, month, sal) SELECT 2,2008,2,2000

INSERT INTO #t (st_no, year, month, sal) SELECT 2,2008,3,2200

INSERT INTO #t (st_no, year, month, sal) SELECT 2,2008,4,2200

INSERT INTO #t (st_no, year, month, sal) SELECT 2,2008,5,2000

DECLARE @st_no int

DECLARE @new_st_no int

DECLARE @change int

DECLARE @Sal money

SET @st_no = 0

SET @new_st_no = 0

SET @change = 0

SET @Sal = 0

UPDATE #t

SET @new_st_no = CASE WHEN st_no = @st_no THEN 0 ELSE 1 END,

@change = change = CASE WHEN @Sal = sal THEN 0 ELSE 1 END,

@Sal = sal,

@st_no = st_no

SELECT * FROM #t WHERE change = 1

DROP TABLE #t

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 13

I think the function is just hidden row by row processing, the CTE will work, you just need to join it to itself. Assuming st_no is some identifier that ties these records together: