Remove Duplicate Row

Question

Remove Duplicate Row

tadhg88

Ten Centuries

Points: 1031
More actions
October 3, 2008 at 7:45 am

#191728

Hi,
I am trying to finish this query below but i cant seem to remove the extra/duplicate rows that are being displayed, i think its a problem with grouping by the date but im not sure how to avoid this problem
Pic of current results
Each player should only have one row and the count for each month displayed on this row, any help or advice would be appreciated.
Thanks in Advance
Tim
Select p.Id as 'playerId',
IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
Cast(Case when DATENAME(MONTH, tl.Date) = 'July' then Count(tl.PersonID) end as nvarchar) as Jul,
Cast(Case when DATENAME(MONTH, tl.Date) = 'August' then Count(tl.PersonID) end as nvarchar) as Aug,
Cast(Case when DATENAME(MONTH, tl.Date) = 'September' then Count(tl.PersonID) end as nvarchar) as Sep,
Cast(Case when DATENAME(MONTH, tl.Date) = 'October' then Count(tl.PersonID) end as nvarchar) as Oct
From TrainingLog tl
Join Person p on p.ID = tl.PersonID
Join SquadPlayerMapping spm on spm.PlayerID = tl.PersonID
Where spm.SquadId = 12
Group By p.ID, p.firstName, p.MiddleName, p.LastName, DATENAME(MONTH, tl.Date)
Order by p.ID

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

Having the groupby include the datemonth() will cause an extra record for each month where there is data. I think you just need to get rid of that logic.

tadhg88 Ten Centuries Points: 1031 More actions · Answer 2

if i remove the date from the group by leaving this sql

Select p.Id as 'playerId',

IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',

Cast(Case when DATENAME(MONTH, tl.Date) = 'July' then Count(tl.PersonID) end as nvarchar) as Jul,

Cast(Case when DATENAME(MONTH, tl.Date) = 'August' then Count(tl.PersonID) end as nvarchar) as Aug,

Cast(Case when DATENAME(MONTH, tl.Date) = 'September' then Count(tl.PersonID) end as nvarchar) as Sep,

Cast(Case when DATENAME(MONTH, tl.Date) = 'October' then Count(tl.PersonID) end as nvarchar) as Oct

From TrainingLog tl

Join Person p on p.ID = tl.PersonID

Join SquadPlayerMapping spm on spm.PlayerID = tl.PersonID

Join Seasons sea on sea.ID = (Select sea.ID

From Seasons sea

Where sea.CountryID = (Select Value

From DatabaseSettings

Where Name = 'HomeCountryID')

And GetDate() Between sea.StartDate And sea.EndDate)

Where spm.SquadId = 12

And tl.Date Between sea.StartDate And sea.EndDate

Group By p.ID, p.firstName, p.MiddleName, p.LastName --, DATENAME(MONTH, tl.Date)

i get the following error

Msg 8120, Level 16, State 1, Line 2

Column 'TrainingLog.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

upendramkumar SSC Veteran Points: 271 More actions · Answer 3

Im not sure if I understand your goal, however what ever results you are looking at seem to make sense to me if you to show all the months seperately.

1. However if you want to combine all of the months then you need to combine all the case statements into one.

Cast(Case when DATENAME(MONTH, tl.Date) = 'July' then Count(tl.PersonID) end as nvarchar) as Jul,

Cast(Case when DATENAME(MONTH, tl.Date) = 'August' then Count(tl.PersonID) end as nvarchar) as Aug,

Cast(Case when DATENAME(MONTH, tl.Date) = 'September' then Count(tl.PersonID) end as nvarchar) as Sep,

Cast(Case when DATENAME(MONTH, tl.Date) = 'October' then Count(tl.PersonID) end as nvarchar) as Oct

something like below -

--

Select p.Id as 'playerId', IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',

Cast(

Case

when DATENAME(MONTH, tl.Date) = 'July' then Count(tl.PersonID)

when DATENAME(MONTH, tl.Date) = 'August' then Count(tl.PersonID)

when DATENAME(MONTH, tl.Date) = 'September' then Count(tl.PersonID)

when DATENAME(MONTH, tl.Date) = 'October' then Count(tl.PersonID) end as nvarchar) as Month

From TrainingLog tl

Join Person p on p.ID = tl.PersonID

Join SquadPlayerMapping spm on spm.PlayerID = tl.PersonID

Where spm.SquadId = 12

Group By p.ID, p.firstName, p.MiddleName, p.LastName, DATENAME(MONTH, tl.Date)

--

2. Simple elimination of duplicates from the output of the query can be done as follows but I dont think this what you are looking for.

Select P.Playerid, P.Player, P.Jul, P.Aug, P.Sep, P.Oct, Count(*)

from

(Select p.Id as 'playerId',

IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',

Cast(Case when DATENAME(MONTH, tl.Date) = 'July' then Count(tl.PersonID) end as nvarchar) as Jul,

Cast(Case when DATENAME(MONTH, tl.Date) = 'August' then Count(tl.PersonID) end as nvarchar) as Aug,

Cast(Case when DATENAME(MONTH, tl.Date) = 'September' then Count(tl.PersonID) end as nvarchar) as Sep,

Cast(Case when DATENAME(MONTH, tl.Date) = 'October' then Count(tl.PersonID) end as nvarchar) as Oct

From TrainingLog tl

Join Person p on p.ID = tl.PersonID

Join SquadPlayerMapping spm on spm.PlayerID = tl.PersonID

Where spm.SquadId = 12

Group By p.ID, p.firstName, p.MiddleName, p.LastName, DATENAME(MONTH, tl.Date))P

group by P.Playerid, P.Player, P.Jul, P.Aug, P.Sep, P.Oct

having count(*) = 1

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Okidoki... first off, since you didn't provide any table creation scripts nor data population scripts, you have to understand that I've not tested this code. Please see the link in my signature for how to get better, quicker answers. 😉

Now, this isn't a dupe deletion problem, folks... it's a CROSSTAB problem. Here's the code that will produce what you want... again, it's untested...

SELECT --This Outer Select pivots the NVP data using a bit of very fast CrossTab technology (faster than PIVOT)

pa.PlayerID,

pa.Player,

SUM(CASE WHEN pa.TheMonth = 7 THEN pa.TheCount ELSE 0 END) AS Jul,

SUM(CASE WHEN pa.TheMonth = 8 THEN pa.TheCount ELSE 0 END) AS Aug,

SUM(CASE WHEN pa.TheMonth = 9 THEN pa.TheCount ELSE 0 END) AS Sep,

SUM(CASE WHEN pa.TheMonth = 10 THEN pa.TheCount ELSE 0 END) AS Oct

FROM (--==== Derived table 'pa' pre-aggrgates the data as if a "Name/Value Pair" or "NVP" table

SELECT p.ID AS PlayerID,

ISNULL(p.FirstName+ ' ','') + ISNULL(p.MiddleName+ ' ','') + ISNULL(p.LastName,'') AS Player,

DATEPART(mm, tl.Date) AS TheMonth,

COUNT(t1.PersonID) AS TheCount,

FROM dbo.TrainingLog t1

INNER JOIN dbo.Person p ON p.ID = t1.PersonID

INNER JOIN dbo.SquadPlayerMapping spm ON spm.PlayerID = t1.PersonID

WHERE spm.SquadID = 12

GROUP BY p.ID, p.FirstName, p.MiddleName, p.LastName, DATEPART(mm, tl.Date))pa

GROUP BY pa.PlayerID, pa.Player

This particular crosstab uses a "pre-aggregation" sub-query which makes it even faster, still. Please see the following URL for more information about creating cross tabs, how they work, and some testing that shows they beat equivalent Pivots.

http://qa.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

tadhg88 Ten Centuries Points: 1031 More actions · Answer 5

thanks for your reply upendramkumar i have uploaded a pic of the results i am hopig to get you can view it here

Tim

tadhg88 Ten Centuries Points: 1031 More actions · Answer 6

thanks for you replay Jeff i checked out that best practices article and have generated the following code to create the necessary table and insert the data thanks for the advice

-- Create table --

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[TrainingLog](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Date] [datetime] NOT NULL,

[PersonID] [int] NULL

CONSTRAINT [PK_TrainingLog] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT TrainingLog ON

--===== Insert the test data into the test table

INSERT INTO TrainingLog

(ID, Date, PersonID)

SELECT '1040','Sep 27 2008 10:00AM','323' UNION ALL

SELECT '1041','Sep 27 2008 12:30PM','19549' UNION ALL

SELECT '1042','Sep 27 2008 1:30PM','19264' UNION ALL

SELECT '1043','Sep 27 2008 2:30PM','19267' UNION ALL

SELECT '1044','Sep 27 2008 9:30AM','317' UNION ALL

SELECT '1045','Sep 27 2008 9:00AM','24756' UNION ALL

SELECT '1046','Sep 27 2008 9:30AM','3438' UNION ALL

SELECT '1047','Sep 27 2008 10:00AM','8810' UNION ALL

SELECT '1048','Sep 27 2008 9:30AM','3438' UNION ALL

SELECT '1049','Sep 27 2008 9:30AM','24752' UNION ALL

SELECT '1050','Sep 27 2008 11:00AM','3341' UNION ALL

SELECT '1051','Sep 27 2008 1:00PM','18081' UNION ALL

SELECT '1052','Sep 27 2008 2:00PM','608' UNION ALL

SELECT '1053','Sep 27 2008 2:00PM','608' UNION ALL

SELECT '1054','Sep 29 2008 10:30AM','310' UNION ALL

SELECT '1055','Sep 29 2008 10:00AM','310' UNION ALL

SELECT '1056','Sep 29 2008 12:00PM','206' UNION ALL

SELECT '1057','Sep 29 2008 1:00PM','10225' UNION ALL

SELECT '1058','Sep 29 2008 1:00PM','10225' UNION ALL

SELECT '1059','Sep 29 2008 10:00AM','303' UNION ALL

SELECT '1060','Sep 29 2008 2:00PM','19266' UNION ALL

SELECT '1061','Sep 29 2008 2:30PM','19141' UNION ALL

SELECT '1062','Sep 29 2008 2:30PM','19264' UNION ALL

SELECT '1063','Sep 29 2008 11:00AM','323' UNION ALL

SELECT '1064','Sep 29 2008 12:00PM','3725' UNION ALL

SELECT '1065','Sep 29 2008 9:00AM','22434' UNION ALL

SELECT '1066','Sep 29 2008 10:00AM','8810' UNION ALL

SELECT '1067','Sep 29 2008 10:00AM','3438' UNION ALL

SELECT '1068','Sep 27 2008 11:30AM','22434' UNION ALL

SELECT '1069','Sep 29 2008 9:00AM','18076' UNION ALL

SELECT '1070','Sep 27 2008 11:30AM','24756' UNION ALL

SELECT '1071','Sep 29 2008 10:00AM','8810' UNION ALL

SELECT '1072','Sep 29 2008 9:00AM','3341' UNION ALL

SELECT '1073','Sep 29 2008 11:00AM','3754' UNION ALL

SELECT '1074','Sep 29 2008 12:00PM','13808' UNION ALL

SELECT '1075','Sep 29 2008 1:00PM','18080' UNION ALL

SELECT '1076','Sep 29 2008 10:00AM','18080' UNION ALL

SELECT '1077','Sep 29 2008 2:00PM','19267' UNION ALL

SELECT '1078','Sep 29 2008 3:00PM','19265' UNION ALL

SELECT '1079','Sep 29 2008 12:00PM','3341' UNION ALL

SELECT '1080','Sep 29 2008 12:00PM','22434' UNION ALL

SELECT '1081','Sep 29 2008 9:00AM','22432' UNION ALL

SELECT '1082','Sep 30 2008 9:00AM','10225' UNION ALL

SELECT '1083','Sep 30 2008 10:30AM','8810' UNION ALL

SELECT '1084','Sep 30 2008 11:00AM','310' UNION ALL

SELECT '1085','Sep 30 2008 9:30AM','22434' UNION ALL

SELECT '1086','Sep 30 2008 12:30PM','310' UNION ALL

SELECT '1088','Sep 30 2008 10:00AM','315' UNION ALL

SELECT '1089','Oct 1 2008 10:00AM','310' UNION ALL

SELECT '1090','Sep 30 2008 11:00AM','3341' UNION ALL

SELECT '1091','Sep 30 2008 10:30AM','306' UNION ALL

SELECT '1092','Oct 1 2008 11:30AM','306' UNION ALL

SELECT '1093','Sep 30 2008 12:30PM','19549' UNION ALL

SELECT '1094','Oct 1 2008 2:00PM','22434' UNION ALL

SELECT '1095','Oct 1 2008 9:00AM','18076' UNION ALL

SELECT '1097','Oct 1 2008 9:30AM','18079' UNION ALL

SELECT '1098','Oct 1 2008 12:00PM','22433' UNION ALL

SELECT '1099','Oct 1 2008 1:00PM','3754' UNION ALL

SELECT '1100','Oct 1 2008 2:00PM','19266' UNION ALL

SELECT '1101','Oct 1 2008 11:00AM','310' UNION ALL

SELECT '1102','Oct 1 2008 10:00AM','8810' UNION ALL

SELECT '1103','Oct 1 2008 10:30AM','306' UNION ALL

SELECT '1104','Oct 1 2008 10:00AM','303' UNION ALL

SELECT '1105','Oct 1 2008 12:00PM','310' UNION ALL

SELECT '1106','Oct 1 2008 12:00PM','310' UNION ALL

SELECT '1107','Oct 1 2008 12:00PM','310' UNION ALL

SELECT '1108','Oct 1 2008 1:00PM','323' UNION ALL

SELECT '1109','Sep 30 2008 9:30AM','3591' UNION ALL

SELECT '1110','Sep 30 2008 10:00AM','13808' UNION ALL

SELECT '1111','Sep 30 2008 10:00AM','3438' UNION ALL

SELECT '1112','Oct 1 2008 10:00AM','3438' UNION ALL

SELECT '1113','Oct 1 2008 10:00AM','308' UNION ALL

SELECT '1114','Oct 1 2008 3:00PM','24752' UNION ALL

SELECT '1115','Sep 30 2008 11:00AM','18080' UNION ALL

SELECT '1116','Sep 30 2008 1:00PM','19' UNION ALL

SELECT '1117','Sep 30 2008 3:00PM','3341' UNION ALL

SELECT '1118','Oct 1 2008 9:00AM','22432' UNION ALL

SELECT '1119','Oct 1 2008 9:30AM','317' UNION ALL

SELECT '1120','Oct 1 2008 10:00AM','9998' UNION ALL

SELECT '1121','Oct 1 2008 11:00AM','18080' UNION ALL

SELECT '1122','Sep 30 2008 10:00AM','8810' UNION ALL

SELECT '1123','Oct 1 2008 9:30AM','3341' UNION ALL

SELECT '1124','Oct 1 2008 1:00PM','18081' UNION ALL

SELECT '1125','Sep 30 2008 9:00AM','18076' UNION ALL

SELECT '1126','Oct 1 2008 2:30PM','19263' UNION ALL

SELECT '1127','Oct 2 2008 9:00AM','10225' UNION ALL

SELECT '1128','Oct 2 2008 9:30AM','13808' UNION ALL

SELECT '1129','Oct 2 2008 10:00AM','19' UNION ALL

SELECT '1130','Oct 2 2008 10:00AM','18076' UNION ALL

SELECT '1131','Oct 2 2008 10:30AM','18076' UNION ALL

SELECT '1132','Oct 1 2008 10:30AM','310' UNION ALL

SELECT '1133','Oct 2 2008 10:30AM','19266' UNION ALL

SELECT '1134','Sep 16 2008 10:00AM','365'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT TrainingLog OFF

i have also stripped back the sql so it will execute using only one table, hopefully this will make it easier

Select tl.PersonID as 'playerId',

Cast(Case when DATENAME(MONTH, tl.Date) = 'July' then Count(tl.PersonID) end as nvarchar) as Jul,

Cast(Case when DATENAME(MONTH, tl.Date) = 'August' then Count(tl.PersonID) end as nvarchar) as Aug,

Cast(Case when DATENAME(MONTH, tl.Date) = 'September' then Count(tl.PersonID) end as nvarchar) as Sep,

Cast(Case when DATENAME(MONTH, tl.Date) = 'October' then Count(tl.PersonID) end as nvarchar) as Oct

From TrainingLog tl

Group By tl.PersonID, DATENAME(MONTH, tl.Date)

order by tl.PersonID

Thanks again

Tim

tadhg88 Ten Centuries Points: 1031 More actions · Answer 7

I managed to figure it out anyway cheers

Select playerId,

IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',

SUM(CASE WHEN Months = 'July' THEN 1 ELSE null END) AS [Jul],

SUM(CASE WHEN Months = 'August' THEN 1 ELSE null END) AS [Aug],

SUM(CASE WHEN Months = 'September' THEN 1 ELSE null END) AS [Sep],

SUM(CASE WHEN Months = 'October' THEN 1 ELSE null END) AS [Oct],

SUM(CASE WHEN Months = 'November' THEN 1 ELSE null END) AS [Nov],

SUM(CASE WHEN Months = 'December' THEN 1 ELSE null END) AS [Dec],

SUM(CASE WHEN Months = 'January' THEN 1 ELSE null END) AS [Jan],

SUM(CASE WHEN Months = 'February' THEN 1 ELSE null END) AS [Feb],

SUM(CASE WHEN Months = 'March' THEN 1 ELSE null END) AS [Mar],

SUM(CASE WHEN Months = 'April' THEN 1 ELSE null END) AS [Apr],

SUM(CASE WHEN Months = 'May' THEN 1 ELSE null END) AS [May],

SUM(CASE WHEN Months = 'June' THEN 1 ELSE null END) AS [Jun]

From (Select tl.PersonID as 'playerId',

DATENAME(MONTH, tl.Date) as 'Months'

From TrainingLog tl

) as d

Join Person p on p.ID = playerId

Group By playerId, p.firstName, p.MiddleName, p.LastName

Order by playerId

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Almost perfect... well done. Now, lemme ask you a question... what do you think will happen when the year in your data changes? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

tadhg88 Ten Centuries Points: 1031 More actions · Answer 9

it will all be completely wrong ha ha, no its ok this is not the fully finished code the season/year is taken into account later

Jerry Hung SSChampion Points: 12908 More actions · Answer 10

Perhaps it's time to look into PIVOT function that's built for this purpose?

You can group by YEAR, PIVOT by months

Unless you are using Reporting Service, then just use a Matrix table, no need to PIVOT in the SQL

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

PIVOT would likely be slower than a good ol' Cross-Tab. See the following... peformance charts are near the end of the article...

http://qa.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions