SQL Select Statement Help

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,831
Reaction score
3,033
Location
On the toilet
Hi all,

Brain isn't helping this morning with a query.
Have a table with 6 fields. Project,Task,Resource,Status,Date and Hours.

I know there are duplicates here but combing through 4000 records and trying to find them isn't working.

Anyway to script this ? :o.

Duplicates would be where the first 4 fields are the same but there is time logged for 2 different statuses.

Any help would be appreciated.
 
Last edited:
SELECT count(id), * FROM table group by project,task,resource

and a bit of editing to find specific fields where count >1
 
SELECT project,task,resource FROM table group by project,task,resource
having count(*) > 1
 
Made a boo boo. First 4 fields make the record unique. Can't group with out having a column in the * in the group aswell.

[table="width: 500"]
[tr]
[td]Project[/td]
[td]Task[/td]
[td]Resource[/td]
[td]Date[/td]
[td]Time[/td]
[td]Status[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]2011-06-29[/td]
[td]5[/td]
[td]1[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]2011-06-29[/td]
[td]8[/td]
[td]2[/td]
[/tr]
[/table]
 
depends on what you are going for here. you could also use DISTINCT or you can group, I think the best approach would be DISTINCT.

so SELECT DISTINCT Project,Task,Resource,Status,Date and Hours FROM table.....
 
Baxteen said:
depends on what you are going for here. you could also use DISTINCT or you can group, I think the best approach would be DISTINCT.

so SELECT DISTINCT Project,Task,Resource,Status,Date and Hours FROM table.....
Click to expand...

Managed with a CTE. Your post got me the idea to google NOT DISTINCT which led me to an answer.

Code: 
;with counts as (
    select ProjectId,TaskId,ResourceId,TimesheetLineStatusId,TimeByDay,HourlyTime,IsAdmin,
      count(*) over (partition by ProjectId,TaskId,ResourceId,TimesheetLineStatusId,TimeByDay) as num
    from staging.TempUpdateTimesheet
)
select ProjectId,TaskId,ResourceId,TimesheetLineStatusId,TimeByDay,HourlyTime,IsAdmin
from counts
where num > 1
order by TimeByDay asc
 
Baxteen said:
depends on what you are going for here. you could also use DISTINCT or you can group, I think the best approach would be DISTINCT.

so SELECT DISTINCT Project,Task,Resource,Status,Date and Hours FROM table.....
Click to expand...

This would still give me all the records that aren't duplicates :cry:
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X