rank?..rownumber? - 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 2008 Forums

Transact-SQL (2008)

rank?..rownumber?

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2011-06-16 : 14:57:24

My problem is how to find the project end date slippage.

slight twist to an age old problem of finding field values in the first record of a group, and the last record of the group...

given a dataset of projects, where the project information is taken once a week (tagged by a field called datamartdate which is set to the day on which each snapshot was taken)...

now group the dataset by the projectID..and order by datamartdate..this produces groups of project data...

First I want the ProjectEndDate from the first record in the group (this represents the original project end date). Next I want the ProjectEndDate from the last record in the project group (this represents the most current project end date. If I cast the date into a different, I get the project slippage (or early delivery).

What is the best way to select data from the first and last record in a group?

I have this...but min and max are not the right functions.

SELECT pin
,min(PlannedEndDate) AS OldestEndDate
,max(plannedenddate)as CurrentEndDate
,cast(max(plannedenddate)-min(plannedenddate) as int) as EndDateVariance
FROM epm_project
where projectstate='active' and plannedenddate is not null
group by pin order by pin datamartdate

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-16 : 15:03:46

MIN and MAX should be okay

SELECT pin
,min(PlannedEndDate) AS OldestEndDate
,max(plannedenddate)as CurrentEndDate
,datediff(day,min(PlannedEndDate) ,max(PlannedEndDate)) as EndDateVariance
FROM epm_project
where projectstate='active' and plannedenddate is not null
group by pin order by pin

Jim

Everyday I learn something that somebody else already knew

dlorenc
Posting Yak Master

172 Posts

Posted - 2011-06-17 : 09:23:25

I think I needed to order by datamartdate in my original note...sorry..

I disagree.. min and max will give you the min/max value of the set?...mmm?..not the plannedenddate from the oldest record in the project group (defined by the oldest datamartdate) and the most current record in the project group (defined by the most current datamartdate)...mm?..

Consider the following grouped by pin, ordered by pin, datamartdate

pin datamartdate plannedenddate
10 1/1/2011 3/1/2011
10 1/8/2011 5/1/2011
10 1/15/2011 4/1 2011

the original plannedenddate happens to be the min...3/1/2011 (however, a project that goes well COULD end earlier that the original planned end date..so this is not always the case)

the current plannedenddate is 4/1...not the max (which would give you 5/1...I think..mmm?...

this is why I was thinking of some way to rank them first..then the min max on the ranking would work...??...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 09:31:35

So the values you want are 3/1 and 4/1 ?

Jim

Everyday I learn something that somebody else already knew

dlorenc
Posting Yak Master

172 Posts

Posted - 2011-06-17 : 09:48:01

exactly.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 09:50:54

Not sure if this is the best way, but it does use rown_number()!

DECLARE @Table Table(Pin int,datamartdate datetime,plannedenddate datetime)

INSERT INTO @Table
SELECT 10 ,'1/1/2011','3/1/2011' UNION
SELECT 10,'1/8/2011','5/1/2011' UNION
SELECT 10,'1/15/2011','4/1/2011'

;with cte
as
(
select pin,datamartdate,plannedEndDate
,row_number() over(partition by pin order by datamartdate asc) as Firstdate
,row_number() over(partition by pin order by datamartdate desc) as LastDate
from @table
)

select c1.pin,datediff(day,c1.plannedenddate,c2.plannedenddate)
from cte c1
inner join cte c2
on c1.pin = c2.pin
and c1.firstdate = c2.lastdate
and c1.firstdate = 1

Jim

Everyday I learn something that somebody else already knew

dlorenc
Posting Yak Master

172 Posts

Posted - 2011-06-17 : 10:07:43

ahhh...well done...thank you!...

I change the first plannedenddate to 7/1 (to represent a project that is pulled up)...and got the right answer (-91 days slippage, which represents and early delivery)

^5

dlorenc
Posting Yak Master

172 Posts

Posted - 2011-06-17 : 12:04:25

lil'help?...

how do I re-point your solution to my data table?... below is my attempt, but sql is yelling at me about " line 8 (which is the ;with) Recursive common table expression 'EPM_Project' does not contain a top-level UNION ALL operator...??..

;with EPM_Project
as
(
select PIN,DataMartDate,PlannedEndDate
,row_number() over(partition by PIN order by DataMartDate asc) as FirstDate
,row_number() over(partition by PIN order by DataMartDate desc) as LastDate
FROM EPM_Project
)

select c1.PIN,c1.PlannedEndDate,c2.PlannedEndDate,datediff(day,c1.PlannedEndDate,c2.PlannedEndDate)as Slippage
from EPM_Project c1
inner join EPM_Project c2
on c1.PIN = c2.PIN
and c1.FirstDate = c2.LastDate
and c1.FirstDate = 1

dlorenc
Posting Yak Master

172 Posts

Posted - 2011-06-17 : 16:19:05

took me a little reading (never did a cte before)... but now I am a bit smarter..I think... here is my final solution based on your most excellent recommendation. Thank you, Jim!

;with cte
as
(
select PIN,DataMartDate,PlannedEndDate
,row_number() over(partition by PIN order by DataMartDate asc) as FirstDate
,row_number() over(partition by PIN order by DataMartDate desc) as LastDate
FROM EPM_Project
)

select c1.pin,c1.plannedenddate,c2.plannedenddate,datediff(day,c1.plannedenddate,c2.plannedenddate)as slippage
from cte c1
inner join cte c2
on c1.pin = c2.pin
and c1.firstdate = c2.lastdate
and c1.firstdate = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources