Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Group Query where money

Posted on 2011-09-25
7
Medium Priority
?
342 Views
Last Modified: 2012-05-12
I have a table that records each time a 'job' changes the sales value.  I want to pull out the most recent record to show the current sales value of a particular job.

My grouping query returns more than 1 record (where more than 1 record exists) as I cannot sum the Rate field - I only want to show the actual content of the field, not sum all of the them!

select max(JobChargeId) As JobChargeId, JobId, Rate from JobCharges
where jobid = 4006223
group by JobId <and I only ant to show the most recent Rate by maximum JobChargeId>

How can I group on the Rate field withoyt summing it, to return only 1 record?
0
Comment
Question by:Lapchien
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36594988
select JobChargeId, JobId, Rate from JobCharges
where JobChargeId = (select max(JobChargeId) from JobCharges where jobid = 4006223)

0
 

Author Comment

by:Lapchien
ID: 36595022
That works great for single records, but I have multiple JobId's that need to be returned ...

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36595057
Lapchien,

Please provide a few rows of sample data, showing at least a couple of cases, along with the output you'd expect given that sample input.

Patrick
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Lapchien
ID: 36595135
The Jobs table contains unique JobId plus other customer information (name, address etc.).

The JobCharges table creates a new record each time the sales value of a particular job is changed, so it has multiple JobId and different Rate values (It has a unique Id called JobChargeId).

I want to report on the job itself and show the most recent sales value - for multiple jobs.
0
 

Author Comment

by:Lapchien
ID: 36595141
Jobs table:

JobId, CustomerName, Date ...

typical:

4001234, Jones, 24/09/11
4001324, Smith, 25/09/11

JobCharges table:

JobChargeId, JobId, Rate

123, 4001234, 85.00
124, 4001234, 89.50
125, 4001324, 85.00
126, 4001324, 75.00

expected query output:

4001234, 89.50
4001324, 75.00

My query has an inner join between Jobs and a select with max jobchargeid, but it returns multiple records as I have to group on the Rate.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36595223
I would use ranking for this personally, but there are a number of ways to get this.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36595232
Here is an example of the ranking approach I mentioned in my Article (linked above).
SELECT j.JobId, j.CustomerName, j.[Date], /*...,*/ c.Rate
FROM Jobs j
/* Use an OUTER JOIN if there may not be a charge record. */
JOIN (
   SELECT JobId, Rate
        , ROW_NUMBER() 
             OVER(PARTITION BY JobId 
                  ORDER BY JobChargeId DESC) RN
   FROM JobCharges
) c ON c.JobId = j.JobId AND c.RN = 1 -- This is the key.
;

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question