Skip to Content

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

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

Add a comment
10|10000 characters needed characters exceeded

  • Thank you for visiting SAP Community to get answers to your questions. Since you're new in asking questions here, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members.

    Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment).

    Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS . By personalizing your profile with a photo of you, you encourage readers to respond.

    Best regards

    Mynyna

    SAP Community moderator

Related questions

0 Answers

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.