Solved

SQL Group Query where money

Posted on 2011-09-25
7
329 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 59

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 59

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error Creating Foreign Keys in SQL Database 7 35
sql Total query 2 27
kill process lock Sql server 9 54
sql 2008 how to table join 2 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

777 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