Solved

SQL Group Query where money

Posted on 2011-09-25
7
336 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 92

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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