Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

How do I query the most recent status per client ID?

I have two tables:

MasterQuoteRec Table
  QuoteID, Customer Fields

History Table
  Quote, Status, DateCreated Fields

I would presume that I need to join these two tables where (QuoteID = Quote).  I'm only interested in Quotes where the Status = Value (i.e. Status = 'In Shipping').  Since the History table contains a complete history of all status records, I only want the most recent status, based off the date field 'DateCreated'.

How do I write this sql query.  All attempts thus far have returned zero results.
0
dparkes
Asked:
dparkes
  • 5
  • 2
  • 2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
select m.QuoteID, m.Customer, h.Status, h.DateCreated
from MasterQuoteRec m
	inner join History h on m.QuoteID = h.Quote
	(select Quote, max(DateCreated) DateCreated from History
	where h.Status = 'In Shipping'
	group by Quote) hm on m.QuoteID = hm.Quote

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Corrected query
select m.QuoteID, m.Customer, h.Status, h.DateCreated
from MasterQuoteRec m
	inner join History h on m.QuoteID = h.Quote
	inner join (select Quote, max(DateCreated) DateCreated from History
	where h.Status = 'In Shipping'
	group by Quote) hm on m.QuoteID = hm.Quote

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
This query is supposed to process only those records having status ''In Shipping'
Raj
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
ajcheung78Commented:
If you could provide some sample data that would be helpful.

I'm guessing you could do something basic like the following:

SELECT mqr.Quote, mqrStatus, mqrDateCreated
FROM MasterQuoteRec mqr
INNER JOIN
(SELECT Quote, MAX(DateCreated FROM History WHERE Status = 'In Shipping' GROUP BY Quote) h
ON
 h.Quote = mqr.Quote
0
 
ajcheung78Commented:
I see that there is someone that already responded but I made a mistake in mine as well:

SELECT mqr.Quote, mqrStatus
FROM MasterQuoteRec mqr
INNER JOIN
(
SELECT Quote, MAX(DateCreated FROM History WHERE Status = 'In Shipping' GROUP BY Quote
)
 h
ON
 h.Quote = mqr.Quote

Wish there was something where you could tell whether someone was already typing in a comment.
0
 
dparkesAuthor Commented:
After adjusting all the field names I got almost all the errors to go away except on the definition of 'hm'.  So, I think I'm a lot closer.

The part where you have:

inner join History h on m.QuoteID = h.Quote

gives me an error immediately on the parenthesis beginning the (Select ... statement.  I don't know how to create the inner join that way and i'm not sure that it is needed when the joing can be on the results of the select statement.  What would you do from here to acheive the goal.
select m.Quote, m.Customer
from MasterQuoteRec m inner join 
	(select max(h.DateChanged), h.QuoteID 
	 from History h 
	 where h.NewValue = 'In Shipping' 
	 group by QuoteID) hm on m.Quote = hm.QuoteID

Open in new window

SQLError.jpg
0
 
Rajkumar GsSoftware EngineerCommented:
Try this
select m.Quote, m.Customer
from MasterQuoteRec m inner join 
	(select max(h.DateChanged) DateChanged, h.QuoteID 
	 from History h 
	 where h.NewValue = 'In Shipping' 
	 group by QuoteID) hm on m.Quote = hm.QuoteID

Open in new window

0
 
dparkesAuthor Commented:
Thanks so much.  I modified it slightly for more queries I'll need to do where I might have other values than 'In Shipping' returned.  For instance, I might just return the current status of all quotes.  Your solution worked great.  Thanks.
select m.Quote, m.Customer, hm.NewValue
from MasterQuoteRec m inner join 
	(select max(h.DateChanged) DateChanged, h.QuoteID, h.NewValue NewValue
	 from History h 
	 where h.NewValue = 'In Shipping' 
	 group by QuoteID, NewValue) hm on m.Quote = hm.QuoteID

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Welcome
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now