Hello, im new into sql, i only know the very basic. Im trying to create an alert for those new vendor with purchases above 20k in the past 6 months or PO count of 5 and above so i can evaluate them. i created a query below. However, i do not know how to include the formula of sum(linetotal) = 20,000 on my filter and also the COUNT(DISTINCT T0.[DocNum]) . also the sum value of line total shows the entire year instead of showing only the 2020. even i already filter the po docdate to 2020. Thanks in Advance for the help!
MAX(T0.Cardname) as Supplier,
T2.[Notes] as Remarks,
T2.[Country] AS 'Bill-to Country Code',
MAX(T4.PymntGroup) as PaymentTerm,
COUNT(DISTINCT T0.[DocNum]) as POCount,
SUM(T1.[LineTotal]) as 'Total Amount SGD'
FROM dbo.OPOR T0
INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OCPR T3 ON T0.CardCode = T3.CardCode
INNER JOIN OCTG T4 ON T0.GroupNum = T4.GroupNum
WHERE T2.[validFor] <> 'N'
AND T2.[U_Category] = 'Monitoring'
AND YEAR(T0.[DocDate]) = '2020'
AND t1.ItemCode <> 'TPT00001'
AND t1.ItemCode <> 'TPT00002'
AND t1.ItemCode <> 'TPT00003'
AND t1.ItemCode <> 'TPT00004'
AND t1.ItemCode <> 'VATCHG01'
AND t1.ItemCode <> 'SVC00001'
AND t1.ItemCode <> 'PACK0001'
AND t1.ItemCode <> 'ADM00001'
GROUP BY T0.CardCode,
Order by SUM(T1.[LineTotal]) DESC