Distinct and order by don't work.

[Former Member]

Hello,

I need dates from all customers incl. faxnumbers which has orderd in the last 4 months. But I it shouldn't show double dates. How can it solved?http://www.dict.cc/englisch-deutsch/double.html

i tried this:

SELECT distinct T0.[Cardcode], T0.[CardName], T0.[DocNum], T0.[GroupNum], T0.[TaxDate], T1.[Fax] 
FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE (T0.[TaxDate] >= CAST(MONTH(DATEADD(m, - 4, GETDATE())) AS varchar) + '.1. ' + CAST(YEAR(DATEADD(m, - 4, GETDATE())) AS varchar))

And this:

SELECT T0.[Cardcode], T0.[CardName], T0.[DocNum], T0.[GroupNum], T0.[TaxDate]
FROM ORDR t
join ( select Fax from OCRD T1 group by T1.[Fax]) i
ON T0.CardCode = T1.CardCode 
WHERE (T0.[TaxDate] >= CAST(MONTH(DATEADD(m, - 4, GETDATE())) AS varchar) + '.1. ' + CAST(YEAR(DATEADD(m, - 4, GETDATE())) AS varchar))
Group by T0.[Cardcode]

[Former Member · Accepted solution]

Hello,

soo, I tryed many times and I guess this work correct (with more functions):

SELECT T0.[CardCode], T0.[CardName], T1.[Fax], count(T0.CardCode) as Bestellungen

FROM OINV T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

WHERE (T0.[TaxDate] >= CAST(MONTH(DATEADD(m, - 4, GETDATE())) AS varchar) + '.1. ' + CAST(YEAR(DATEADD(m, - 4, GETDATE())) AS varchar)) and

T0.[DocTotal]  > [%DocTotal] and T0.[Groupnum] = 4

Group by T0.[CardCode], T0.[CardName], T1.[Fax]

Having Count(*) > 1

[kothandaraman_nagarajan · Accepted solution]

Hi Peter,

That's great. But your initial query is  different from the above.

The count function and doc total condition did the trick.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

This query is not the same as what you had asked in the first place. Please close your thread.

[kothandaraman_nagarajan]

Hi,

You can't avoid getting double tax document date in your query result. Because, there  are possibility adding more documents on the same date.

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

Try:

SELECT T0.[Cardcode], T0.[CardName], T0.[DocNum], T0.[GroupNum], T0.[TaxDate], T1.[Fax]

FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

WHERE DateDiff(dd,T0.[TaxDate], GETDATE())<122

However, if you have multiple fax for a customer, the duplication is not avoidable.

Thanks,

Gordon

[KennedyT21]

Hi Peter Eschweg...

Can You explain clear what you mean by double dates...

SELECT T0.[Cardcode], T0.[CardName], T0.[DocNum], T0.[GroupNum], Max(T0.[TaxDate])

FROM ORDR t

join ( select Fax from OCRD T1 group by T1.[Fax]) i

ON T0.CardCode = T1.CardCode

WHERE (T0.[TaxDate] >= CAST(MONTH(DATEADD(m, - 4, GETDATE())) AS varchar) + '.1. ' + CAST(YEAR(DATEADD(m, - 4, GETDATE())) AS varchar))

Group by T0.[Cardcode], T0.[CardName], T0.[DocNum]

Try This

Regards

Kennedy