Solved

SQL Group Query where money

Posted on 2011-09-25
7
323 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
Comment Utility
select JobChargeId, JobId, Rate from JobCharges
where JobChargeId = (select max(JobChargeId) from JobCharges where jobid = 4006223)

0
 

Author Comment

by:Lapchien
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Lapchien
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now