Need help with why min function not working

[satinsilhouette]

Hi I have this code, and generally the min function works, but it is not picking up the minimum date as requested. Can someone help. The from clause and where clause do not matter. And there is a group clause, which should not matter right now.

This is the piece that is not working. I have tried it with To_Date to force it to a date and without, still will not pick up minimum due date.


Activity.COMPANY, MHSTHDR.CUSTOMER, CUSTDESC.R_NAME, Activity.ACCT_UNIT,
PRODDTL.CONTRACT, ACCNTRACT.DESCRIPTION AS "Activity Desc", PRODHDR.CONT_DATE,
MHSTHDR.INVOICE_DATE AS TRANS_DATE, MHSTHDR.INVOICE AS "Prod Invoice", PRODDTL.PRODUCT,
PRODDTL.USERS, MHSTHDR.CURRENCY_CODE, PRODDTL.LIST_PRICE, PRODDTL.DISCOUNT_AMT,
PRODDTL.NET_PRICE, PRODDTL.PREV_EXP_AMT, PRODDTL.TOTAL_COST, PRODDTL.CGSX_ACCOUNT,
PRODDTL.CGSX_SUB_ACCT, CUSTDESC.ADDR1, CUSTDESC.ADDR2, CUSTDESC.ADDR3, CUSTDESC.ADDR4,
CUSTDESC.CITY, CUSTDESC.STATE, CUSTDESC.ZIP, CUSTDESC.COUNTRY, PRODDTL.SITE_LOCATION,
Activity.POSTING_FLAG, MIN(To_DATE(MHSTHDR.DUE_DATE)) AS DUEDATE

Thanks!

Thanks so much!
satinsilhouette

 

[bborissov]

It works, but maybe it is NOT what you want.
With so many fields that are used in GROUP BY clause, I thing exactly what you asked for, not what you want .

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[satinsilhouette]

Thank you for your kind answer, but that doesn't help me solve it.

Thanks so much!
satinsilhouette

 

[bborissov]

It doesn't because I don't know what you want.
Maybe, just maybe, if you post some example data and desired result from that data, I could help you somehow.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Maybe? I bet if there was sample data and expected results, boris would easily be able to help.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I bet too. Boris, if the OP posts and you would NOT be able to solve it, me and George will owe you.

 

[bborissov]

If I am not asleep I'll try
(One hour to midnight here )))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[satinsilhouette]

You guys are funny, the code was posted. results expected maybe not.

I even shortened the code to just contract and date, but didn't get the minimum of the date(s) for the contract:

SELECT CONTRACT, MIN(TO_DATE(DUE_DATE)) AS DUE_DATE
FROM MHSTHDR
WHERE (CONTRACT = '72116P01 ')
GROUP BY CONTRACT, DUE_DATE
ORDER BY DUE_DATE

result set has 5 dates in it

9/30/3009
10/31/2009
11/30/2009
12/31/2009
1/31/2010

I should only be getting 9/30/2009 and its contract number back.

Thanks again!

Thanks so much!
satinsilhouette

 

[markros]

I don't understand why do you include Due_Date into GROUP BY condition. Exclude it. Also, what database you're using - Access? SQL Server doesn't have To_DATE function, AFAIK.

 

[gmmastros]

Maybe it's a typo, but your 9/30 date has a year = [!]3009[/!]

That almost a thousand years in the future, and certainly doesn't occur earlier than those other [!]2009[/!] and [!]2010[/!] dates.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snitin78]

Is this what you are looking for?

declare @table1 table (contract_id int, due_date datetime)

insert into @table1 values (1, 'Sep 30 2009')
insert into @table1 values (1, 'Oct 1 2009')
insert into @table1 values (1, 'Oct 15 2009')
insert into @table1 values (2, 'Oct 1 2009')
insert into @table1 values (2, 'Oct 15 2009')

select contract_id, due_date
from @table1 a
group by contract_id, due_date
having due_date = (select min(due_date) from @table1 where contract_id = a.contract_id)
order by due_date

RESULT:

1	2009-09-30 00:00:00.000
2	2009-10-01 00:00:00.000

 

[satinsilhouette]

Yes Thanks that was a type on 3009.

Can the having be switched to a where? And how can I incorporate that into the original query?

Thanks so much!
satinsilhouette

 

[satinsilhouette]

Okay snitin78 - almost, I should only be getting 9/30/2009 back as the result.

Thanks so much!
satinsilhouette

 

[satinsilhouette]

And Oracle 10g is the database

Thanks so much!
satinsilhouette

 

[markros]

declare @table1 table (contract_id int, due_date datetime)

insert into @table1 values (1, 'Sep 30 2009')
insert into @table1 values (1, 'Oct 1 2009')
insert into @table1 values (1, 'Oct 15 2009')
insert into @table1 values (2, 'Oct 1 2009')
insert into @table1 values (2, 'Oct 15 2009')

select Contract_ID, MIN(due_date) from @table1 where contract_ID = 1 group by Contract_ID

As I said, exclude it from GROUP BY.

 

[satinsilhouette]

Markos,

That doesn't work. I still get the 5 records back. And it won't let me take the Due_Date out of the group by, it sqwaks.

Thanks so much!
satinsilhouette

 

[gmmastros]

This is part of your problem:

[tt][blue]
SELECT CONTRACT, MIN(TO_DATE(DUE_DATE)) AS DUE_DATE
FROM MHSTHDR
WHERE (CONTRACT = '72116P01 ')
GROUP BY CONTRACT, [!]DUE_DATE[/!]
ORDER BY DUE_DATE
[/blue][/tt]

You are including the Due_date in the group by. This means that for every combination of contract AND Due_Date, return the contract and due_date. What you really want is.... for every contract, return the minimum due_date. To fix this problem, remove the due_date from the group by clause.

SELECT     CONTRACT, MIN(TO_DATE(DUE_DATE)) AS DUE_DATE
FROM         MHSTHDR
WHERE     (CONTRACT = '72116P01       ')
GROUP BY CONTRACT
ORDER BY DUE_DATE

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snitin78]

If he removes the due_date from the group by clause then how can it be there in the select?

It will throw an error. No?

 

[satinsilhouette]

If I take Due Date out of the group by, this query won't run and gives the error it is missing an expression in the group by clause.

Here is the actual, full, query:

SELECT ACTIVITY.COMPANY, MHSTHDR.CUSTOMER, CUSTDESC.R_NAME, ACTIVITY.ACCT_UNIT,
PRODDTL.CONTRACT, CNTRACT.DESCRIPTION AS "Activity Desc", ACPRODHDR.CONT_DATE,
MHSTHDR.INVOICE_DATE AS TRANS_DATE, MHSTHDR.INVOICE AS "Prod Invoice", PRODDTL.PRODUCT,
PRODDTL.USERS, MHSTHDR.CURRENCY_CODE, PRODDTL.LIST_PRICE, PRODDTL.DISCOUNT_AMT,
PRODDTL.NET_PRICE, PRODDTL.PREV_EXP_AMT, PRODDTL.TOTAL_COST, PRODDTL.CGSX_ACCOUNT,
PRODDTL.CGSX_SUB_ACCT, CUSTDESC.ADDR1, CUSTDESC.ADDR2, CUSTDESC.ADDR3, CUSTDESC.ADDR4,
CUSTDESC.CITY, CUSTDESC.STATE, CUSTDESC.ZIP, CUSTDESC.COUNTRY, PRODDTL.SITE_LOCATION,
ACTIVITY.POSTING_FLAG, MIN(MHSTHDR.DUE_DATE) AS DUEDATE
FROM PRODDTL RIGHT OUTER JOIN
ACTIVITY RIGHT OUTER JOIN
ACPRODHDR INNER JOIN
MHSTHDR ON ACPRODHDR.CONTRACT = MHSTHDR.CONTRACT ON
ACTIVITY.ACTIVITY = ACPRODHDR.ACTIVITY ON PRODDTL.CONTRACT = ACPRODHDR.CONTRACT LEFT OUTER JOIN
ACPRODLSTX ON PRODDTL.PRODUCT = ACPRODLSTX.PRODUCT LEFT OUTER JOIN
CNTRACT INNER JOIN
CUSTDESC ON CNTRACT.CUSTOMER = CUSTDESC.CUSTOMER ON
ACPRODHDR.CONTRACT = CNTRACT.CONTRACT
WHERE (ACTIVITY.COMPANY IN COMPANY)) AND (ACPRODLSTX.R_STATUS = 'A') AND (MHSTHDR.INVOICE_DATE BETWEEN
:StartDate AND :EndDate) AND (MHSTHDR.INVOICE_TYPE <> 'T') AND (TRIM(PRODDTL.PRODUCT) IN PRODUCT))
GROUP BY ACTIVITY.COMPANY, MHSTHDR.CUSTOMER, CUSTDESC.R_NAME, ACTIVITY.ACCT_UNIT,
PRODDTL.CONTRACT, CNTRACT.DESCRIPTION, ACPRODHDR.CONT_DATE, MHSTHDR.INVOICE_DATE,
MHSTHDR.INVOICE, PRODDTL.PRODUCT, PRODDTL.USERS, MHSTHDR.CURRENCY_CODE,
PRODDTL.LIST_PRICE, PRODDTL.DISCOUNT_AMT, PRODDTL.NET_PRICE, PRODDTL.PREV_EXP_AMT,
PRODDTL.TOTAL_COST, PRODDTL.CGSX_ACCOUNT, PRODDTL.CGSX_SUB_ACCT, CUSTDESC.ADDR1,
CUSTDESC.ADDR2, CUSTDESC.ADDR3, CUSTDESC.ADDR4, CUSTDESC.CITY, CUSTDESC.STATE,
CUSTDESC.ZIP, CUSTDESC.COUNTRY, PRODDTL.SITE_LOCATION, ACTIVITY.POSTING_FLAG

Thanks so much!
satinsilhouette

 

[markros]

Do you in reality need the whole record's info for minimum date for each contract?

If yes, see this blog - you would need to adjust it for Oracle:

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related