Gillian_Pappas2002 (12/18/2013)

---

Hi.

I have the following code which is correctly selecting my data, but it is giving me duplicates and I can't use a distinct, or Max to be able to eliminate the duplicates. Any ideas?

Thanks for all of your help.

G

SELECT

[name]

,[cust_num]

,[co_num]

,[item]

,[inv_num]

,([order_date]) as order_date

,MAX ([promise_date]) as promise_date

,[AvailableDate]

,MAX ([inv_date]) as inv_date

INTO #rptset

FROM @tmp_FullfillOrder

GROUP BY

[name]

,[cust_num]

,[co_num]

,[item]

,[inv_num]

,[order_date]

,[promise_date]

,[AvailableDate]

,[inv_date]

SELECT

[name]

,[cust_num]

,[co_num]

,[item]

,[inv_num]

,[order_date]

,[promise_date]

,[AvailableDate]

,[inv_date]

--9 Days to available - number of days between order date and date aXllocated.

,DATEDIFF(day,[order_date],[availabledate]) as 'DaysToAvailable'

--10 Ship days - number of days from order date to highest invoice date

,DATEDIFF(day,order_date,inv_date) as 'ShipDays'

--11 Reliable? - Yes if allocate date is less than or equal to Orig Promise date, otherwise leave blank

,CASE WHEN

DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0

then 'Yes' else 'No' end as 'Reliable'

,CASE WHEN

DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0

THEN 1 ELSE 0 end as 'ReliableCalc'

FROM #rptset

Partial Output:

name cust_num co_num item inv_num

ANNA GAI 74351 SF00008668 222-63-80SF0000018266

ELLA WANG 74351 SF00008668 222-63-80SF0000018266

JEFFRE VASCOE 74351 SF00008668 222-63-80SF0000018266

UDITH YOUNG 74351 SF00008668 222-63-80SF0000018266

5D TRUCKING 787051 869123 222-63-13SF0000015676

A-1 Movers 70221 SF00012876 222-13-06SF0000017363

A-1 Movers 70221 SF00012876 222-13-10SF0000017363

A-1 Movers 70221 SF00012876 222-13-16SF0000017363

Want:

ANNA GAI 74351 SF00008668 222-63-80SF0000018266

5D TRUCKING 787051 869123 222-63-13SF0000015676

A-1 Movers 70221 SF00012876 222-13-06SF0000017363