Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A different Distinct Query


A different Distinct Query

Author
Message
johncyriac
johncyriac
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 58
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



jacob sebastian
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 2523
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
Vladan
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 754
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
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 2523
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
Alvin Ramard
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2904 Visits: 11593
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

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
Vladan
Vladan
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 754
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 Wink

It would work with a proper datetime column though, so one can see how using proper datatypes helps.



Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2288 Visits: 23116
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
jacob sebastian
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 2523
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
Alvin Ramard
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2904 Visits: 11593
Someone slap me please! All I keep thing about is using a ...... cursor.



Alvin Ramard
Memphis PASS Chapter

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
bobhovious
bobhovious
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 17
Okay..... SLAP !!! BigGrin

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search