Qry help - one record per process

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Qry help - one record per process

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-05-09 : 06:12:53

Hi, i need help please.

I want a one line record with a few task indicators.
i got my case & qry to do the foll:

Pro Desc Task1 Task2
1111 test Y N
1111 test N Y

I want to group on Process or description.

What i want is one line:
Pro Desc Task1 Task2
1111 test Y Y

Please Assist!
qry:
select v2.Process as Process
,MAX(Case when max_qry2.ControlID = 'FG1010NItemD' then max_qry2.ControlValue else '' END) as Description
,convert(nvarchar, cast(Start.CreateDate as datetime), 6) as StartDte
,rtrim(u2.FirstName) + ' ' + rtrim(u2.LastName) as Originator
,convert(nvarchar, TaskReceived, 6) as TaskReceived
,ToUser as TaskUser
,v2.description
,Completed
,case when v2.Description like 'CFG - Fin%' then 'Y' else 'N' end as CFGFin
,case when v2.Description like 'CFG - Mfs%' then 'Y' else 'N' end as CFGMst
,case when v2.Description like 'CFG - Mkt%' then 'Y' else 'N' end as CFGMkt
,case when v2.Description like 'CFG - RD%' then 'Y' else 'N' end as CFGRD
,case when v2.Description like 'CFG - Cst%' then 'Y'
when v2.Description like 'CFG - Fin%' or v2.Description like 'CFG - Mfs%' or v2.Description like 'CFG - Mkt%' or v2.Description like 'CFG - RD%' then 'T'
else 'N' end as CFGCst
,case when v2.Description like 'CFG Price - Mkt%' then 'Y'
when v2.Description like 'CFG - Cst%' or v2.Description like 'CFG - Fin%' or v2.Description like 'CFG - Mfs%' or v2.Description like 'CFG - Mkt%' or v2.Description like 'CFG - RD%' then 'T'
else 'N' end as CFGPrice
,case when v2.Description like 'CFG End - Mfs%' then 'Y'
when v2.Description like 'CFG - Fin%' or v2.Description like 'CFG - Mfs%' or v2.Description like 'CFG - Mkt%' or v2.Description like 'CFG - RD%' or v2.Description like 'CFG - Cst%' or v2.Description like 'CFG Price - Mkt%' then 'T'
else 'N' end as CFGEnd

from
(select rtrim(e.ProcessID) as Process
,cast(e.CreateDate as datetime) as TaskReceived
,rtrim(e.Assigned) as Assigned
,rtrim(e.ResultUser) as RUser
,rtrim(e.ResultDate) as Completed
,rtrim(e.Description) as Description
,rtrim(e.EventId) as EventID
,rtrim(E.ID) as ID
,rtrim(v.EventHistID) as EventHistID
,rtrim(v.ControlID) as ControlID
,rtrim(v.ControlValue) as ControlValue
,case when e.ResultUser = 0 --and e.ResultUser = 0
then rtrim(role.description)
else
rtrim(u.FirstName) + ' ' + rtrim(u.LastName) end as ToUser

from dbo.fcEventHist as E

left join dbo.fcEventValue as v
on e.ID = v.EventHistID

left join dbo.fcUser as U
on E.ResultUser = U.ID

left join dbo.fcRoleGroup as Role
on Role.ID = E.ResultID

where e.EventId in (96,97,98,99,100,101,104)

) v2

join (select max(ID) as max_id, e1.ProcessId, e1.EventId
from dbo.fcEventHist E1 group by e1.ProcessID, e1.EventId) max_qry
on max_id = v2.ID
and max_qry.EventId = v2.EventId
and max_qry.ProcessId = v2.Process

join (select max(v3.EventHistID) as max_id2,v3.ControlID, v3.ControlValue, e3.ProcessId from dbo.fcEventHist E3
left join dbo.fcEventValue v3
on e3.ID = v3.EventHistID
and v3.ControlID = 'FG1010NItemD'
group by v3.controlid, v3.ControlValue, e3.ProcessId) max_qry2
on max_qry2.ProcessId = v2.Process

left join dbo.fcEventHist as Start
on Start.ProcessID = v2.Process
and Start.EventId = 95

left join dbo.fcUser as U2
on Start.CreateId = U2.RoleId

where max_qry2.ControlID is not null
and completed = 0

group by
v2.Process
,v2.Description
,Start.CreateDate
,rtrim(u2.FirstName) + ' ' + rtrim(u2.LastName)
,TaskReceived
,ToUser
,Completed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 10:51:22

It will be better if you can provide a sample set of data for your scenario and give your desired o/p out of it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources