Skip to Content
0
Jul 27, 2020 at 01:24 PM

Alert for new vendors created with accumulated PO of 20K within 6 months

25 Views Last edit Jul 27, 2020 at 06:12 AM 2 rev

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!

SELECT

T0.CardCode,

MAX(T0.Cardname) as Supplier,

T2.CreateDate,

T2.[Notes] as Remarks,

T2.[Country] AS 'Bill-to Country Code',

T2.[U_QualifiedDate],

T2.[U_ReviewDate],

T2.[U_StrategyLevel],

T2.[U_RiskLevel],

T2.[U_Type],

T2.[U_Category],

T2.[U_SupplyService],

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,

T2.CreateDate,

T2.[Notes],

T2.[Country],

T2.[U_QualifiedDate],

T2.[U_ReviewDate],

T2.[U_StrategyLevel],

T2.[U_RiskLevel],

T2.[U_Type],

T2.[U_Category],

T2.[U_SupplyService],

T4.PymntGroup

Order by SUM(T1.[LineTotal]) DESC