I have a bunch of columns, and want to get all rows with max(wf.STEP) only.
Not getting a way to bring., if i use group by clause , then i need to put all columns into group by. which is not possible.
how can i get all rows that has max(wf.step) thanks a lot for the helpful info..
Select wf.ActivityID,
dbo.f_GetProg_Proj_Ctr(wf.ModuleName,wf.ModuleRecordID) as "Dept - Proceso - Tipo de ID",
rm.ContractorRefNo as "No de ID",
rm.sequenceno as "No de Radicacion",
rm.barcode as Expedient,
dbo.f_GetModuleTitle(wf.ModuleName,wf.ModuleRecordID) as Titulo,
CONVERT(varchar(12), rm.documentdate, 103) as "Fecha Radicado",
CONVERT(varchar(12), rm.ReceivedDate, 103) as "Fecha Recibido",
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lbllogtype' and PickID= rm.LogTypeID) as "Tipo(Canal)",
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lblDocumentSource' and PickID= rm.SubjectID) as "Comite",
wf.step,
(Select username from tab_ccsnetusers where userid = wf.AssignedTo) as Asignado,
wf.Description as descripcion,
CONVERT(varchar(12), wf.duedate, 103) as Vencimento,
CONVERT(varchar(12), wf.donedate, 103) as Realizada,
---CONVERT(date, CONVERT(varchar(10), wf.duedate, 105), 105) AS VenceDate,
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'ActivityType' and PickID= wf.Type) as Type,
CONVERT(varchar(20), DATEDIFF(Day, wf.duedate, wf.donedate)) as Days,
[days] = CASE WHEN wf.duedate < GetDate() THEN -1 * DATEDIFF(Day, wf.duedate, GetDate())
ELSE CONVERT(varchar(20), DATEDIFF(Day, GetDate(), wf.duedate))
END
FROM Tab_ccsNetWorkflowActivity wf, TAB_ccsNetRM rm
where wf.ModuleName='rm' and wf.modulerecordid=rm.rmid
--and datediff(d,getdate(),duedate) <= 30
---and critical=1
and dbo.f_GetModuleTitle(wf.ModuleName,wf.ModuleRecordID) <> ''
and rm.ProgID = 71
and rm.documentdate BETWEEN cast('01/01/2015' as datetime)
AND GETDATE()