Hello, I am using SQL server mgmt studio 2008 r2 and I have a situation where I joined two views to get just one field where it is calculation based on conditions.
I have the following sample SQL statement where I get red squiggly syntax error underlined to get the field from the other view:
In the select, I got this:
Isnull((CASE WHEN bsd.StatusID= 01 AND bsd.TypeID = 10 THEN bsd.Amt * bsd.Qty ELSE 0 END ),0) as TotalLineItemAmt,
In the from statement, I got this:
INNER JOIN dbBillsAndInvoices..StatementDetails bsd ON bsd.StatementID = b.StatementID
The view, which is the using the inner join above, returns 49 rows which I know for sure the StatementDetails table returns 9 rows that are confirmed as true known fact.
How would I make the statement above define only 9 rows and not 49 rows using a similar Count Distinct or something valid?