SQL reporting services query question.

armela20

Hi there
I have build a query wchich does not work very well.
I need the following info.
I need to be able to filter on an item fields and view inventory data sold which will include Posted invoices and posted Credit memos.

Here is my query.

SELECT [Cronus USA$Item].No_, [Cronus USA$Item].[Item Category Code], [Cronus USA$Sales Invoice Line].Quantity,
[Cronus USA$Sales Cr_Memo Line].Quantity AS CM_Qty, [Cronus USA$Sales Cr_Memo Line].[Unit Cost (LCY)] AS [CM-Cost],
[Cronus USA$Sales Invoice Line].[Unit Cost (LCY)], [Cronus USA$Sales Invoice Line].Amount,
[Cronus USA$Sales Cr_Memo Line].Amount AS Cm_Amount
FROM [Cronus USA$Item] LEFT OUTER JOIN
[Cronus USA$Sales Cr_Memo Line] ON
[Cronus USA$Item].No_ = [Cronus USA$Sales Cr_Memo Line].No_ LEFT OUTER JOIN
[Cronus USA$Sales Invoice Line] ON [Cronus USA$Item].No_ = [Cronus USA$Sales Invoice Line].No_
WHERE ([Cronus USA$Sales Invoice Line].Type = 2) AND ([Cronus USA$Sales Cr_Memo Line].Type = 2) AND
([Cronus USA$Sales Cr_Memo Line].[Posting Date] >= @start_date) AND
([Cronus USA$Sales Cr_Memo Line].[Posting Date] <= @end_date) AND
([Cronus USA$Sales Invoice Line].[Posting Date] >= @start_date) AND
([Cronus USA$Sales Invoice Line].[Posting Date] <= @end_date) AND
([Cronus USA$Sales Cr_Memo Line].[Location Code] IN (@location_filter)) AND
([Cronus USA$Sales Invoice Line].[Location Code] IN (@location_filter)) AND
([Cronus USA$Item].[Item Category Code] IN (@item_category_filter))

Sorry, it is too long
but what it gives me is all my sales Lines and the CM info in between.
For example, I have 1 item that has been sold on 8 different Invoices and returned on 1.
I would like to see 9 records for that item.
In return I only get 8.

Any ideas ??

Thanks

fredp1

Hi,

I'm not that familiar with the table structure, but generally speaking, your left outer join is behaving like a equal join... because you have a "where" statement that is filtering data on tables on the "right" hand side of the join.

You basically need to do your filtering in a subquery which is left outer joined to item table.

It would look something like this.

SELECT T1.No_, T1.[Item Category Code], T3.Quantity, T2.CM_Qty, T2.[CM-Cost], T3.[Unit Cost (LCY)], T3.Amount, T2.Cm_Amount
FROM [Lipa Test$Item] as T1
LEFT OUTER JOIN
( SELECT [Lipa Test$Sales Cr_Memo Line].No_, [Lipa Test$Sales Cr_Memo Line].Quantity AS CM_Qty, [Lipa Test$Sales Cr_Memo Line].[Unit Cost (LCY)] AS [CM-Cost], [Lipa Test$Sales Cr_Memo Line].Amount AS Cm_Amount FROM [Lipa Test$Sales Cr_Memo Line]
WHERE ([Lipa Test$Sales Cr_Memo Line].Type = 2)
AND ([Lipa Test$Sales Cr_Memo Line].[Posting Date] >= @start_date)
AND ([Lipa Test$Sales Cr_Memo Line].[Posting Date] <= @end_date)
AND ([Lipa Test$Sales Cr_Memo Line].[Location Code] IN (@location_filter))
) as T2 ON T1.No_ = T2.No_
LEFT OUTER JOIN
( SELECT [Lipa Test$Sales Invoice Line].No_, [Lipa Test$Sales Invoice Line].Quantity, [Lipa Test$Sales Invoice Line].[Unit Cost (LCY)], [Lipa Test$Sales Invoice Line].Amount FROM [Lipa Test$Sales Invoice Line]
WHERE ([Lipa Test$Sales Invoice Line].Type = 2)
AND ([Lipa Test$Sales Invoice Line].[Posting Date] >= @start_date)
AND ([Lipa Test$Sales Invoice Line].[Posting Date] <= @end_date)
AND ([Lipa Test$Sales Invoice Line].[Location Code] IN (@location_filter))
) as T3 ON T1.No_ = T3.No_
WHERE (T1.[Item Category Code] IN (@item_category_filter))

Give this a go.

Fred