April 24, 2015 at 3:24 pm
Taking the "sample data" you posted as if it were the data in a table, here is how I would get the most recent row of data for each PlanId:
/*
Id EditDate PlanId TransId EntityId QtyPrior QtyAfter Description NoteText
83765 2015-04-23 19:42:19.517 15 2063532368 1 0.657894736842105 0.789473684210526 EDIT change from 18 to 25
83766 2015-04-23 19:42:19.643 15 2063532508 1 0.657894736842105 0.789473684210526 EDIT change from 18 to 25
83767 2015-04-23 19:42:19.740 15 2063532098 1 0.657894736842105 0.789473684210526 EDIT change from 18 to 25
83768 2015-04-23 19:42:19.883 15 2063532183 1 0.657894736842105 0.789473684210526 EDIT change from 18 to 25
83769 2015-04-23 19:42:20.000 15 2063531914 1 0.657894736842105 0.789473684210526 EDIT change from 18 to 25
*/
with BaseData as (
select
Id,
EditDate,
PlanId,
TransId,
EntityId,
QtyPrior,
QtyAfter,
Description,
NoteText,
rn = row_number() over (partition by PlanId order by EditDate desc)
from
dbo.EditLog
)
select
Id,
EditDate,
PlanId,
TransId,
EntityId,
QtyPrior,
QtyAfter,
Description,
NoteText
from
BaseData
where
rn = 1
order by
PlanId;
April 24, 2015 at 3:26 pm
forrest77 (4/24/2015)
Lynn, this is the table.CREATE TABLE [dbo].[EditLog](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[EditDate] [DATETIME] NOT NULL,
[PlanId] [INT] NOT NULL,
[TransId] [INT] NOT NULL,
[EntityId] [INT] NOT NULL,
[QtyPrior] [FLOAT] NULL,
[QtyAfter] [FLOAT] NOT NULL,
[Description] [VARCHAR](255) NULL,
[NoteText] [NVARCHAR](MAX) NULL,
CONSTRAINT [PK_EditLog] PRIMARY KEY NONCLUSTERED
Here is some sample data
IdEditDatePlanIdTransIdEntityIdQtyPriorQtyAfterDescriptionNoteText
837652015-04-23 19:42:19.51715206353236810.6578947368421050.789473684210526EDITchange from 18 to 25
837662015-04-23 19:42:19.64315206353250810.6578947368421050.789473684210526EDITchange from 18 to 25
837672015-04-23 19:42:19.74015206353209810.6578947368421050.789473684210526EDITchange from 18 to 25
837682015-04-23 19:42:19.88315206353218310.6578947368421050.789473684210526EDITchange from 18 to 25
837692015-04-23 19:42:20.00015206353191410.6578947368421050.789473684210526EDITchange from 18 to 25
Still can't cut and paste the sample data into the table. Posted some code, see if it helps.
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply