Solved

Little help on Query Builder Report

  • 29 January 2021
  • 6 replies
  • 336 views
Asela Munasinghe
Rank
Userlevel 7
Badge +19

Since I am not so strong in SQL, I tried query builder to come up with a report with the below columns for Inventory parts

 

Part No

Part Description 

Site = X only 

Created Date (on or after 1-1-2020) 

Has 5 or more transactions 

“ Primary supplier for the purchase part”  with Supplier name and supplier ID (2 columns) 

 

However, for the number of transactions column I am facing a challenge since there is no view for me to chose in query builder. So I omitted that column, but still need the report sorted for parts with only for 5 or more transactions. 

 

How should I do? an advise  with the right SQL statement would also help. 

icon

Best answer by Tj12 29 January 2021, 14:51

View original
Let's connect on LinkedIn: https://www.linkedin.com/in/asela61/
This topic has been closed for comments

6 replies

T
Rank
Userlevel 3
Badge +8

What do you mean by “Has 5 or more transactions” ? You can look up from Inventory Transaction History to get the transaction count 

Asela Munasinghe
Rank
Userlevel 7
Badge +19

What do you mean by “Has 5 or more transactions” ? You can look up from Inventory Transaction History to get the transaction count 

@Tj12 Yes I know, I wanted to get this report only for the parts having 5 or more lines in the Inventory Transaction History for that period.

Is that possible?

Let's connect on LinkedIn: https://www.linkedin.com/in/asela61/
T
Rank
Userlevel 3
Badge +8

@EnzoFerrari 

Not sure about the query builder.  But if you have access to build quick reports or SQL query tool below will give the data you want 

I also could not find an exact view relate to inv transaction his in query builder. hope someone will comment about that

 

select t1.PART_NO,
       t1.DESCRIPTION,
       t1.CONTRACT,
       t2.VENDOR_NO,
       SUPPLIER_API.Get_Vendor_Name(VENDOR_NO) as "Supplier Name",
       (select Count(TRANSACTION_ID)
          from INVENTORY_TRANSACTION_HIST2 t3
         where t1.part_no = t3.part_no
           and t1.contract = t3.contract) as "Trans Count"
  from INVENTORY_PART t1, PURCHASE_PART_SUPPLIER t2
 where t1.PART_NO = t2.PART_NO
   and t1.CONTRACT = t2.CONTRACT
   and t2.PRIMARY_VENDOR_DB = 'Y'
   and t1.CONTRACT = 'X'
   and (select Count(TRANSACTION_ID)
          from INVENTORY_TRANSACTION_HIST2 t3
         where t1.part_no = t3.part_no
           and t1.contract = t3.contract) >= 5
 

Asela Munasinghe
Rank
Userlevel 7
Badge +19

Thanks @Tj12. Created Date (on or after 1-1-2020) is not included in it right?

Let's connect on LinkedIn: https://www.linkedin.com/in/asela61/
T
Rank
Userlevel 3
Badge +8

Thanks @Tj12. Created Date (on or after 1-1-2020) is not included in it right?

 

Try This 

 

select t1.PART_NO,
       t1.DESCRIPTION,
       t1.CONTRACT,
       t2.VENDOR_NO,
       SUPPLIER_API.Get_Vendor_Name(VENDOR_NO) as "Supplier Name",
       (select Count(TRANSACTION_ID)
          from INVENTORY_TRANSACTION_HIST2 t3
         where t1.part_no = t3.part_no
           and t1.contract = t3.contract) as "Trans Count",
        t1.create_date
  from INVENTORY_PART t1, PURCHASE_PART_SUPPLIER t2
 where t1.PART_NO = t2.PART_NO
   and t1.CONTRACT = t2.CONTRACT
   and t2.PRIMARY_VENDOR_DB = 'Y'
   and t1.CONTRACT = 'X'
   and (select Count(TRANSACTION_ID)
          from INVENTORY_TRANSACTION_HIST2 t3
         where t1.part_no = t3.part_no
           and t1.contract = t3.contract) >= 5
   and To_char(t1.create_date,'YYYY-MM-DD') >= '2020-01-01'

Asela Munasinghe
Rank
Userlevel 7
Badge +19

Thank You very much

Let's connect on LinkedIn: https://www.linkedin.com/in/asela61/
Terms & ConditionsCookie settings