Remove Duplicate Row
-
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
-
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.
-
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 2Column 'TrainingLog.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
-
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
-
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://www.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) -
thanks for your reply upendramkumar i have uploaded a pic of the results i am hopig to get you can view it here
Tim
-
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
-
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
-
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) -
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
-
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 -
PIVOT would likely be slower than a good ol' Cross-Tab. See the following... peformance charts are near the end of the article...
http://www.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)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply