Pick maximun date, and amount from that date

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

General SQL Server Forums

New to SQL Server Programming

Pick maximun date, and amount from that date

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-06-26 : 11:31:08

Hi, I have these fields in table T

project, name, billedData, amount

I want to be able to see for each project, all name with their last billedDate and amount. I can do the maximun for billed date, but no sure how to bring the specific amount for that date.

Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-26 : 11:43:04

SELECT *
FROM

(select project, name, billedData, amount
,row_number() over(partition by project order by billedDate desc) as RowNumber
from T
)
WHERE T.RowNumber =1

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 11:45:40

There are a bunch of ways to get those results. Here are two:

SELECT
	project, 
	name, 
	billedDate, 
	amount
FROM
	(
		SELECT
			project, 
			name, 
			billedDate, 
			amount,
			ROW_NUMBER() OVER (PARTITION BY project ORDER BY billedDate DESC) AS RowNum
		FROM
			T
	) AS A
WHERE
	RowNum = 1

SELECT
	A.project, 
	A.name, 
	A.billedDate, 
	A.amount
FROM
	T AS A
INNER JOIN
	(
		SELECT	
			project,
			MAX(billedDate) AS billedDate
		FROM
			T
		GROUP BY
			project
	) AS B
	ON A.project = B.project
	AND A.billedDate = B.billedDate

jaimealvarez
Starting Member

31 Posts

Posted - 2012-06-26 : 11:54:37

Hi, thanks for your help. I tried to adapt your code to mine but get an error saying that can't add select.

My code looks like this:

SELECT
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt

FROM

(
select
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt
, row_number()
over (partition by timecard.tmatter
order by timecard.tworkdt desc) as RowNumber
from son_db.dbo.timecard timecard
)
WHERE timecard.RowNumber = 1

Please let me know if you can see where I went wrong. Thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 12:18:07

You need to alias your derived table:

SELECT 
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt

FROM 

(
select 
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt
, row_number() 
over (partition by timecard.tmatter 
order by timecard.tworkdt desc) as RowNumber
from son_db.dbo.timecard timecard
) AS timecard
WHERE timecard.RowNumber = 1

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 12:22:09

quote:
Originally posted by jaimealvarez

Hi, thanks for your help. I tried to adapt your code to mine but get an error saying that can't add select.

My code looks like this:

SELECT
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt

FROM

(
select
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt
, row_number()
over (partition by timecard.tmatter
order by timecard.tworkdt desc) as RowNumber
from son_db.dbo.timecard timecard
)
WHERE timecard.RowNumber = 1

Please let me know if you can see where I went wrong. Thank you

SELECT
T.tmatter
, T.ttk
, T.tworkdt
, T.tworkrt

FROM

(
select
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt
, row_number()
over (partition by timecard.tmatter
order by timecard.tworkdt desc) as RowNumber
from son_db.dbo.timecard timecard
) AS T
WHERE T.RowNumber = 1

--------------------------
Get rich or die trying
--------------------------

jaimealvarez
Starting Member

31 Posts

Posted - 2012-06-26 : 12:32:22

Thank you! This brings me close:

SELECT
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt

FROM

(
select
timecard.tmatter
, timecard.ttk
, timecard.tworkdt
, timecard.tworkrt
, row_number()
over (partition by timecard.tmatter
order by timecard.tworkdt desc) as RowNumber
from son_db.dbo.timecard timecard
) AS timecard
WHERE timecard.RowNumber = 1

The only thing is that it shows me the project(tmatter) and then just the last name(ttk). I need it to show the project(tmatter), all names under that project, with their last billeddate(tworkdt) and the amount billed (tworkrt) on that last date.

So something like this:

Project Name LastBilledDate Amount
A Alfred 1/1/12 230
A Peter 3/1/12 320
B John 12/2/11 420
C Patrick 6/3/12 520
C John 6/2/12 210
C Peter 5/1/11 120

I should add, just if it makes it easier, that there is a index number per record (tindex). Thank you for your help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 13:19:03

I assume you just need to change the PARTITION BY clause to inclue name:

row_number() 
over (partition by timecard.tmatter, timecard.name 
order by timecard.tworkdt desc) as RowNumber

jaimealvarez
Starting Member

31 Posts

Posted - 2012-06-26 : 13:41:38

Worked! Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources