Only need last entry for joined table.

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)

Only need last entry for joined table.

Author

Topic

hbeasley
Starting Member

4 Posts

Posted - 2009-07-24 : 17:14:43

I have been racking my brain over this for a long time, and learning along the way. But I have come up against a road block, so I need help.

Using SQL Reporting Services 2000, I am trying to get only one row for each project id with the last task enered for that project (that would be max task.id). Running the following query, I get one line for every task with duplicate project information, not one line with the last task entered:

Select inv.id AS INV_ID, inv.Name AS INV_NAME, inv.Lifecycle_Phase, inv.PES_Investment_Type, inv.Complexity_Level, task.obj_UID AS TaskID, task.Name AS TaskName, task.obj_ProjectUID AS PROJ_ID " & _

From PES_MACRO_INVESTMENT(inv, obj_UID IN 1444 , id , Name, Lifecycle_Phase, PES_Investment_Type, Complexity_Level ) LEFT OUTER JOIN PES_MACRO_PROJECT_TASK(task, obj_ProjectUID IN 1444, id , Name) ON task.obj_ProjectUID=inv.id Where inv.Complexity_Level LIKE 'Complex' AND inv.PES_Investment_Type LIKE 'OIT Project'

After reading other suggestions, I have tried the Max(task.obj), but I get an error that I have not used Group By. I tried using Group By and it comes back and tells me that I do not have Group declared.

I would really appreciate any help you can provide.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-24 : 21:54:13

Are you using SQL Server??

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-24 : 21:59:20

quote:
Originally posted by hbeasley

I am trying to get only one row for each project id with the last task enered for that project (that would be max task.id)

have a look at this, see if it gives u the right idea. else you'll need to post table definitions so we can better help


SELECT	p.*
FROM	Projects p
JOIN	(
	SELECT	projectID, Max(taskID) taskID
	FROM	projects
) x
On	x.projectID = p.projectID
And	x.taskID - p.taskID;

hbeasley
Starting Member

4 Posts

Posted - 2009-07-25 : 08:44:43

Thank you for your response. Yes, I am running this report on a SQL Server 2000 Database.

Here is a very simplified verssion of what my tables look like:
Project:
PID Name Inv_Type Level
1 Proj 1 Project Complex
2 Act 1 Activity Routine
3 Proj 2 Project Routne
4 Proj 3 Project Complex

Task:
TID TName PID (Project association)
20 Summary 1
21 Task 2 1
22 Task 3 1
24 Summary 2
25 Summary 3
26 Task 2 3
26 Summary 4

Currently, when I run the report, I get the following, which includes all tasks for the selected projects (as depicted in the where and on clauses):

PID Name Inv_Type Level TID Name PID
1 Proj 1 Project Complex 20 Summary 1
1 Proj 1 Project Complex 21 Task 2 1
1 Proj 1 Project Complex 22 Task 3 1
4 Proj 4 Project Complex 26 Summary 4

All I want to see is:

1 Proj 1 Project Complex 22 Task 3 1
4 Proj 4 Project Complex 26 Summary 4

The real problem I am trying to solve is to see if there is only a summary task for project, in which case there are no associated tasks. As you can see from the data, there could be multiple ways to address this:
1) Pass the Maximum Task number and Task Name associated with the project (if it is "Summary", it has no tasks)
2) Pass the Count of tasks to the repor (if it is "1". it has no tasks)
Either way would givde me what I am looking for--which ever is the most efficient.

I want all complex projects to be returned, but I want only one line per project.

Thanks so much!

Harriet

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-25 : 09:09:04

[code]
SELECT PID, Name, Inv_Type, TID, TName
FROM
(
SELECT p.PID, p.Name, p.Inv_Type, t.TID, t.TName,
row_no = row_number() OVER(PARTITION BY p.PID ORDER BY t.TID DESC)
FROM Project p
INNER JOIN Task t ON p.PID = t.PID
) d
WHERE d.row_no = 1
[/code]

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

hbeasley
Starting Member

4 Posts

Posted - 2009-07-25 : 12:07:47

Thank you, KH. Now it is giving an error that row_number is not a recognized function.

Harriet

hbeasley
Starting Member

4 Posts

Posted - 2009-07-27 : 12:58:09

An Update: I found out that Row_Number is not a valid command in SQL Reporting Services 2000. It is supposed to have a command RowNumber(), but that is not working either...it says it is not a recognized function.

I did find another possible function--CountRow (once I get the count in report, I could do IIF CountRow > 1, "Have Taks". "No Taks") would give me the information I need as well) but it doesn't seem to work either--it is not a recognized function.

Note: The reason I cannot do this in the query is becaues I need to create charts, not just a table.

I appreciate your suggestions and look forward to further suggestions on how to resolve this.

Harriet

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-27 : 13:48:52

[code]
SELECT p.PID, p.Name, p.Inv_Type, t.TID, t.TName
FROM Project p
JOIN Task t
On p.PID = t.PID
JOIN (
SELECT PID, Max(TID) TID
FROM project
) x
On x.PID = p.PID
And x.TID - p.TID;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources