(I’ve attached my BAQ with an attempt at using a CTE after my attempt at inner subquery also stumped me) AshRcptCostsData2.baq (27.5 KB)
I might just be missing the obvious or I’ve found something that can’t be done (yet) inside a BAQ - or possibly some other reason, but I need to see if (and will be buying drinks in Vegas for) anyone can throw me a bone on this one.
Problem/Background ('cause some of y’all are nosy ) - We deal with a lot of steel parts that include ever-changing prices due to tariffs/surcharges/market, so we’ve got a guy adjusting Part costs on a daily basis based on the PO unit cost/Rcvd Qty/weighted avg method every time we receive it into Inspection.<a class=“attachment”
Request - A list of all of the PUR-INS receipts since a given date (and possibly a part#) - with the Inventory Running Total On Hand (from Part Trans History tracker column) and the PartTran.MtlUnitCost - both from the moment just before the PUR-INS transaction happens.
The attached SQL shows a perfectly working query for this solution. But I just can’t get it into a BAQ. The problem is that the “trannum < Max(pt.trannum)” in the where clause of the calculated field sub-selects. I just can’t see who to make that relationship within the CTE or Subquery constructs. AshRcptCostsData2.SQL (1.7 KB)
I know of few of you are trapped beneath the snow (or will be) so here is something to look at instead of work
Can you make the Max(pt.trannum) a subquery, then in the joins, you can just change = to < (you have to type it in. There isn’t any drop down)
Here’s a part of a query that I need more than a 1:1 join. I was able to use two joins with < and >
where (PartDtl1.RequirementFlag = 1)
group by [PartDtl1].[Company],
[PartDtl1].[PartNum],
[PartDtl1].[DueDate],
[DateTable].[Calculated_DateRange],
[DateTable].[Calculated_RowNum],
[Part1].[DaysSupply_c]) as DailySum1 on
DailySum.PartDtl1_PartNum = DailySum1.PartDtl1_PartNum
and DailySum.Calculated_RowNum <= DailySum1.Calculated_RowNum
and DailySum.Calculated_DateRange >= DailySum1.Calculated_RowNum
inner join Erp.Part as Part on
DailySum.PartDtl1_Company = Part.Company
and DailySum.PartDtl1_PartNum = Part.PartNum
and ( Part.TypeCode = 'P' and Part.UsageExclude_c = 0 )
I’m probably not fully understanding the SQL or what you are trying to do, but they look awfully similar. (and to be honest, I didn’t open up your BAQ yet, just took a quick look at your SQL)
For SumQty, you can create a calculated field and use: SUM(TranQty) OVER (ORDER BY PartNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
For PreviousCost: LAG(MtlUnitCost, 1,0) OVER (PARTITION BY PartNum ORDER BY TranNum)
The advantage of Window functions is that you only need a single table scan so they are extremely fast for things like running totals, first record, last record and sums. The formulas that I put in probably will not be what you are actually looking for but should hopefully get you moving in the right direction.
Worst case @hkeric.wcidid a post about use custom User Defined Functions. This would allow you to write a function to get the specific records and you can run it as a single line in the BAQ. Keep in mind, BAQ’s do not actually run the SQL that you think they do since they are using Entity Framework.
-- I would also suggest using the WITH clause to help simplify the -- construction and debugging of your query. -- In this way you can make sure the records you want are coming -- back correctly before you try any of the window functions on them. -- Without the CTE view, it makes coding the query near impossible for me. -- -- For instance -- WITH myparttran AS
(
SELECT company,
partnum,
tranum,
CASE
WHEN acttranqty <> tranqty
AND acttranqty = 0 THEN 0
WHEN LEFT (trantype, 3) = 'STK' THEN -1 * tranqty
ELSE tranqty
END AS tranqty
FROM erp.parttran WITH (nolock)
WHERE inventorytrans = 1
AND trantype IN ('adj-mtl',
'adj-qty',
'dmr-stk',
'ins-stk',
'mfg-stk',
'pur-stk',
'stk-cus',
'stk-ins',
'stk-mtl'))
Why not just call the subquery in a calculated field?
as for the efficiency of the query that is another story. The way you have it written you are doing to in line aggregation per record. That will slow things done a bit. You might want to see if there is a way to remove or reduce that.
Sorry for the delay guys - got me a snow day yesterday and decided not to work from home
@knash - external BAQ and view - good idea and that might be the way I go in the end. I thought of that, but I’m trying to stick with a BAQ so someone else can inherit this who doesn’t know SQL. As for the query inside a calc field - no go. Check Syntax returns an error along the lines of 'Possible Unauthorized query" so I can see that it won’t work that way.
@Dave_Leannah - my first couple of passes at this were using WITH clause, but I couldn’t see the trannum < max(pt.tranum) relationship - but I was using it as a regular subquery in the FROM clause, not as part of a calc’d field subselect like I have it now. I can see how it would simplify the SQL code a bit. Thanks - I’ll give it another try, but I’m still not quite seeing the BAQ side of it. I’ll try it again.
@John_Mitchell & @Banderson - I think I’m following along, but the SumQty calculated field (as a window function) won’t allow for the multiple where clause criteria will it? I cannot just sum up PartTran records without the criteria or I get the wrong total. Am I missing something?
I’m reworking it now to learn about what you both are saying.
Thanks all, and I’ll let you know what I come up with.
You can partition over multiple things, so it basically acts like multiple where clauses. You would probably want to filter out transaction types you don’t want on the table first, so you probably want your running totals in a subquery. Then you can rejoin to the main table later if you need.
Also, if you only need a single value per row, (like a value at a specific date) you can do what I call a “mini sub” where you join the subquery with subquery criteria and bring that value into the table with a calculated field. There’s a good thread where @rbucek explains how to do it. It’s super handy and probably would have some applications to what you are looking for. I would link it, but I’m on the mobile app now, but you should be able to find it.
I do appreciate all the help, but I think I’ve hit the limit of the BAQ editor. I spent the last 3 hours reading/searching/trying everything I think I can intuit from what you are telling me. I believe I’ve covered all the bases, but my head is swimming again so time to report back in and call it a day.
I really think the crux of the issue comes down to
the Sum(tranqty) needs filters that cannot be applied in standard SQL window functions
the MtlUnitCost (non aggregate result) has to be the single previous row of a filtered dataset (trantype, partnum and trannum)
LAG will get me close to it, but not all the way until I get to SQL 2017 where I can use first_value or last_value to obtain the single value of MtlUnitCost.
the BAQ editor will not allow filtering of subquery columns not chosen as ‘display columns’ in the aggregate subquery. This last is really the main issue here.If I expose trannum, then I have to group by it but that breaks the sum() by adding a level.
@knash - “Should be able to” and “Actually can” seem to be in conflict with each other at the moment. And it feels like I’m so close to this, I can’t see through to the obvious solution…
@brandon - “sum() Over Partition” does allow for multiple partitions, but not an IN clause to filter the trantypes. I can’t just sum(tranqty) over partnum as it has to be only those transaction types. Plus it has to be where trannum < TopLevelQuery.max(trannum) but not partitioned or grouped by trannum in any way.
No matter how I try, the BAQ will not allow the max(parent.trannum) > sub.trannum relationship… I’ve tried all of these repeatedly and in various forms.
calculated field using Sum() Over (Partition, Order, Range) - cannot include the trantype filter for the summary, or specify “< max(trannum)” relationship in the Range clause. I can offset the Range by 1 row which I think will work for that, but it doesn’t get me the trantype filter.
aggregate subquery - cannot expose the trannum field without including it in the group by, so the criteria of TopLevel.Calculated_max(trannum)> AggSubQuery.trannum cannot be applied.
non-aggregate subquery - which is really just a copy of PartTran - I cannot apply the TopLevel.Calculated_max(trannum)> SubQuery.trannum criteria at the join, table, or subquery level b/c the BAQ says it is invalid because it either cannot be bound or is not included in the group by or having clause. And while you can mark the subquery criteria as ‘having’, it still barks an error.
Looks like I’m down to building my own function or using a SQL/View and External BAQ - if we can pass parameter values - which I don’t think we can yet.
I’ll try to tackle some pieces of this probably tomorrow. I just started my own project. But at far as this part, if you make a subquery that was only tackling the tran quantity, you can filter the part tran table to only include the transactions that affect inventory (so you don’t have to do that from within the partitioning). Then when you have that subquery, you can use this method to bring in only the top 1 row where the date is less than the date of the row that it’s joined to. That should solve your quantity at a certain date part of the problem.
Then I would basically use the same method to get the cost as well, except you shouldn’t need windowing functions for that, just a unit cost of a transaction type that you trust with the same top 1 row, where the date is less than the date you are trying to join to. That would get you the qty and cost.
Then you filter your part tran again (you would have 3 by now) for the top level to get your pur-ins transactions. I don’t see using a CTE for this one.
That’s all I have time for now. When I get a chance I’ll work up and example (except we don’t have pur-ins transactions so it’ll be slightly different)
@Banderson - I do appreciate the help, but I certainly do not want to interfere with your other projects. (This is not an immediate priority on our end) I’ll be working on it as well some today too, so we’ll see what happens.
@knash - yep - that’s about the short of it. Appreciate any effort you want to put in (and potentially learn something ) but PLEASE do not detract from normal business on this. I’ve tried a handful of times and keep hitting the wall (but I am certainly not an expert at SQL or BAQs like others so I am perfectly happy to learn something new!) Thanks!
) - We deal with a lot of steel parts that include ever-changing prices due to tariffs/surcharges/market, so we’ve got a guy adjusting Part costs on a daily basis based on the PO unit cost/Rcvd Qty/weighted avg method every time we receive it into Inspection.<a class=“attachment”