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 2008 Forums
 Transact-SQL (2008)
 Last datetime

Author  Topic 

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-19 : 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
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'
)temp
WHERE temp.LastAssignment = 1


If not, post the sample data of Problem and Task tables and also expected output.......

--
Chandu
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-19 : 06:19:08
Thanks Bandi

I'll check this out...

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-19 : 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
Go to Top of Page

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 BY

Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

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'
)temp
WHERE temp.Seq = 1


--
Chandu
Go to Top of Page

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.71
Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

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
Go to Top of Page

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.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
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-20 : 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...!
Go to Top of Page

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 result
ROW_NUMBER() OVER(PARTITION BY A.ParentNum, A.TaskType ORDER BY A.CreatedDateTime DESC)As Seq,

Why you need View?

--
Chandu
Go to Top of Page
   

- Advertisement -