April 28, 2015 at 12:17 pm
This the result of the function: this is good
SELECT * FROM dbo.ADMC_DDL_GetUniqueEditLog()
3029 2013-01-08 15:00:21.91013144379005410 2160EDIT NULL
838072015-04-24 10:25:58.42015206348842011500 10000EDIT
I posted the response regarding the other questions.
April 28, 2015 at 12:25 pm
Your previous question - if this is the only value you want returned, is this based on PlanId, TransId, or EntityId? If there are different PlanIds, TransIds, or EntityIds what do you want?
- in some case there will be different planids, and entityid. transid will always be different so as id. the return record should be unique planid, entityid and most up to date editdate
April 28, 2015 at 1:31 pm
Here is the final query for the view:
SELECT NS.LocCode
,NS.LocationDesc
,NS.PrdName
,NS.ProductDescription
,NS.PkgCode
,VE.Label
,E.PlanId
,P.Description
,E.TransId
,E.EventTypeId
,CONVERT(INT,ROUND(SUM(E.Qty),0)) AS Quantity
,EL.Id
,EL.EditData
,EL.PlanId
,EL.TransId
,PP.PeriodLabel
,EL.EntityId
,CONVERT(INT,ROUND(SUM(EL.QtyPrior),0)) AS [Quantity Prior]
,EL.Description
,EL.NoteText
FROM dbo.Event AS E
INNER JOIN dbo.ADMC_DDL_GetUniqueEditLog() AS EL
ON EL.PlanId = E.PlanId
AND EL.TransId = E.TransId
LEFT JOIN dbo.NodeSku AS NS
ON NS.NodeId = E.NodeId
LEFT JOIN dbo.ViewElements AS VE
ON VE.Id = E.EventTypeId
INNER JOIN dbo.Plans AS P
ON P.PlanId = E.PlanId
AND P.PlanId = EL.PlanId
LEFT JOIN dbo.PlanPeriod AS PP
ON PP.CalendarId = P.CalendarId
AND PP.PeriodId = E.TimePeriod
WHEREE.EventTypeId IN (300703,300000, 300001)
AND P.PlanId = 15
AND E.Qty <> 0
AND PP.PeriodEnd <=GETDATE()
GROUP BY E.PlanId
,P.Description
,NS.LocCode
,NS.LocationDesc
,NS.PrdName
,NS.ProductDescription
,NS.PkgCode
,VE.Label
,E.TransId
,PP.PeriodLabel
,E.EventTypeId
,E.Qty
,EL.Id
,EL.EditData
,EL.PlanId
,EL.TransId
,EL.EntityId
,EL.QtyPrior
,EL.Description
,EL.NoteText
The result still wrong. I should get one record and the "Quantity" field should say "40" not "1" and "Quantity Prior" should say "30" not "1". see below and for better, copy the result to notepad. I appreciate all the help so far but I don't know this is not working
LocCodeLocationDescPrdNameProductDescriptionPkgCodeLabelPlanIdDescriptionTransIdEventTypeIdQuantityIdEditDataPlanIdTransIdPeriodLabelEntityIdQuantity PriorDescriptionNoteText
NULLNULLNULLNULLNULLQty Adjustment15Factory20635317772012022638452015-04-28 20:36:35.9471520120204/20/2015-04/26/201512EDITchange value from 30 to 40
0734Laval1234 tesingABCDQty Adjustment15Factory20635317772012021638452015-04-28 20:36:35.9471520120204/20/2015-04/26/201511EDITchange value from 30 to 40
April 28, 2015 at 1:33 pm
What part is wrong?
The data from the initial query we worked on or from the final query?
Did you ever post expected results based on the 3 lines of sample data you finally posted as INSERT INTO statements?
April 28, 2015 at 2:12 pm
Hi Lynn,
I think the final query is. Yes I posted the result
when I query the EditLog table
SELECT *
FROM dbo.EditLog AS EL
WHERE EL.NoteText = 'change value from 30 to 40'
I get 38 records where QtyPrior 0.789473684210526 are all the same and QtyAfter = 1.05263157894737 are all the same
so if I do a quick math for QtyPrior field (0.789473684210526 * 38 =30) and QtyAfter (1.05263157894737 * 38 =40)
So in my final query I should have QtyPrior 30 QtyAfter 40
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply