Simple Query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple Query...

Author

Topic

cidr2
Starting Member

28 Posts

Posted - 2009-03-12 : 05:56:00

Hi There,

As the heading suggests, this'll be a simple query. However, I'm still learning TSQL and hope someone can help me.

I have a table that has a PayRate column a Staff Column and a StartDate column.

There are 5 Staff values that can be entered either from 1-5. For staff (1) the PayRate might change and a new StartDate will be entered.

I want to write a query that will display each staff value with the Max(StartDate).

I thought I could do something like the query below but obviously not.

It's just a case of listing each staffID (1,2,3,4,5) that has the Maximum start date only.

Hope someone can help.

Cheers


SELECT RateID, ProjectID, StaffID, PayRate, DateStart, DateExpiary
	FROM  Milestones.amDailyRates
	WHERE GradePositionID  IN

		(SELECT StaffID, MAX(StartDate)
			FROM Milestones.amDailyRates
			WHERE StaffID = '1'
			GROUP BY StaffID) AND

		(SELECT StaffID, MAX(StartDate)
			FROM Milestones.amDailyRates
			WHERE StaffID = '2'
			GROUP BY GradePositionID) ETC ETC...

pratikasthana17
Starting Member

3 Posts

Posted - 2009-03-12 : 06:30:25

Hi,
I think for ur problem query will be ....

select Max(StartDate),staff value from <TableName>
group by staff value

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 06:33:01

[code]SELECT PayRate,
Staff,
StartDate
FROM (
SELECT PayRate,
Staff,
StartDate,
ROW_NUMBER() OVER (PARTITION BY Staff ORDER BY StartDate DESC) AS recID
FROM Table1
) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-12 : 06:44:58

you can try doing this...

SELECT x.RateID, x.ProjectID, y.StaffID, x.PayRate, y.StartDate, x.DateExpiary
FROM Milestones.amDailyRates x
JOIN (SELECT StaffID, MAX(StartDate) StartDate
FROM Milestones.amDailyRates
WHERE StaffID in (1,2,3,4,5)
GROUP BY StaffID) y
ON (x.StaffID = y.StaffID and x.StartDate = y.StartDate)
WHERE x.StaffID in (1,2,3,4,5)

I dont know if the table name on your script like StartDate and DateStart is related...is it just a typo error or something?

hope this helps..

cidr2
Starting Member

28 Posts

Posted - 2009-03-12 : 10:01:25

Hi there,

Thanks again for all the input on this.

Peso, I used your technique which worked. However, when I put it into a view, I get parsing error, saying it can't use OVER. It does work after selecting IGNORE, and works subsequently when directly opening the view.

I have an error message everytime I go into design view of the View. Do you think this will matter since it works?

Chrianth, I tried your technique but unfortunately can't get it to work. The DateStart, StartDate is an error.. sorry about that:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources