SQLTeam.com | Weblogs | Forums

Subquery Where


I like to bring inventory on hand quantity and consumption quantity into one pivot table or report. Curranty it is 2 SQL scripts and 2 pivot tables.
Database table name is [item ledger entry]. on hand quantity column is [Remaining Quantity], consumption quantity column is [Quantity] then defined by [Entry Type].

When I add WHERE L.[Entry Type] = '5', the remaining quantity become 0.
My script is like this.

SELECT L.[Remaining Quantity], L.[Quantity], L.[Entry Type]
FROM [CRONUS Canada, Inc_$Item Ledger Entry] AS L
WHERE L.[Entry Type] = '5'

Can use subquery? SQL expert please help. Thank you.


Is this for microsoft sql server?

Yes, it is MS SQL server. The system is NAV 2017. Item Ledger Entry table store all transitions, sales, purchase, consumption... When I write where entry type is consumption, all other types are not showing.
Currently I saved them in different files. Then I use Excel lookup function to bring them together in one sheet. It will be nice to have one query include different entry type.
Thank you.

What is this?

[CRONUS Canada, Inc_$Item Ledger Entry]

a table?