select
[PartTran].[PartNum] as [PartTran_PartNum],
(case when PartTran.TranType = 'MFG-STK' then MAX(PartTran.SysDate) end) as [Calculated_StkMfg],
(case when PartTran.TranType = 'STK-CUS' then MAX(PartTran.SysDate) end) as [Calculated_StkCust],
[PartQty].[OnHandQty] as [PartQty_OnHandQty]
from Erp.PartTran as PartTran
inner join Erp.PartCost as PartCost on
PartTran.Company = PartCost.Company
And
PartTran.PartNum = PartCost.PartNum
inner join Erp.PartQty as PartQty on
PartCost.Company = PartQty.Company
And
PartCost.PartNum = PartQty.PartNum
where (PartTran.TranType = 'MFG-STK' or PartTran.TranType = 'STK-CUS')
group by PartTran.PartNum,
PartQty.OnHandQty,
PartTran.TranType
order by PartTran.PartNum
It’s the tran type in your grouping that’s messing it up. If you have it to group by that, it will have a line for each type. If you want them on the same line, you can’t group by that.
The quick and dirty way. Make the query you have a innerquery. Add a new top query and reference it. Then you need to select the partNum field of the subquery. Then you will need to add the other fields as calculated fields.
You could also create two separate queries and then join by partnum. That would be three total queries instead of two. Your choice.
select NewList.[PartTran_PartNum], Max([Calculated_StkMfg]) AS [Calculated_StkMfg], MAX([Calculated_StkCust]) as [Calculated_StkCust], SUM([PartQty_OnHandQty])
from (select
[PartTran].[PartNum] as [PartTran_PartNum],
(case when PartTran.TranType = ‘MFG-STK’ then MAX(PartTran.SysDate) end) as [Calculated_StkMfg],
(case when PartTran.TranType = ‘STK-CUS’ then MAX(PartTran.SysDate) end) as [Calculated_StkCust],
[PartQty].[OnHandQty] as [PartQty_OnHandQty]
from Erp.PartTran as PartTran
inner join Erp.PartCost as PartCost on
PartTran.Company = PartCost.Company
And
PartTran.PartNum = PartCost.PartNum
inner join Erp.PartQty as PartQty on
PartCost.Company = PartQty.Company
And
PartCost.PartNum = PartQty.PartNum
where (PartTran.TranType = ‘MFG-STK’ or PartTran.TranType = ‘STK-CUS’)
group by PartTran.PartNum,
PartQty.OnHandQty,
PartTran.TranType
) as NewList
group by NewList.[PartTran_PartNum]
order by NewList.[PartTran_PartNum]
I made the three queries, filter the 2 subs by tran-type and just did max(date) for each sub, group by part number. Then brought in the sub queries to the top level and joined the three tables (like Ken suggested).
The case statements were throwing the group by error with the tran type, so it’s easier (for me) just to filter the parttran table by tran type and do a simple max date there.
Sub queries of this type really are pretty easy to use, and very powerful.
Let us know if you need a step by step walk through. It’s really very simple in the BAQ wizard. Don’t think of them as scary sub queries. Just think of them like another table that you’ve formatted/filtered to what you want. (until you get to union and CTE’s, those can be tricky)
The third subquery is giving me trouble. You say, “Repeat with the second subquery, just like the first one we did, but filter by STK-CUS instead.”
I’m unclear how to create the third subquery. If you could go over what steps I need to do and the order in which to do them that would be most helpful. Oh, and by the way I am extremely familiar with the BAQ Wizard/Designer as this is how I have been creating all my BAQ’s. I just wasn’t familiar with calling it a Wizard.
When I try to create the third subquery I’m trying to put the PartTran table in it, but I get the error that it has already been used.
Hope you had a great weekend…and thank you for all your help!
I’m getting so close…I’m getting an error, “The multi-part identifier “PartTran.SysDate” could not be bound”. Could you tell me what this error means and what I’m doing wrong.
Otherwise, I feel that I have a better understanding of the Subqueries…THANK YOU SO MUCH!
I pretty much have it, and I agree that 'a little struggle" helps!
I am having trouble now…Well, I added another subquery, that looks for the ‘STK-MTL’ transaction. When I look at the dates of the actual transactions in Part Transaction History Tracker, I am not getting the correct dates. So I decided I would add to the calculations a check for the correct TranType:
Example: (CASE when PartTran.TranType = ‘MFG-STK’ then max( PartTran.SysDate )END
Now I’m getting the following error:
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “PartTran.TranType” could not be bound.
The multi-part identifier “PartTran.SysDate” could not be bound.
