VetLink PRO

Connecting Veterinary Professionals with Smarter Solutions

Skip to content
English - Australia
Sign in
Contact us
  • There are no suggestions because the search field is empty.

Clients Who have Not Bought a Product Export

v7,v6

Gives a list of products not purchased since date user can input.

See Create Export to CSV using SQL Query for  information on creating export using query.

SELECT PM."Ref_Number", PM."Name", PM."Class", PM."Buy_Size", pm."Sell_Price_w_Tax", PM."Complex",
(select PS."Cur_Stock" from "Product_Store" PS where PS."Product" = PM."Ref_Number" and "Store" = 1) as "Store_1_SOH",
(select PS."Cur_Stock" from "Product_Store" PS where PS."Product" = PM."Ref_Number" and "Store" = 2) as "Store_2_SOH",
(select max(GLL1."Trn_Date") from "General_Ledger_Log" GLL1
        where GLL1."Prd_Num" = PM."Ref_Number" and GLL1."Trn_Source" = 'C' and GLL1."Inv_Type" <> 4) as "Last_Purchased"
from "Product_Main" PM
where PM."Ref_Number" > 10000001000
and PM."Status" ='A'
and PM."Complex" in (0,2)
and PM."Class" in ('R','M','D')​
and not exists (select * from "General_Ledger_Log" GLL
        where GLL."Prd_Num" = PM."Ref_Number"
        and GLL."Trn_Date" >= :Date/*Not purch From*/
        and (GLL."Trn_Source" = 'C')
        and GLL."Inv_Type" <> 4)
and PM."Ref_Number" < ((select max(PM2."Ref_Number") from "Product_Main" PM2) - 25)
Order by "Ref_Number"