Solved

SQL Group Query where money

Posted on 2011-09-25
7
331 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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