Author |
Topic |
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-03-19 : 04:24:35
|
HiIm 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]ASSELECT 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 OpenDaysFROM Problem PInner Join Task As A OnP.RecID=A.ParentLink_RecIDWhere P.Status <> 'Closed'Group ByP.ProblemNumber,P.LastModDateTime,P.Status,P.Priority,P.Subject,P.IncidentCount,A.OwnerTeam,A.CreatedDateTimeWe can see here the PROBLEM rows, just want to see the latest createddatetime (2nd column).http://www.pcfixerman.com/index.php/screenshotThanksSZ1Learning and development is the driving force in the universe...! |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 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' )tempWHERE temp.LastAssignment = 1 If not, post the sample data of Problem and Task tables and also expected output.......--Chandu |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-03-19 : 06:19:08
|
Thanks BandiI'll check this out...SZ1Learning and development is the driving force in the universe...! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 07:32:23
|
quote: Originally posted by sz1 Thanks BandiI'll check this out...SZ1Learning and development is the driving force in the universe...!
got correct results--Chandu |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-03-19 : 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 BYThanksSZ1Learning and development is the driving force in the universe...! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-19 : 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' )tempWHERE temp.Seq = 1 --Chandu |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-03-19 : 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.71ThanksSZ1Learning and development is the driving force in the universe...! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-19 : 11:58:43
|
If you still need help, please follow the links below for how to post your question in a way that helps us to help you:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 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.71ThanksSZ1Learning 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
Aged Yak Warrior
555 Posts |
Posted - 2013-03-20 : 07:15:59
|
HiThe 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]ASSELECT T.ParentNum,MAX(T.CreatedDateTime) As CreatedDateTime,T.TaskTypeFrom Task TGroup ByT.ParentNum,T.TaskTypeGOSZ1Learning and development is the driving force in the universe...! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 07:36:28
|
means you have to get latest record based on Task.ParentNum and Task.TaskType....It will also give correct resultROW_NUMBER() OVER(PARTITION BY A.ParentNum, A.TaskType ORDER BY A.CreatedDateTime DESC)As Seq,Why you need View?--Chandu |
|
|
|