Need help understanding how to filter out rows

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)

Need help understanding how to filter out rows

Author

Topic

furrelkt
Starting Member

49 Posts

Posted - 2009-12-23 : 15:54:03

I need some direction, i can't seem to get this right, i can get #1 requirement and #2 but not both together...please need assistance.


SELECT dp.PROJECT_ID 
 , t.prstatus
 , t.prname
 , t.prfinish
 
FROM  NBI_PROJECT_CURRENT_FACTS dp 
INNER JOIN PRTASK t ON t.PRPROJECTID = dp.PROJECT_ID

WHERE (1=1) and dp.is_active <> 0
and t.is_open_te = 1 --tsk open for te
AND t.prismilestone <> 1 AND t.priskey <> 0  --key task only

1. need to pull the prfinish date where the prname like '%Package and Delivery%' and keep (store) the prfinish date AS delivery_date.
2. need to pull the task where min(prfinish) and prstatus <> 2 and list this task only

details:basically there would be ONE row per project_id, the row with #2 requirement (prname) and #1 requirement (delivery_date)


RETURNS:
PROJECT_ID prstatus prname                                  	   prfinish
5007767	   2	    7458 - Develop Detailed Requirements	   2009-12-10 17:00:00.000
5007767	   1	    7458 - Requirements Client Review and Sign-off 2009-12-21 17:00:00.000
5007767	   1	    7458 - Code					   2009-12-18 17:00:00.000
5007767	   1	    7458 - Perform System Testing	           2009-12-22 17:00:00.000
5007767	   0        7458 - Package and Delivery to Client for UAT  2010-01-15 17:00:00.000
5007767	   0	    7458 - UAT					   2010-06-15 17:00:00.000
5007769	   2	    7460 - Develop Detailed Requirements	   2009-10-16 17:00:00.000
5007769	   1	    7460 - Requirements Client Review and Sign-off 2009-12-29 15:24:00.000
5007769	   2	    7460 - Code					   2009-10-22 17:00:00.000
5007769	   1	    7460 - Perform System Testing		   2010-12-09 15:36:24.000
5007769	   2	    7460 - Package and Delivery to Client for UAT  2010-12-16 16:13:00.000
5007769	   1	    7460 - UAT					   2010-06-15 17:00:00.000


WANT TO RETURN:
PROJECT_ID  prstatus prname	 prfinish		 delivery_date
5007767	    1	     7458 - Code 2009-12-18 17:00:00.000 2010-01-15 17:00:00.000
5007769	    2        7460 - Code 2009-10-22 17:00:00.000 2010-12-16 16:13:00.000

thank you for any insight you all can provide me.

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

furrelkt
Starting Member

49 Posts

Posted - 2009-12-24 : 09:26:39

Anyone have any ideas for me?? please, i am really needing some help here guru's. THX

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-24 : 09:56:02

Try this...

SELECT dp.PROJECT_ID
, t.prstatus
, t.prname
, t2.minPrFinish as PrFinish
, t3.maxPrFinish as Deliverydate

FROM NBI_PROJECT_CURRENT_FACTS dp
INNER JOIN PRTASK t ON t.PRPROJECTID = dp.PROJECT_ID
Inner Join
(Select
ProjectID, prname ,prfinish
from (Select *, Row_Number() OVER(Partition by Project_ID order by Project_ID) as myRow
from PrTask where prStatus <> 2) a where a.myRow = 1) t2
on dp.Project_ID = t2.Project_ID
Inner Join
(Select ProjectID, Max(prfinish) as maxPrFinish from PRTASK) t3 on dp.Project_ID = t3.Project_ID

WHERE (1=1) and dp.is_active <> 0
and t.is_open_te = 1 --tsk open for te
AND t.prismilestone <> 1 AND t.priskey <> 0 --key task only

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-24 : 10:02:44

how do you get this record

"5007769	    2        7460 - Code 2009-10-22 17:00:00.000 2010-12-16 16:13:00.000"

it seems to be coming from this record. But the prstatus = 2 which is not the condition you specified

5007769	   2	    7460 - Code					   2009-10-22 17:00:00.000

KH
[spoiler]Time is always against us[/spoiler]

furrelkt
Starting Member

49 Posts

Posted - 2009-12-24 : 11:23:25

quote:
Originally posted by khtan

how do you get this record
"5007769 2 7460 - Code 2009-10-22 17:00:00.000 2010-12-16 16:13:00.000"

it seems to be coming from this record. But the prstatus = 2 which is not the condition you specified
5007769 2 7460 - Code 2009-10-22 17:00:00.000

KH
[spoiler]Time is always against us[/spoiler]

You are correct, an oversight on my part, i do apologize... it should be ...


5007769	   1  7460 - Requirements Client Review and Sign-off   2009-12-29 15:24:00.000

(which is the next min finish date that is not a prstatus of 2.. thank you for catching that for me.)
[code]
with a delivery date of :2010-12-16 16:13:00.000
[code]

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

furrelkt
Starting Member

49 Posts

Posted - 2009-12-24 : 11:47:33

To clarify...

The delivery date should be the finish date of the task named '%Package and Delivery%' in each project. so for instance, in project 5007767, the finish date of the task that's called "7458 - Package and Delivery to Client for UAT" THAT finish date is "2010-01-15 17:00:00.000" so that WILL be the Delivery date for that single row for project 5007767.

The next requirement will be finding the task that is NOT a status of 2 and the min finish date. So again, for project 5007767, the task that has the min finish date is "7458 - Develop Detailed Requirements" BUT it has a status of 2 so we don't want that one, we want the task WITHOUT a status of 2, so the next min date would be "2009-12-18 17:00:00.000" and that task (prname) is called "7458 - Code".

for the project 5007767 row (single row) it would list as
PROJECT_ID..prstatus...prname....prfinish.....delivery_date
5007767..1...7458 - Code....2009-12-18 17:00:00.000.....2010-01-15 17:00:00.000

HTH my explanation a bit more. Thank you.

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-25 : 01:49:25

[code]
; with result (PROJECT_ID, prstatus, prname, prfinish)
as
(
SELECT dp.PROJECT_ID
, t.prstatus
, t.prname
, t.prfinish

FROM NBI_PROJECT_CURRENT_FACTS dp
INNER JOIN PRTASK t ON t.PRPROJECTID = dp.PROJECT_ID

WHERE (1=1) and dp.is_active <> 0
and t.is_open_te = 1 --tsk open for te
AND t.prismilestone <> 1 AND t.priskey <> 0 --key task only
)
select PROJECT_ID, prstatus, prname, prfinish, delivery_date
from
(
select *, row_no = row_number() over (partition by PROJECT_ID order by prfinish)
from result s
where prstatus <> 2
) a
cross apply
(
select delivery_date = prfinish
from result x
where x.PROJECT_ID = a.PROJECT_ID
and prname like '%Package and Delivery%'
) d
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

furrelkt
Starting Member

49 Posts

Posted - 2009-12-28 : 09:43:48

khtan, that's it. i have checked about 4 project and will continue to check but that's it. thank you so much for your help. thanks all who replied.

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources