Skip to Content
0
Jun 03, 2022 at 08:54 PM

make this quote query more eiffiecnet if possible

67 Views

I was wondering if this code could be cleaned up and be more efficient

SELECT INSIDE.[Sales Employee Name], 
INSIDE.[BP Code], INSIDE.[BP Name], 
INSIDE.[Posting Date], INSIDE.[Valid Until], 
INSIDE.[Quote Number],
INSIDE.[Quote Status O], 
INSIDE.[Quote Status C], 
INSIDE.[Original Amount],
INSIDE.[Closed Amount],
INSIDE.[Percent Closed] AS '% Closed'

FROM
(
--Open Column
SELECT T3.[SlpName] AS [Sales Employee Name], 
T2.[CardCode] AS [BP Code], T2.[CardName] AS [BP Name], 
T0.[DocDate] AS [Posting Date], T0.[DocDueDate] AS [Valid Until], 
T0.[DocNum] AS [Quote Number], 
T0.[DocStatus] AS [Quote Status O], 
'' AS [Quote Status C], 

(SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry) AS [Original Amount],
(SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry AND T11.[LineStatus] = 'C') AS [Closed Amount],

FORMAT((SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry AND T11.[LineStatus] = 'C') 
/
(SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry) ,'P')
AS [Percent Closed]

FROM OQUT T0  
INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] 
INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode] 
WHERE T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]'
AND T0.[DocStatus] = 'O'

--Closed Column
UNION ALL

SELECT T3.[SlpName] AS [Sales Employee Name], 
T2.[CardCode] AS [BP Code], T2.[CardName] AS [BP Name], 
T0.[DocDate] AS [Posting Date], T0.[DocDueDate] AS [Valid Until], 
T0.[DocNum] AS [Quote Number], 
'' AS [Quote Status O], 
T0.[DocStatus] AS [Quote Status C], 


(SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry) AS [Original Amount],
(SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry AND T11.[LineStatus] = 'C') AS [Closed Amount],

FORMAT((SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry AND T11.[LineStatus] = 'C') 
/
(SELECT SUM(T11.[LineTotal]) 
FROM QUT1 T11
WHERE T11.DocEntry = T0.DocEntry) ,'P')
AS [Percent Closed]

FROM OQUT T0  
INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] 
INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode] 
WHERE T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]'
AND T0.[DocStatus] = 'C'
) INSIDE
ORDER BY INSIDE.[Sales Employee Name], 
INSIDE.[BP Code], INSIDE.[BP Name], 
INSIDE.[Posting Date], INSIDE.[Valid Until], 
INSIDE.[Quote Number]