Supplier Perfomance Query

57 Views

Good Afternoon,

I have a query built to identify supplier performance between us and our sister company in Germany. We have a free text field (WDE Prop) were they supply us a date for delivery that normally varies to the delivery date we requested. We are now looking to split the results by product category A, B or C - is this possible?

Or are there any better solutions to measuring supplier perfomance.

Kind regards,

James

SELECT T1.[DocNum] as 'GRPO Number', T1.[DocDate], T1.[CardCode], T1.[CardName], T0.[ItemCode], T0.[Dscription],

[WUK Proposed Date] = T0.[ShipDate],

[WDE Proposed Date] = CONVERT(date, convert(varchar (10), T0.[FreeTxt])),

[Actual Delivery Date] = T0.[ActDelDate],

[Days Difference to WUK]= DATEDIFF(day,T0.ShipDate, T0.[ActDelDate]) ,

[Days Difference to WDE]= DATEDIFF(day,CONVERT(date, convert(varchar (10), T0.[FreeTxt])), T0.[ActDelDate])

FROM PDN1 T0 INNER JOIN OPDN T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE DATEDIFF(day,CONVERT(date, convert(varchar (10), T0.[FreeTxt])), T0.[ActDelDate]) < 1000

and T1.[DocDate] >= Case '[%0]' when '' then '01.01.2001' else '[%0]' end and T1.[DocDate] <= Case '[%1]' when '' then '31.12.2099' else '[%1]' end

and T1.[CardCode] = '[%2]'

Order by T1.[CardCode] , T1.[DocNum]