help creating query/dashboard for getting the date for the first part transaction in parttran with the transaction type being either MTL-STK or PUR-STK or STK-CUS, wanting this for all active parts in the Part table.
I had a query working and worked great at the end of December, and not sure why not, so trying to recreate query and dashboard again.
Best answer by cheeder@statlab.com
View originalHere’s a technique for getting “first” or “last” from a query..
Create a main query with the table of values to get the results of first or last)
Example:
To get the first or last PO number for a list of parts
in main query include Part table and filter it for the parts of interest.
Then create a sub query for the source of the first or last info
Example:
In subquery, use PODetail linked to POHeader
Include fields:
PONum
PODate
and any other information of interest (such as Vendor number, quantity, etc.)
Add a calculated field in subquery:
For earliest PO
Define a new calculated variable named (say)
Found
as nvarchar 8
And for earliest PO, enter the formula:
Case
when Row_Number() OVER (Partition By PODetail.PartNum
ORDER BY POHeader.OrderDate)=1
then 'YES'
else 'NO'
end
or
for latest PO, enter the formula:
Case
when Row_Number() OVER (Partition By PODetail.PartNum
ORDER BY POHeader.OrderDate DESC)=1
then 'YES'
else 'NO'
end
Add Subquery to main query and link the Subquery to Part by PartNum
Filter on SubQuery for Found=YES
Include fields from subquery (field names defined in subquery will pull through to main query).
It helps others to help you if you share your query syntax and the current results.
Here is what I have for the query:
select
[PartTran].[SysDate] as [PartTran_SysDate],
[PartTran].[SysTime] as [PartTran_SysTime],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[WareHouseCode] as [PartTran_WareHouseCode],
[PartTran].[BinNum] as [PartTran_BinNum],
[PartTran].[TranQty] as [PartTran_TranQty],
[PartTranPrev].[TranNum] as [PartTranPrev_TranNum],
((case when (PartTranPrev.TranNum ) is null then 1 else 0 end)) as [Calculated_NullPrev],
[PartTran].[TranType] as [PartTran_TranType],
[Part].[TypeCode] as [Part_TypeCode],
[Part].[ClassID] as [Part_ClassID],
[PartClass].[Description] as [PartClass_Description]
from Erp.Part as Part
inner join Erp.PartTran as PartTran on
Part.Company = PartTran.Company
and Part.PartNum = PartTran.PartNum
and ( PartTran.TranType = 'STK-MTL' or PartTran.TranType = 'PUR-STK' )
left outer join Erp.PartTran as PartTranPrev on
PartTranPrev.Company = PartTran.Company
and PartTranPrev.PartNum = PartTran.PartNum
and PartTranPrev.TranNum < PartTran.TranNum
and ( PartTranPrev.TranType = 'STK-MTL' or PartTranPrev.TranType = 'PUR-STK' )
inner join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID
where (Part.InActive = false)
and (((case when (PartTranPrev.TranNum ) is null then 1 else 0 end)) = true)
order by PartTran.PartNum
This query runs fine for me. What issues are you seeing?
It is timing out and coming back with nothing. It worked last month, and nothing has changed with the Query, and no new updates to Epicor, so I am wondering if it is a data issue. I am going to look at the process and make sure there isn’t a loop created if it doesn’t find anything in the Parttran table for apart added in the part table. So if there are no transactions on a part for transaction type PUR-STK, MLT-STK, or STK-CUS, what does it do?
I fired up our test version ( older data ) and the query runs fine there. So it has to be either a condition in the data that is causing the query to run in a circle and then timing out. So in looking at the query, thinking what happens if one of the parts in the Part Table, doesn’t have a transaction at all with a “STK-MTL or PUR-STK” for that part. If it doesn’t find at least one what happens? I can see that if it doesn’t find one in the parttran PREV copy it has a way out, as it looks for a NULL and then ends for that part. Wondering if that is the case.
Not sure what else would cause it to error out. Or how to figure that out.
I like what you have there, but confused on how to apply it to what I need. So For all active parts in out PART table, we want to get the earliest date for either the STK-MTL or PUR-STK transaction ( in the PartTran table , where the TranType is either the STK-MTL or PUR-STK. I need to get that date and which TranType that is.
I will try a few things but if you have a suggestion I would greatly appreciate that.
Okay I got this to work here is what I did:
with [SubQuery1] as
(select
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[TranDate] as [PartTran_TranDate],
[Part].[TypeCode] as [Part_TypeCode],
(case
when Row_Number() OVER (Partition BY Part.PartNum ORDER BY PartTran.TranDate)=1
then 'YES'
else 'NO'
end) as [Calculated_Found]
from Erp.PartTran as PartTran
inner join Erp.Part as Part on
PartTran.Company = Part.Company
and PartTran.PartNum = Part.PartNum
and ( Part.InActive = False )
where (PartTran.TranType = 'PUR-STK' or PartTran.TranType = 'STK-MTL' or PartTran.TranType = 'MFG-STK'))
select
[SubQuery1].[PartTran_PartNum] as [PartTran_PartNum],
[SubQuery1].[PartTran_TranDate] as [PartTran_TranDate],
[SubQuery1].[PartTran_TranType] as [PartTran_TranType],
[SubQuery1].[Part_TypeCode] as [Part_TypeCode]
from SubQuery1 as SubQuery1
where (SubQuery1.Calculated_Found = 'YES')
This works great, wouldn’t of resolved this as quickly without help from cheeder@statlab.com. Thank you very much.
didn’t see where to click to say this was the best answer, but it was.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.