Tuesday, March 27, 2012
Duplicate Records in a View
I am having a problem with some SQL and hope someone out there can help. I
am fairly new so apologise any bad coding etc.
I have created a View, which works fine except I need to eliminate some
duplicate entries and I have tried using distinct but my problem seems
outside of it's capabilities.
The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
As you can see from this example, ID 1 and ID 14 are duplicated because
there are two tutors (TUT ID) for these courses. The entire rows are not
duplicated because of the differing TUT ID so distinct will not work (unless
I am using it wrong).
I need to have the output as is now, but show only a single instance of each
ID regardless of how many tutors the courses may have.
How can I achieve this?
Thanks
Keith
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Keith" <@..> wrote in message news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi There
> I am having a problem with some SQL and hope someone out there can help.
I
> am fairly new so apologise any bad coding etc.
> I have created a View, which works fine except I need to eliminate some
> duplicate entries and I have tried using distinct but my problem seems
> outside of it's capabilities.
> The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
> The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
> As you can see from this example, ID 1 and ID 14 are duplicated because
> there are two tutors (TUT ID) for these courses. The entire rows are not
> duplicated because of the differing TUT ID so distinct will not work
(unless
> I am using it wrong).
> I need to have the output as is now, but show only a single instance of
each
> ID regardless of how many tutors the courses may have.
> How can I achieve this?
> Thanks
>
|||Thanks
I don't want to list the duplicates though - I want to list everything
EXCEPT the duplicates.
I couldn't figure out (being a newbie) how to do this.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ON%23ucxWQEHA.236@.TK2MSFTNGP10.phx.gbl...
> Keith
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Keith" <@..> wrote in message
news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> I
not
> (unless
> each
>
Duplicate Records in a View
I have had a look at the data and have come to the
conclusion that it is not duplicated in any way.
The reason is that if you check each row in the output
they are all in some way unique. For instance id 14 (which
as in twice) has a different TUT ID.
To give you some sort of solution then we need to know
what do you actually want as un duplicated data.
Thanks
J
>--Original Message--
>Hi There
>I am having a problem with some SQL and hope someone out
there can help. I
>am fairly new so apologise any bad coding etc.
>I have created a View, which works fine except I need to
eliminate some
>duplicate entries and I have tried using distinct but my
problem seems
>outside of it's capabilities.
>The SQL is here: http://www.step-
online.org.uk/sql/Courses_SQL.png
>The Output is here: http://www.step-
online.org.uk/sql/Courses_Output.png
>As you can see from this example, ID 1 and ID 14 are
duplicated because
>there are two tutors (TUT ID) for these courses. The
entire rows are not
>duplicated because of the differing TUT ID so distinct
will not work (unless
>I am using it wrong).
>I need to have the output as is now, but show only a
single instance of each
>ID regardless of how many tutors the courses may have.
>How can I achieve this?
>Thanks
>
>.
>Hi and Thanks for looking
I know that the TUT ID is different.
I want to ignore this though and exclude all the records which are duplicate
(even if they have different TUT IDs).
TUT ID is the tutor assigned to the course, so a record is showing up twice
if there are two tutors on the course. I only want to show the record once,
regardless of how many tutors (TUT IDs) there are for each course.
For my purposes, a duplicate record is one where there is another record
where everything is identical EXCEPT the TUT ID, because there can be many
tutors on the same course.
Thanks
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1160e01c4419c$2926d190$a101280a@.phx
.gbl...[vbcol=seagreen]
> Dear Keith
> I have had a look at the data and have come to the
> conclusion that it is not duplicated in any way.
> The reason is that if you check each row in the output
> they are all in some way unique. For instance id 14 (which
> as in twice) has a different TUT ID.
> To give you some sort of solution then we need to know
> what do you actually want as un duplicated data.
> Thanks
> J
>
> there can help. I
> eliminate some
> problem seems
> online.org.uk/sql/Courses_SQL.png
> online.org.uk/sql/Courses_Output.png
> duplicated because
> entire rows are not
> will not work (unless
> single instance of each|||Any suggestions would be great.
Thanks
"Keith" <@..> wrote in message
news:um6a%23eaQEHA.1620@.TK2MSFTNGP12.phx.gbl...
> Hi and Thanks for looking
> I know that the TUT ID is different.
> I want to ignore this though and exclude all the records which are
duplicate
> (even if they have different TUT IDs).
> TUT ID is the tutor assigned to the course, so a record is showing up
twice
> if there are two tutors on the course. I only want to show the record
once,
> regardless of how many tutors (TUT IDs) there are for each course.
> For my purposes, a duplicate record is one where there is another record
> where everything is identical EXCEPT the TUT ID, because there can be many
> tutors on the same course.
> Thanks
>
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:1160e01c4419c$2926d190$a101280a@.phx
.gbl...
>
Duplicate Records in a View
I am having a problem with some SQL and hope someone out there can help. I
am fairly new so apologise any bad coding etc.
I have created a View, which works fine except I need to eliminate some
duplicate entries and I have tried using distinct but my problem seems
outside of it's capabilities.
The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
As you can see from this example, ID 1 and ID 14 are duplicated because
there are two tutors (TUT ID) for these courses. The entire rows are not
duplicated because of the differing TUT ID so distinct will not work (unless
I am using it wrong).
I need to have the output as is now, but show only a single instance of each
ID regardless of how many tutors the courses may have.
How can I achieve this?
ThanksKeith
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Keith" <@..> wrote in message news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi There
> I am having a problem with some SQL and hope someone out there can help.
I
> am fairly new so apologise any bad coding etc.
> I have created a View, which works fine except I need to eliminate some
> duplicate entries and I have tried using distinct but my problem seems
> outside of it's capabilities.
> The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
> The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
> As you can see from this example, ID 1 and ID 14 are duplicated because
> there are two tutors (TUT ID) for these courses. The entire rows are not
> duplicated because of the differing TUT ID so distinct will not work
(unless
> I am using it wrong).
> I need to have the output as is now, but show only a single instance of
each
> ID regardless of how many tutors the courses may have.
> How can I achieve this?
> Thanks
>|||Thanks
I don't want to list the duplicates though - I want to list everything
EXCEPT the duplicates.
I couldn't figure out (being a newbie) how to do this.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ON%23ucxWQEHA.236@.TK2MSFTNGP10.phx.gbl...
> Keith
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Keith" <@..> wrote in message
news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> I
not[vbcol=seagreen]
> (unless
> each
>
Duplicate Records in a View
I have had a look at the data and have come to the
conclusion that it is not duplicated in any way.
The reason is that if you check each row in the output
they are all in some way unique. For instance id 14 (which
as in twice) has a different TUT ID.
To give you some sort of solution then we need to know
what do you actually want as un duplicated data.
Thanks
J
>--Original Message--
>Hi There
>I am having a problem with some SQL and hope someone out
there can help. I
>am fairly new so apologise any bad coding etc.
>I have created a View, which works fine except I need to
eliminate some
>duplicate entries and I have tried using distinct but my
problem seems
>outside of it's capabilities.
>The SQL is here: http://www.step-
online.org.uk/sql/Courses_SQL.png
>The Output is here: http://www.step-
online.org.uk/sql/Courses_Output.png
>As you can see from this example, ID 1 and ID 14 are
duplicated because
>there are two tutors (TUT ID) for these courses. The
entire rows are not
>duplicated because of the differing TUT ID so distinct
will not work (unless
>I am using it wrong).
>I need to have the output as is now, but show only a
single instance of each
>ID regardless of how many tutors the courses may have.
>How can I achieve this?
>Thanks
>
>.
>
Hi and Thanks for looking
I know that the TUT ID is different.
I want to ignore this though and exclude all the records which are duplicate
(even if they have different TUT IDs).
TUT ID is the tutor assigned to the course, so a record is showing up twice
if there are two tutors on the course. I only want to show the record once,
regardless of how many tutors (TUT IDs) there are for each course.
For my purposes, a duplicate record is one where there is another record
where everything is identical EXCEPT the TUT ID, because there can be many
tutors on the same course.
Thanks
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1160e01c4419c$2926d190$a101280a@.phx.gbl...[vbcol=seagreen]
> Dear Keith
> I have had a look at the data and have come to the
> conclusion that it is not duplicated in any way.
> The reason is that if you check each row in the output
> they are all in some way unique. For instance id 14 (which
> as in twice) has a different TUT ID.
> To give you some sort of solution then we need to know
> what do you actually want as un duplicated data.
> Thanks
> J
>
> there can help. I
> eliminate some
> problem seems
> online.org.uk/sql/Courses_SQL.png
> online.org.uk/sql/Courses_Output.png
> duplicated because
> entire rows are not
> will not work (unless
> single instance of each
|||Any suggestions would be great.
Thanks
"Keith" <@..> wrote in message
news:um6a%23eaQEHA.1620@.TK2MSFTNGP12.phx.gbl...
> Hi and Thanks for looking
> I know that the TUT ID is different.
> I want to ignore this though and exclude all the records which are
duplicate
> (even if they have different TUT IDs).
> TUT ID is the tutor assigned to the course, so a record is showing up
twice
> if there are two tutors on the course. I only want to show the record
once,
> regardless of how many tutors (TUT IDs) there are for each course.
> For my purposes, a duplicate record is one where there is another record
> where everything is identical EXCEPT the TUT ID, because there can be many
> tutors on the same course.
> Thanks
>
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:1160e01c4419c$2926d190$a101280a@.phx.gbl...
>
sql
Duplicate Records in a View
I am having a problem with some SQL and hope someone out there can help. I
am fairly new so apologise any bad coding etc.
I have created a View, which works fine except I need to eliminate some
duplicate entries and I have tried using distinct but my problem seems
outside of it's capabilities.
The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
As you can see from this example, ID 1 and ID 14 are duplicated because
there are two tutors (TUT ID) for these courses. The entire rows are not
duplicated because of the differing TUT ID so distinct will not work (unless
I am using it wrong).
I need to have the output as is now, but show only a single instance of each
ID regardless of how many tutors the courses may have.
How can I achieve this?
ThanksKeith
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Keith" <@..> wrote in message news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi There
> I am having a problem with some SQL and hope someone out there can help.
I
> am fairly new so apologise any bad coding etc.
> I have created a View, which works fine except I need to eliminate some
> duplicate entries and I have tried using distinct but my problem seems
> outside of it's capabilities.
> The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
> The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
> As you can see from this example, ID 1 and ID 14 are duplicated because
> there are two tutors (TUT ID) for these courses. The entire rows are not
> duplicated because of the differing TUT ID so distinct will not work
(unless
> I am using it wrong).
> I need to have the output as is now, but show only a single instance of
each
> ID regardless of how many tutors the courses may have.
> How can I achieve this?
> Thanks
>|||Thanks
I don't want to list the duplicates though - I want to list everything
EXCEPT the duplicates.
I couldn't figure out (being a newbie) how to do this.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ON%23ucxWQEHA.236@.TK2MSFTNGP10.phx.gbl...
> Keith
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Keith" <@..> wrote in message
news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> > Hi There
> >
> > I am having a problem with some SQL and hope someone out there can help.
> I
> > am fairly new so apologise any bad coding etc.
> >
> > I have created a View, which works fine except I need to eliminate some
> > duplicate entries and I have tried using distinct but my problem seems
> > outside of it's capabilities.
> >
> > The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
> >
> > The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
> >
> > As you can see from this example, ID 1 and ID 14 are duplicated because
> > there are two tutors (TUT ID) for these courses. The entire rows are
not
> > duplicated because of the differing TUT ID so distinct will not work
> (unless
> > I am using it wrong).
> >
> > I need to have the output as is now, but show only a single instance of
> each
> > ID regardless of how many tutors the courses may have.
> >
> > How can I achieve this?
> >
> > Thanks
> >
> >
>|||Dear Keith
I have had a look at the data and have come to the
conclusion that it is not duplicated in any way.
The reason is that if you check each row in the output
they are all in some way unique. For instance id 14 (which
as in twice) has a different TUT ID.
To give you some sort of solution then we need to know
what do you actually want as un duplicated data.
Thanks
J
>--Original Message--
>Hi There
>I am having a problem with some SQL and hope someone out
there can help. I
>am fairly new so apologise any bad coding etc.
>I have created a View, which works fine except I need to
eliminate some
>duplicate entries and I have tried using distinct but my
problem seems
>outside of it's capabilities.
>The SQL is here: http://www.step-
online.org.uk/sql/Courses_SQL.png
>The Output is here: http://www.step-
online.org.uk/sql/Courses_Output.png
>As you can see from this example, ID 1 and ID 14 are
duplicated because
>there are two tutors (TUT ID) for these courses. The
entire rows are not
>duplicated because of the differing TUT ID so distinct
will not work (unless
>I am using it wrong).
>I need to have the output as is now, but show only a
single instance of each
>ID regardless of how many tutors the courses may have.
>How can I achieve this?
>Thanks
>
>.
>|||Hi and Thanks for looking
I know that the TUT ID is different.
I want to ignore this though and exclude all the records which are duplicate
(even if they have different TUT IDs).
TUT ID is the tutor assigned to the course, so a record is showing up twice
if there are two tutors on the course. I only want to show the record once,
regardless of how many tutors (TUT IDs) there are for each course.
For my purposes, a duplicate record is one where there is another record
where everything is identical EXCEPT the TUT ID, because there can be many
tutors on the same course.
Thanks
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1160e01c4419c$2926d190$a101280a@.phx.gbl...
> Dear Keith
> I have had a look at the data and have come to the
> conclusion that it is not duplicated in any way.
> The reason is that if you check each row in the output
> they are all in some way unique. For instance id 14 (which
> as in twice) has a different TUT ID.
> To give you some sort of solution then we need to know
> what do you actually want as un duplicated data.
> Thanks
> J
>
> >--Original Message--
> >Hi There
> >
> >I am having a problem with some SQL and hope someone out
> there can help. I
> >am fairly new so apologise any bad coding etc.
> >
> >I have created a View, which works fine except I need to
> eliminate some
> >duplicate entries and I have tried using distinct but my
> problem seems
> >outside of it's capabilities.
> >
> >The SQL is here: http://www.step-
> online.org.uk/sql/Courses_SQL.png
> >
> >The Output is here: http://www.step-
> online.org.uk/sql/Courses_Output.png
> >
> >As you can see from this example, ID 1 and ID 14 are
> duplicated because
> >there are two tutors (TUT ID) for these courses. The
> entire rows are not
> >duplicated because of the differing TUT ID so distinct
> will not work (unless
> >I am using it wrong).
> >
> >I need to have the output as is now, but show only a
> single instance of each
> >ID regardless of how many tutors the courses may have.
> >
> >How can I achieve this?
> >
> >Thanks
> >
> >
> >.
> >|||Any suggestions would be great.
Thanks
"Keith" <@..> wrote in message
news:um6a%23eaQEHA.1620@.TK2MSFTNGP12.phx.gbl...
> Hi and Thanks for looking
> I know that the TUT ID is different.
> I want to ignore this though and exclude all the records which are
duplicate
> (even if they have different TUT IDs).
> TUT ID is the tutor assigned to the course, so a record is showing up
twice
> if there are two tutors on the course. I only want to show the record
once,
> regardless of how many tutors (TUT IDs) there are for each course.
> For my purposes, a duplicate record is one where there is another record
> where everything is identical EXCEPT the TUT ID, because there can be many
> tutors on the same course.
> Thanks
>
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:1160e01c4419c$2926d190$a101280a@.phx.gbl...
> > Dear Keith
> >
> > I have had a look at the data and have come to the
> > conclusion that it is not duplicated in any way.
> >
> > The reason is that if you check each row in the output
> > they are all in some way unique. For instance id 14 (which
> > as in twice) has a different TUT ID.
> >
> > To give you some sort of solution then we need to know
> > what do you actually want as un duplicated data.
> >
> > Thanks
> > J
> >
> >
> > >--Original Message--
> > >Hi There
> > >
> > >I am having a problem with some SQL and hope someone out
> > there can help. I
> > >am fairly new so apologise any bad coding etc.
> > >
> > >I have created a View, which works fine except I need to
> > eliminate some
> > >duplicate entries and I have tried using distinct but my
> > problem seems
> > >outside of it's capabilities.
> > >
> > >The SQL is here: http://www.step-
> > online.org.uk/sql/Courses_SQL.png
> > >
> > >The Output is here: http://www.step-
> > online.org.uk/sql/Courses_Output.png
> > >
> > >As you can see from this example, ID 1 and ID 14 are
> > duplicated because
> > >there are two tutors (TUT ID) for these courses. The
> > entire rows are not
> > >duplicated because of the differing TUT ID so distinct
> > will not work (unless
> > >I am using it wrong).
> > >
> > >I need to have the output as is now, but show only a
> > single instance of each
> > >ID regardless of how many tutors the courses may have.
> > >
> > >How can I achieve this?
> > >
> > >Thanks
> > >
> > >
> > >.
> > >
>
Thursday, March 22, 2012
Duplicate Counts per column
example I have a table with the following structure:
CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
the data loaded looks like this
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
the results I need are
SiteIdx 3 Digit Count 4 Digit Count
--- ---- ----
1 2 0
2 2 0
I've tried various queries the closest being:
SELECT DISTINCT SiteIdx, COUNT(*) as "3-Digit Count"
FROM dbo.TestDpln
GROUP BY SiteIdx, "3-Digit"
HAVING COUNT(*) > 1
but it only shows one column and one site I'm not sure how to get the '4 Digit Count' column to show up and the rest of the sites. below are the results I get so far.
SiteIdx 3 Digit Count
--- ----
1 2
any help would be great.
Thanks
MikeTry:
COUNT(DISTINCT [YourColumn])|||That did not work I was thinking it would need to be some sort of select statement run on each column then grouped by the siteidx then a count done by site. I'm just not sure how to write it.|||Your data and required output doesn't agree
3d 4d idx
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
No duplicates here but you want 2 0 1
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
and no duplicates here but you want 2 0 2
And then you also ask how to display the rest of the sites
So do you want all zeros returned if a siteidx has no duplicates?|||How do you expect to get 4-Digit counts of zero from the data you supplied, which obviously contains multiple 4-Digit count values?|||Sorry for being so unclear it was a long day and not enough coffee
First the piece that I forgot to put in is that there is an input string that we pass in and are searching for such as '51212' this string is then broken down in to 3 digits from the right giving the search string for the 3 digit column of 212 and then four digits from the right giving the search string for the four digit column of 1212
so with this information and some updated data this is what I'm looking for
Input string 51212
3d 4d Site
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2212','212','2212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2213','213','2213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
3 digit 212 duplicates would be 4
4 digit 1212 duplicates would be 2
But the final output should look like
SiteIdx 3digit 4digit
--- -- --
1 4 2|||I hope this example is better|||You sure pick some bad column names.
set nocount on
CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
))
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2212','212','2212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2213','213','2213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
declare @.InputString char(5)
set @.InputString = '51212'
select SiteIDx,
sum(case when [3-Digit] = right(@.InputString, 3) then 1 else 0 end) as '3digit',
sum(case when [4-Digit] = right(@.InputString, 4) then 1 else 0 end) as '4digit'
from [dbo].[TestDpln]
group by SiteIDx
drop table [dbo].[TestDpln]|||That worked great a million thanks to you I never would have thought about using sum for this.
P.S.
I know the column names are bad I inherited this from someone who was trying to use MSACCESS for this data and now I need to really make it work on MSSQL we are redoing the whole schema as we build this.
Monday, March 19, 2012
Dumb question: How do I view a report?
for my username and password even though I'm using Windows integrated
security for SQL Server. What do I need to do to create/access reports?Never mind. I'd typed in http://localhost/ReportServices$SQLSERVER2005 when
I was prompted for a username and password. I should've typed in
http://localhost/Reports$SQLSERVER2005/Pages/ReportViewer.aspx ...
As I said, it's a dumb question ... hahaha.
"AlBruAn" wrote:
> I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
> for my username and password even though I'm using Windows integrated
> security for SQL Server. What do I need to do to create/access reports?|||On Apr 9, 1:22 pm, AlBruAn <albr...@.hotmail.com.(donotspam)> wrote:
> I have SSRS installed, but when I try to navigate to it via IE, I'm prompted
> for my username and password even though I'm using Windows integrated
> security for SQL Server. What do I need to do to create/access reports?
If you are using a browser other than IE, this may occur. If not, then
you will want to check the Directory Security (for the Reports and
ReportServer virtual directories). You do this via: Right-click My
Computer -> Select Manage -> Navigate to Services and Applications ->
Internet Information Services -> Web Sites -> Default Web Site. Then,
right-click Reports (or ReportServer) and select Properties -> select
the Directory Security tab -> select the Edit button just below
'Anonymous access and authentication control' and check to see if you
have either 'Anonymous access' selected or 'Integrated Windows
Authentication.' If anonymous is selected, most likely SSRS will not
allow you to avoid providing authentication (if it allows navigation
to the URL at all). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Friday, February 17, 2012
DTS vs BCP vs TSQL
Hi,
As I know, although DTS gives graphical view of the data transformation/conversion work flow, it tend to be slower than BCP and TSQL scripts. So, keeping all that in mind, can I still use DTS (b'cos its a good tool to illustrate what’s happening inside, easy to understand and maintain) but with TSQL scripts (i.e: insert into...) doing the operations instead of DTS copy column or vb scripting, to gain maximum performance provided by the TSQL scripts? If you have other view points pls advice., What is the possibility of calling BCP exe inside DTS ?.
Let me provide you info. about the work I need to do, following are the things I need to do;
1. SQL source to SQL destination (different servers)
2. Transformations same SQL server and the same database
3. SQL source to text files.
Thanx
According to what I found, Yes you can call BCP inside DTS, which is very handy. Furthermore BCP and copy column transformations gave almost same performance.
|||Based on what you need, you definitely will need DTS for the ransformation. BCP is just to prepare your data for bulk copy into staging tables. Scrub and transform your data using DTSDTS Transfer of 'view over view' fails
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.
There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
DTS Transfer of 'view over view' fails
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
Wednesday, February 15, 2012
DTS Transfer of 'view over view' fails
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
Dts transfer of a 'view over a view'
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.Sorry for the inadvertent re-post.
Paul.
--
"Paul W" <qqq@.qqq.com> wrote in message
news:eQL9hW3EFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
>
Dts transfer of a 'view over a view'
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.
Sorry for the inadvertent re-post.
Paul.
"Paul W" <qqq@.qqq.com> wrote in message
news:eQL9hW3EFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
>
Dts transfer of a 'view over a view'
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.Sorry for the inadvertent re-post.
Paul.
--
"Paul W" <qqq@.qqq.com> wrote in message
news:eQL9hW3EFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
>
DTS to Excel, $0
I am exporting some data from a view to an excel file though a DTS. $0 amounts come as -0, what is the reason for that an dhow can I fix it?
Jim,
When I try a straight export with money data equalling zero
or '$0' as strings, either from SQL Server 2005 or 2000 (using
the Import/Export Data wizard in both cases), I get $0.00
or $0 in the Excel file.
Can you be more specific about what you are seeing? What is the
view definition? Is the problem column a table column declared
as money/smallmoney in SQL Server, an expression, or what? Are
you using a wizard or another method to do the export? When you
select from the view in SQL Server, do you see .0000, or something
else?
You haven't given us much to go on.
Steve Kass
Drew University
www.stevekass.com
JIM.H.@.discussions.microsoft.com wrote:
> I am exporting some data from a view to an excel file though a DTS. $0
> amounts come as -0, what is the reason for that an dhow can I fix it?
>
>
|||DTS is using the view. When I use the view I do not see -0. But the exported data has -0. If I format the column in excel for 2 decimals, it becomes 0.00 so the negative drops. Maybe DTS considers a long decimal after the point and assumes it is negative.
|||You haven't really answered some of the relevant questions:
Is the problem column a table column declared as money/smallmoney
in SQL Server, an expression, or what? Are you using a wizard
or another method to do the export?
SK
JIM.H.@.discussions.microsoft.com wrote:
> DTS is using the view. When I use the view I do not see -0. But the
> exported data has -0. If I format the column in excel for 2 decimals, it
> becomes 0.00 so the negative drops. Maybe DTS considers a long decimal
> after the point and assumes it is negative.
>
>
|||Thanks for your help. The column is a float in the table. I used the DTS wizard.|||Apparently the number is a very small negative number. When such a number
is represented in Excel with only a few decimal places, Excel can't show
its absolute value differently from zero, but Excel could display its
sign correctly, yet doesn't. Excel displays the number as negative for
some formats and not for others. I would consider this a
bug, and you might wish to report it. (I don't know where, but if you
post this in an Excel newsgroup someone will probably tell you.)
I reproduced the fleeting - as follows:
In an Excel cell, type -0.00000001, the format the cell in these ways:
Number (2 decimal places). The result is 0.00 [not negative]
Currency (2 decimal places, with () for negatives. The result is $(0.00) [negative]
Currency (2 decimal places, with - for negative values. The result is $0.00 [not negative]
In particular, the currency marker shouldn't affect whether or not the number is shown as negative.
Steve Kass
Drew University
www.stevekass.com
JIM.H.@.discussions.microsoft.com wrote:
> Thanks for your help. The column is a float in the table. I used the DTS
> wizard.
>
DTS to excel, $0
I am exporting some data from a view to an excel file though a DTS. $0 amounts come as -0, what is the reason for that an dhow can I fix it?
Is the data type of the column (contains $0 values) MONEY? I export MONEY data to Currency (default mapping type) data in Excel with no problem. I'm using Excel2003+SQL2000
|||data type is float, not money. If I format excel column with 2 decimal I get 0.00 without any problem.
DTS to Excel (replace existing rows)
Hi,
I am using a DTS package to output a view to a pre-determined Excel file. Currently it just adds the output to the bottom of the current table in excel but i would like it to delete the contents of the worksheet before adding the new rows.
Any help is much appreciated.
Thanks
Greg
So in other words, if a row already exists update it, otherwise insert. Is that correct?
If so, read this: http://www.sqlis.com/default.aspx?311
-Jamie