| Author |
 Topic  |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
 Posted - 03/19/2013 : 04:24:35
|
Hi
Im trying to only show the latest datetime row from the statement below but I'm seeing other assignment rows from the TASK table which I've joined to the PROBLEM table, each time a new assignment is created it is recorded in the TASK table and a new row is added to the PROBLEM ID.
ALTER VIEW [dbo].[LastProblemAssignment]
AS
SELECT Distinct P.ProblemNumber,
MAX(A.CreatedDateTime) As LastAssignment,
A.OwnerTeam As AssignedTeam,
P.LastModDateTime,
P.Status,
P.IncidentCount,
P.Priority,
P.Subject,
DateDiff(d,A.CreatedDateTime, GetDate()) As OpenDays
FROM Problem P
Inner Join Task As A On
P.RecID=A.ParentLink_RecID
Where P.Status <> 'Closed'
Group By
P.ProblemNumber,
P.LastModDateTime,
P.Status,
P.Priority,
P.Subject,
P.IncidentCount,
A.OwnerTeam,
A.CreatedDateTime
We can see here the PROBLEM rows, just want to see the latest createddatetime (2nd column).
http://www.pcfixerman.com/index.php/screenshot
Thanks
SZ1
Learning and development is the driving force in the universe...! |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/19/2013 : 05:37:32
|
May be this?
SELECT *
FROM (SELECT Distinct P.ProblemNumber,
ROW_NUMBER() OVER(ORDER BY A.CreatedDateTime DESC)As LastAssignment,
A.OwnerTeam As AssignedTeam,
P.LastModDateTime,
P.Status,
P.IncidentCount,
P.Priority,
P.Subject,
DateDiff(d,A.CreatedDateTime, GetDate()) As OpenDays
FROM Problem P
Inner Join Task As A On P.RecID=A.ParentLink_RecID
Where P.Status <> 'Closed'
)temp
WHERE temp.LastAssignment = 1
If not, post the sample data of Problem and Task tables and also expected output.......
--
Chandu |
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/19/2013 : 06:19:08
|
Thanks Bandi
I'll check this out...
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/19/2013 : 07:32:23
|
quote:
Originally posted by sz1
Thanks Bandi
I'll check this out...
SZ1
Learning and development is the driving force in the universe...!
got correct results
--
Chandu |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/19/2013 : 08:20:28
|
Seemed to have strange results with this,for example the LastAssignment field is returning as a number not a date, the IncidentCount is returning 0 even though there are incidents attached to the problems and also there is only one problem returned and not all 11 that are open.
I se we dont need grouping with your query is that because of the : ROW_NUMBER() OVER(ORDER BY
Thanks
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/19/2013 : 09:46:18
|
You can add PARTITION BY option for group based results....
SELECT *
FROM (SELECT Distinct P.ProblemNumber,
ROW_NUMBER() OVER(PARTITION BY P.ProblemNumber, P.LastModDateTime, P.Status, P.Priority, P.Subject, P.IncidentCount, A.OwnerTeam ORDER BY A.CreatedDateTime DESC)As Seq,
A.CreatedDateTime AS LastAssignment,
A.OwnerTeam As AssignedTeam,
P.LastModDateTime,
P.Status,
P.IncidentCount,
P.Priority,
P.Subject,
DateDiff(d,A.CreatedDateTime, GetDate()) As OpenDays
FROM Problem P
Inner Join Task As A On P.RecID=A.ParentLink_RecID
Where P.Status <> 'Closed'
)temp
WHERE temp.Seq = 1
--
Chandu |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/19/2013 : 10:29:49
|
That returns all the rows but with the duplicates again, so the Problem number appears more than once where it finds more than one assignment and the LastAssignment field is not a date, its like this: 41,074.71
Thanks
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3829 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/20/2013 : 01:07:52
|
quote:
Originally posted by sz1
That returns all the rows but with the duplicates again, so the Problem number appears more than once where it finds more than one assignment and the LastAssignment field is not a date, its like this: 41,074.71
Thanks
SZ1Learning and development is the driving force in the universe...!
Post the sample data of Problem and Task Tables and also expected output.....
Why CreatedDateTime is not a DATETIME type?
--
Chandu |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 03/20/2013 : 07:15:59
|
Hi
The CreatedDateTime is a datetime stamp, I got around the issue by creating a simple view that returns only the Max datetime, then I pulled the TASK table into the report and linked the fields up, this now removes all duplicate values except the latest one.
Thanks for your advice on this.
ALTER VIEW [dbo].[LastProbAssignment]
AS
SELECT
T.ParentNum,
MAX(T.CreatedDateTime) As CreatedDateTime,
T.TaskType
From Task T
Group By
T.ParentNum,
T.TaskType
GO
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/20/2013 : 07:36:28
|
means you have to get latest record based on Task.ParentNum and Task.TaskType....
It will also give correct result
ROW_NUMBER() OVER(PARTITION BY A.ParentNum, A.TaskType ORDER BY A.CreatedDateTime DESC)As Seq,
Why you need View?
--
Chandu |
|
|
| |
Topic |
|
Last datetime
