Hi, I am working with a large query and I'm having trouble with duplicates when doing grouping.
Data looks like this:
ORDER NUMBER LINE NUMBER PROMISE DATE REVENUE COST
ORD123 1 5/1/2007 100.00 50.00
ORD123 2 NULL 200.00 100.00
Trying to group the data at the order header level. However, this promise date field is causing me a lot of problems. I tried doing MAX() on the promise date, but it yelled at me for having NULLs and it basically didn't do anything.
I then wrote a case statement to put 12/31/1900 as the date if the date was null. Then I wrote a second query off that to pull the max() again...it still returned 2 rows...
What am I doing wrong and how do I get it to group this?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Well I have tried writing the query like this:
SELECT ORDER_NO, MAX(PROMISE_DT) , SUM(REVENUE), SUM(COST)
FROM OPENORDERS
GROUP BY ORDER_NO
And it still returns 2 rows for the ones that have multiple promise dates... the max isn't working.
Are you saying just write my query normally and just do the inner join you wrote?
If you do want the SUM of cost and revenue your query should work as long as the ORDER_NO is the same. Is it possible that one ORDER_NO value contains some non visible characters. Just for testing try:
SELECT ORDER_NO, MAX(PROMISE_DT) , SUM(REVENUE), SUM(COST)
FROM OPENORDERS
WHERE ORDER_NO = 'ORD123'
GROUP BY ORDER_NO
AND
SELECT LTRIM(RTRIM(ORDER_NO)) AS ORDER_NO, MAX(PROMISE_DT) , SUM(REVENUE), SUM(COST)
FROM OPENORDERS
GROUP BY LTRIM(RTRIM(ORDER_NO))
I get this error message when I try daletian's solution:
Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.
And for Ksaul,
The Order number is infact unique and has no hidden values or spaces... even if I substitute '12/31/1900' for the null values in the promise date field, it still does not return the max of the field.
I'm wondering if Max does not work on certain data typed fields.
I assume PROMISE_DT is a datetime field. MAX should work on datetime fields, and I've just been doing some testing here and it seems to be working. If the dates are all null, I even get null as the max. I haven't seen any multiple results.
I don't see anything wrong with the SELECT you posted.
Based on my tests, replacing the null dates doesn't seem to be necessary, but, FWIW, if I was going to do it, I'd use something like:
SELECT ORDER_NO, MAX(IsNull (PROMISE_DT, CONVERT (datetime, '12/31/1990', 101))), SUM(REVENUE), SUM(COST)
FROM OPENORDERS
GROUP BY ORDER_NO
FWIW, I don't think you'd want to use daletian's suggestion, because it looks like it's trying to exclude the rows with the other dates and only include the ones with the MAX date.
James
Business Accounts
Answer for Membership
by: ksaulPosted on 2007-05-22 at 07:13:19ID: 19134049
You also have different Revenue and Cost data for each row and if you want to retrieve those along with the max promise date you would have to get the aggregate in a subquery and then select the orders that match those dates. Like:
SELECT *
FROM YourOrderTable
INNER JOIN (SELECT [ORDER NUMBER], MAX([PROMISE DATE]) AS MaxDate
FROM YourOrderTable
GROUP BY [ORDER NUMBER]) AS MaxDates
ON YourOrderTable.[ORDER NUMBER] = MaxDates.[ORDER NUMBER] AND YorOrderTable.[PROMISE DATE] = MaxDates.MaxDate