Left Join and Most Recent Date - Problem

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)

Left Join and Most Recent Date - Problem

Author

Topic

gingergino
Starting Member

10 Posts

Posted - 2009-05-04 : 12:46:05

I have multiple tables. One of the tables maintains an sales history. Hence, everytime there is a sale it is recorded in the table. So, a customer (table 1) may have zero or more orders (table 2). Table 2 is the left joined table.

How can I extract the most recent order date from a table that was left joined? Ultimately, I want all customer names and the last date an order was placed. If no, was placed by a customer, I still want the customer's name to be printed and a blank in the last order column.

Example Query: Select Customer.customer_name, Sales.last_order from Customer left join Sales on Customer.customer_id = Sales.customer_id where sales.sales_person_id = 5

This query produces all the information, but has ALL customer orders.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-04 : 12:57:03

[code]Select
Customer.customer_name,
Sales.last_order
from
Customer
left join Sales on Customer.customer_id = Sales.customer_id
join ( select
customer_id,
max(sale_date) maxdate
from
Sales
group by
customer_id)maxc on maxc.maxdate=Sales.sale_date and maxc.customer_id=customer_id
where
sales.sales_person_id = 5[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-04 : 13:04:11

[code]
SELECT
Customer.customer_name,
last.last_order
FROM
Customer
left join
( select
customer_id
,last_order
,[Rank] = row_number() over(partition by cutomer_id order by last_order desc)
from sales
where sales_person_id = 5
)last
WHERE
last.[rank] = 1

[/code]

Jim

gingergino
Starting Member

10 Posts

Posted - 2009-05-04 : 13:11:17

Thank you! I'll try these options.

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 02:27:31

Ok, let me try this again. I could not get the previous suggestions to work. So, I'll post my actual query and problem.

Background: I am developing a report that returns the Tasks assigned to an individual. The report prints out the Project Name and the status of all Assigned Task. Multiple tasks can be assigned to one project.

The application maintains an UpdateHistory table. It keeps track of all updates provided by the person assigned the tasks (Taskee). The report should print out all the Task information and it should also provide the last date a task update was provided. So, the UpdateHistory table may have zero or more updates for a specific task.

My actual query is below. The query below returns all the data for the report. But it needs modifying so that the LATEST DATE is returned. The latest date is maintained in the update_date field.

The problem with the query below, is that it returns ALL updates provided by the taskee for a task. I only need the latest date.

Select Project.proj_title, Tasks.task_title, Tasks.end_date, Tasks.status, UpdateHistory.update_date, Tasks.last_remind_time from ((Tasks left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id) left join Project on Tasks.proj_id=Project.proj_id) left join UpdateHistory on Tasks.task_id =UpdateHistory.task_id where Task2TeamLeagur.member_id=10 order by Project.proj_id desc;

Thank you for your assistance.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 08:05:05

what from the previous post didn't work for you.

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 08:47:54

When I tried your sakets_2000 recommendation, it produced the same results as the initial query.

By the way, I would like to compensate you if you can get the query to work.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 09:11:50

this works ?

Select 
	Project.proj_title, 
	Tasks.task_title, 
	Tasks.end_date, 
	Tasks.status, 
	UpdateHistory.update_date, 
	Tasks.last_remind_time 
from 
	
	Tasks 
	left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id
	left join Project on Tasks.proj_id=Project.proj_id
	left join (select task_id,max(last_remind_time) as last_remind_time from UpdateHistory group by task_id)UpdateHistory on Tasks.task_id =UpdateHistory.task_id 
where 
	Task2TeamLeagur.member_id=10 
order by 
	Project.proj_id desc

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-05 : 09:34:20

What errors did my query produce?

Jim

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 13:50:08

sakets_2000

I tried the newest query, but it yielded no results. I do not know the specific error because I am using an ASP.NET Enterprise Manager app. It does not show an error, only produces no results.

Here is what I did.
1) I initially performed a cut and paste and nothing was returned.
2) Next, I inserted parenthesis around the join statements and nothing was returned
3) I extracted the select task_id... after the last join and the query remaining query ran, but produced results with all update_date history
4) I pasted only the select task_id... and noticed that "max(last_remind_time)" does not exist. I changed it to "max(update_date)". This time the smaller code segment returned results.
5) I reinserted the select statement into the overal query and it produced no results.
See modified query below. ONly changes were parenthesis and max column reference.

Select Project.proj_title,Tasks.task_title, Tasks.end_date,Tasks.status, UpdateHistory.update_date, last_remind_time from ((Tasks left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id) left join Project on Tasks.proj_id=Project.proj_id) left join (select task_id,max(update_date) as last_remind_time from UpdateHistory group by task_id) UpdateHistory on Tasks.task_id = UpdateHistory.task_id
where Task2TeamLeagur.member_id=10 order by Project.proj_id desc

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 13:51:37

quote:
Originally posted by jimf

What errors did my query produce?

Jim

Your recommendation returned no results. It was probably because of an error I made. I do not fully understand the syntax of the rank, over partition, etc. I probably need to investigate it more.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 14:28:23

does this return you records ?

Select 
	Project.proj_title, 
	Tasks.task_title, 
	Tasks.end_date, 
	Tasks.status, 
	UpdateHistory.update_date, 
	Tasks.last_remind_time 
from 
	
	Tasks 
	left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id
	left join Project on Tasks.proj_id=Project.proj_id
	left join (select task_id,max(update_date) as last_remind_time from UpdateHistory group by task_id)UpdateHistory on Tasks.task_id =UpdateHistory.task_id

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 16:43:18

Unfortunately, the same result. It does not seem to like the second sql statement. Although, I can remove it from the main query and it runs separately. But it does not run with the main query.

I can send you the data tables if you like. Again, I will compensate you for your time.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 16:49:26

what I posted before should display records as long as tasks table has records in it.
"It does not seem to like the second sql statement".. you mean this bit "left join (select task_id,max(update_date) as last_remind_time ...",, right ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 16:58:54

OK, lets go step by step, Can you post which ones return you records from these 3 queries..

--1
Select 
	Project.proj_title, 
	Tasks.task_title, 
	Tasks.end_date, 
	Tasks.status, 
	UpdateHistory.update_date, 
	Tasks.last_remind_time 
from 
	
	Tasks 
	left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id
	left join Project on Tasks.proj_id=Project.proj_id
	left join (select task_id,max(update_date) as last_remind_time from UpdateHistory group by task_id)UpdateHistory on Tasks.task_id =UpdateHistory.task_id
where 
	Task2TeamLeagur.member_id=10 
--2
Select 
	Project.proj_title, 
	Tasks.task_title, 
	Tasks.end_date, 
	Tasks.status, 
	UpdateHistory.update_date, 
	Tasks.last_remind_time 
from 
	Tasks 
	left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id
	left join Project on Tasks.proj_id=Project.proj_id
	left join UpdateHistory on Tasks.task_id =UpdateHistory.task_id 
where 
	Task2TeamLeagur.member_id=10 
--3
 select task_id,max(update_date) as last_remind_time from UpdateHistory group by task_id

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 17:26:11

Queries 2 and 3 return data

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 18:15:41

[code]Select
Project.proj_title,
Tasks.task_title,
Tasks.end_date,
Tasks.status,
UpdateHistory.update_date,
Tasks.last_remind_time
from

Tasks
left join Task2TeamLeagur on Tasks.task_id = Task2TeamLeagur.task_id
left join Project on Tasks.proj_id=Project.proj_id
left join (select task_id,max(update_date) as update_date from UpdateHistory group by task_id)UpdateHistory on Tasks.task_id =UpdateHistory.task_id
where
Task2TeamLeagur.member_id=10 [/code]

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 18:54:57

Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Yes!Y

Do you have a Paypal Account? My email address is bsharris@iwatch-it.com

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 18:57:20

ah no, don't bother, thanks :)

gingergino
Starting Member

10 Posts

Posted - 2009-05-05 : 19:13:58

I really would like to pay you. Please tell me how I could do it. Please use my email address.

Do you do any other work (.NET)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources