SQL Query report for Active & Inactive BP based on GRPO

Former Member — Thu, 29 Nov 2018 03:16:24 GMT

Hi folks,

I'm new in B1 and SQL so i need some help with this issue. A client asked for a query report for active and inactive BP based on GRPO transaction. The inactive BP report does not fit my client's requirement. There are two requirement;

BP with transaction within a year
BP with no transaction more than a year

What i'm struggling with is the second requirement which i could not understand how to achieve that. It would be great help if you can help me understand this.

Below are the query for what i;ve done so far (active bp within 1 year), i don't know if this even correct.

SELECT distinct getdate() as 'Current Date',T0.[DocDate], T1.[CardCode], T1.[CardName] 
FROM OPDN T0  INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] 
WHERE  (datediff(dd,T0.[DocDate],getdate())) < 365

Additional info, i'm using B1 9.2 PL11.

topic SQL Query report for Active & Inactive BP based on GRPO in Application Development and Automation Discussions

SQL Query report for Active & Inactive BP based on GRPO