SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Last datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 03/19/2013 :  04:24:35  Show Profile  Reply with Quote
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
2217 Posts

Posted - 03/19/2013 :  05:37:32  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 03/19/2013 :  06:19:08  Show Profile  Reply with Quote
Thanks Bandi

I'll check this out...

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

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/19/2013 :  07:32:23  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 03/19/2013 :  08:20:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/19/2013 :  09:46:18  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 03/19/2013 :  10:29:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/19/2013 :  11:58:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/20/2013 :  01:07:52  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 03/20/2013 :  07:15:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/20/2013 :  07:36:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000