"Select item.id as itemId,item.fullname as itemName,CUSTOMLISTCUSTOMLISTPSGS_PRDT_SIZ1.name AS itemSize, transaction.tranId,salesInvoiced.tranDate,salesInvoiced.amount As baseprice,salesInvoiced.amountnet AS netSales,salesInvoiced.itemCount, transactionLine.id,transaction.id as transaction,transactionLine.memo as description,item.totalQuantityOnHand,location.name as locationName,CUSTOMRECORD_TLS_LIFE_CAT.name AS productLifeStyleCategory,CUSTOMRECORD_TLS_PRODUCT_CATEGORY.name AS productCategory,CUSTOMRECORD_CSEG1.name AS Inventory_MH1_Division,CUSTOMRECORD_CSEG3.name AS Inventory_MH3_Class,AggregateItemLocation.quantityonhand AS locationLevelInventory,AggregateItemLocation.preferredstocklevel FROM salesInvoiced INNER JOIN transaction ON salesInvoiced.transaction = transaction.id INNER JOIN transactionLine  ON salesInvoiced.uniquekey = transactionLine.uniquekey INNER JOIN item ON item.id = salesInvoiced.item INNER JOIN location ON transactionLine.location = location.id INNER JOIN CUSTOMRECORD_TLS_LIFE_CAT ON item.custitem_tls_lifestyle_category = CUSTOMRECORD_TLS_LIFE_CAT.id INNER JOIN CUSTOMRECORD_TLS_PRODUCT_CATEGORY ON item.custitem_tls_product_category = CUSTOMRECORD_TLS_PRODUCT_CATEGORY.id LEFT JOIN CUSTOMRECORD_CSEG1 ON item.cseg1 = CUSTOMRECORD_CSEG1.id LEFT JOIN CUSTOMRECORD_CSEG3 ON item.cseg3 = CUSTOMRECORD_CSEG3.id INNER JOIN AggregateItemLocation ON ( AggregateItemLocation.Item = TransactionLine.Item ) AND ( AggregateItemLocation.Location = TransactionLine.Location ) LEFT JOIN CUSTOMLISTCUSTOMLISTPSGS_PRDT_SIZ1 ON transactionLine.custcol_tls_size_new = CUSTOMLISTCUSTOMLISTPSGS_PRDT_SIZ1.id  where  salesInvoiced.trandate = TO_DATE('2024-12-31', 'YYYY-MM-DD') AND salesInvoiced.itemCount != 0 AND Item.ItemType = 'InvtPart'"

Could you please assist me in optimizing this query? I need to retrieve records for products that are two years old or more. Additionally, I need to include the base inventory price for each product.

AI generated answer (using internal Slack threads):

To optimize your query and retrieve records for products that are two years old or more, you can add a condition in the WHERE clause to filter out items that have a creation date less than two years from the current date. You can use the

SYSDATE

function to get the current date and subtract two years from it. Here is how you can do it:

sql
...
WHERE salesInvoiced.trandate = TO_DATE('2024-12-31', 'YYYY-MM-DD') 
AND salesInvoiced.itemCount != 0 
AND Item.ItemType = 'InvtPart'
AND item.creationdate <= SYSDATE - INTERVAL '2' YEAR

To include the base inventory price for each product, you need to join the

item

table with the

inventoryitem

table on the

itemId

and select the

baseprice

from the

inventoryitem

table. Here is how you can do it:

sql
...
INNER JOIN inventoryitem ON item.id = inventoryitem.itemId
...
SELECT ..., inventoryitem.baseprice AS baseInventoryPrice
...

Please replace the

...

with the rest of your query. Remember to replace

SYSDATE

and

INTERVAL '2' YEAR

with equivalent functions in your database if they are not supported. Also, replace

creationdate

and

baseprice

with the actual column names in your

item

and

inventoryitem

tables that store the creation date and base price of the items. Please note that this is a general guidance and the actual implementation may vary depending on your database schema and the SQL dialect you are using.