March 3, 2011 at 6:52 am
Hi all
I had a table columns with
sno Name Case Datetime Team Id
-------------------------------------------------------------
1 ravikira 1122 2011-01-26 11:45:00.000 2
2 kiran 2244 2011-02-03 00:00:00.000 3
3 ravikira 1122 2011-01-25 00:00:00.000 3
4 kiran 2244 2011-04-05 00:00:00.000 1
I want output like
sno Name Case Dateime TeamId
---------------------------------------------------------------
1 ravikira 1122 2011-01-26 11:45:00.000 2
2 kiran 2244 2011-04-05 00:00:00.000 1
I need to remove duplicate values from the name columns and recent entry of Datetime
Thanks
Ravi
March 3, 2011 at 7:44 am
I believe you could just group the information:
select sno, name, case, max(datetime) as MaxDate
from tablename
group by sno, name, case
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 3, 2011 at 8:22 am
Sharon Kumar (3/3/2011)
Hi allI had a table columns with
sno Name Case Datetime Team Id
-------------------------------------------------------------
1 ravikira 1122 2011-01-26 11:45:00.000 2
2 kiran 2244 2011-02-03 00:00:00.000 3
3 ravikira 1122 2011-01-25 00:00:00.000 3
4 kiran 2244 2011-04-05 00:00:00.000 1
I want output like
sno Name Case Dateime TeamId
---------------------------------------------------------------
1 ravikira 1122 2011-01-26 11:45:00.000 2
2 kiran 2244 2011-04-05 00:00:00.000 1
I need to remove duplicate values from the name columns and recent entry of Datetime
Thanks
Ravi
There is no duplicate data here. Your desired output does not match the table data. You have sno 2 Kiran and TeamID 1. That just doesn't match the original data.
If you can clarify business rules of what you are trying to do we can have a go at it.
_______________________________________________________________
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/
March 3, 2011 at 8:25 am
For my solution, I assumed Team ID was not required or that it was just a typo of the data. If my assumptions were both wrong, then yes, we need further business rules or actual sample data.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 3, 2011 at 11:23 am
Hi this is table values
Investigation_IdCase_IdInvestigation_NoteDateTimeTeam_IdStatus_Id
31101050282Testing Testing Testing 2011-01-26 11:45:00.000 2 1
51101050282sample 2011-01-26 00:00:00.000 3 2
91102010093fdsafsadfasd2011-02-03 00:00:00.000 2 2
111102010093rffas rffas rffas rffas 2011-04-05 00:00:00.000 1 2
131101050215retst fasfsdaf 2011-04-05 00:00:00.000 2 1
I need output like
Investigation_IdCase_IdInvestigation_NoteDateTime Team_Id Status_Id
31101050282Testing Testing 2011-01-26 11:45:00.000 2 1
111102010093rffas rffas rffas rffas 2011-04-05 00:00:00.000 1 2
131101050215retst fasfsdaf 2011-04-05 00:00:00.000 2 1
I need results like caseid which is recently updated.
March 3, 2011 at 11:26 am
Again your sample data doesn't have any duplicates. The output you displayed is exactly what's in the sample data.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 3, 2011 at 11:41 am
In the caseid i have duplicated values.
March 3, 2011 at 11:44 am
my original SQL should work for you then.
select Investigation_Id, Case_Id, Investigation_Note, Max(DateTime) MaxDate, Team_Id, Status_Id
from tablename
group by Investigation_Id, Case_Id, Investigation_Note, Team_Id, Status_Id
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 3, 2011 at 12:13 pm
Sharon Kumar (3/3/2011)
Hi this is table valuesInvestigation_IdCase_IdInvestigation_NoteDateTimeTeam_IdStatus_Id
31101050282Testing Testing Testing 2011-01-26 11:45:00.000 2 1
51101050282sample 2011-01-26 00:00:00.000 3 2
I need output like
Investigation_IdCase_IdInvestigation_NoteDateTime Team_Id Status_Id
31101050282Testing Testing 2011-01-26 11:45:00.000 2 1
I need results like caseid which is recently updated.
This doesn't really work. You have part of one record and part of another. The group by example will not return a single row in this case. The Status_Id changes, the Team_Id changes. What is the correct Team? Status? Do you only want the most recent record for each Case_Id? Given you sample data and the desired output it just doesn't match up.
_______________________________________________________________
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/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply