August 30, 2011 at 1:35 pm
Hello -
I need some T-SQL help and hope someone can clear my head of this.
I have a query I want to be able to see the latest ModifiedDate records. So lets say out of 15 records of the same ticket number. I only want the ones that have not been touched since 7 days from today. Here is my script I have so far.
SELECT tn.WOID as 'Ticket_Number', t.request as 'Requestor', t.Task as 'Summary',t.Respons as 'Assigned_Technician', t.assndate as 'Assigned_To_Tech_On',tn.TaskNoteTypeId, tn.ModifiedDate
FROM TaskNote tn
INNER JOIN Tasks t ON tn.WOID = t.WOID
WHERE t.assndate <= dateadd(ww, -1, getdate())
AND tn.WOID = '3067642'
AND t.CLSDDATE IS NULL
AND tn.TaskNoteCategoryId IS Null
I cannot figure what to use? MAX or Group By and how to work that into the script.
Regards,
David
August 30, 2011 at 1:47 pm
I'm not clear on what you want to Max or Group By for. Max what?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 1:50 pm
david.ostrander (8/30/2011)
Hello -I need some T-SQL help and hope someone can clear my head of this.
I have a query I want to be able to see the latest ModifiedDate records. So lets say out of 15 records of the same ticket number. I only want the ones that have not been touched since 7 days from today. Here is my script I have so far.
SELECT tn.WOID as 'Ticket_Number', t.request as 'Requestor', t.Task as 'Summary',t.Respons as 'Assigned_Technician', t.assndate as 'Assigned_To_Tech_On',tn.TaskNoteTypeId, tn.ModifiedDate
FROM TaskNote tn
INNER JOIN Tasks t ON tn.WOID = t.WOID
WHERE t.assndate <= dateadd(ww, -1, getdate())
AND tn.WOID = '3067642'
AND t.CLSDDATE IS NULL
AND tn.TaskNoteCategoryId IS Null
I cannot figure what to use? MAX or Group By and how to work that into the script.
Regards,
David
right with Gus not sure what you want. What do you mean by touched? You have a ModifiedDate in TaskNote. Can you just add tn.AssignedDate <= dateadd(ww, -1, getdate())??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 1:53 pm
Hi -
I attached an example of my sql output. What I'm looking for is how to get only the the last date 8/29/2011 when I run my query.
Sorry if this does not make sense...
Thanks,
David
August 30, 2011 at 1:59 pm
If you can provide some ddl and sample data this is a pretty quick and painless task. Take a look at the link in my signature about the best approach to posting this type of stuff.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 2:01 pm
The basic gist of what you want to do. I am pretty sure you want to use Task and NOT TaskNote as your main table. Then join to TaskNote getting the latest note. For this you would want the MAX(ModifiedDate) or whatever field in that table that would let you know which one is the most recent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 3:09 pm
Thank you both I was able to get what I needed by using the Task and not Tasknote.
David
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply