SELECT A.[VENDOR NAME] AS 'VENDOR NAME', A.[VENDOR NUMBER] AS 'VENDOR NUMBER' FROM (SELECT PURCHTABLE.PURCHNAME AS 'VENDOR NAME', PURCHTABLE.ORDERACCOUNT AS 'VENDOR NUMBER', COUNT(DISTINCT PURCHTABLE.PURCHID) AS 'PURCHASE ORDER', COUNT(PURCHLINE.LINENUMBER) AS 'NUMBER OF LINES', SUM(PURCHLINE.LINEAMOUNT) AS 'PO PRICE TOTAL', DATEDIFF(dd, PURCHTABLE.CONFIRMEDDLV, MAX(VENDPACKINGSLIPJOUR.DELIVERYDATE))-(DATEDIFF(wk, PURCHTABLE.CONFIRMEDDLV, MAX(VENDPACKINGSLIPJOUR.DELIVERYDATE)) * 2)-(CASE WHEN DATENAME(dw, PURCHTABLE.CONFIRMEDDLV) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, MAX(VENDPACKINGSLIPJOUR.DELIVERYDATE)) = 'Saturday' THEN 1 ELSE 0 END) AS 'DAYS LATE' FROM PURCHTABLE JOIN PURCHLINE ON PURCHLINE.PURCHID = PURCHTABLE.PURCHID JOIN VENDPACKINGSLIPJOUR ON VENDPACKINGSLIPJOUR.PURCHID = PURCHTABLE.PURCHID WHERE PURCHTABLE.DELIVERYDATE >= '2019-01-01' AND PURCHTABLE.DELIVERYDATE <= '2019-01-20' AND PURCHTABLE.ORDERACCOUNT = 'FDS5834') as A GROUP BY A.[VENDOR NAME], A.[VENDOR NUMBER], A.[PURCHASE ORDER], PURCHTABLE.CONFIRMEDDLV
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.