Subquery Where

Transact-SQL
1

Hello,

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.

2

Welcome,

Is this for microsoft sql server?

3

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.

4

What is this?

[CRONUS Canada, Inc_$Item Ledger Entry]

a table?