Duplicated Rows

Hi,

I have just started developing in SQL Express in the last 2 months so still learning. The problem I’m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I’m asking.

A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.

ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS

SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],

CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],

MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'

WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'

WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'

WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'

WHEN DATEPART(MM,SessionDate)=5 THEN 'May'

WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'

WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'

WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'

WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'

WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'

WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'

WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'

FROM Sessions

GROUP BY [Job No], Sessions.SessionDate

ORDER BY [Job No]

Results in SQL Server Express

'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month'

1113-001 0 08/16/2001 08/16/2001 Aug
1113-002 0.5 07/11/2000 07/11/2000 Jul
1113-002 0.5 02/09/2000 02/09/2000 Feb
1116-001 1 07/07/1999 07/07/1999 Jul
1116-001 1 07/06/1999 07/06/1999 Jul
1118-001 1 01/12/1999 01/12/1999 Jan
1118-001 0.5 03/17/1999 03/17/1999 Mar
1118-001 1 02/23/1999 02/23/1999 Feb
1118-001 1 01/26/1999 01/26/1999 Jan
1118-001 0.5 03/09/1999 03/09/1999 Mar
1118-001 1 12/15/1998 12/15/1998 Dec
1118-001 1 02/09/1999 02/09/1999 Feb

Results in Ms Access

Days Done per Job

JobNo

Days to Date

Start Date

End Date

End Month

1113-001

0.00

16/08/2001

16/08/2001

Aug01

1113-002

1.00

09/02/2000

11/07/2000

Jul00

1116-001

2.00

06/07/1999

07/07/1999

Jul99

1118-001

6.00

15/12/1998

17/03/1999

Mar99

Not being an expert by any means, my hunch is that you should set up the first column as a primary key. It will never allow duplicate values after that. It will take you a second to do it in the SQL database table. Right click on the column name in designer. It may not eliminate the problem completely in a sense that there might be a bug in your code that does it. At least you will get an error message from the server protesting the fact that somebody is sending duplicates to it. It will be a cue for you where to look.|||Cheers Alex will give it a go.

Duplicate Records

Hi Folks,

I am new to SQl so need help in developing Stored procedure. What I am trying to do is, I have a big table of demographic information(more than 500000 records) for our customers. the table has lots of duplicates. It is because of typos. The unique key in the table is contactid. I have to write a code which reads through the records in the table and look for duplicates on different matchng criteria like firtsname, last name or phone no match etc. and assign a new id in a diffrent table with that contactid.
Like I have to open a recordset which reads through the first record and put that contactid and assign a new id(Let's call it personid) in another table(This table will have only two fields) then it will read the next record and see if we already have found this person if we have it will pick up that person id and will enter a record with that personid and its contactid and then it will go to the next record until done with all. So, by the end we will have one more table where we will have all the conatctids with their corresponding personid.

Plaese help me write some kind of store procedure in which I can do this.

Thank for the help.There are much faster and better ways to do this than by stepping through your records one at a time, but which is best depends on some specifics of your task.

One solution would be:

Select Max(PrimaryKey), Column1, Column2, Column3...
From YourOldTable
Into YourNewTable
Group By Colum1, Column2, Column3...

Other solutions involve select statements that join two instances of your table and match them on selected columns.

Is your goal to eliminate duplicates after updating related tables that have obsolete key values, or are you just creating a lookup table?

blindman|||I am not trying to eliminate duplicates. I want to create a lookup table. I want to run the several kinds of matches like first I want it on firstname, last name and zipcode, the next query will be on just the phone nos and next query should be on email. My loookup table will have only contactid and personid(one to many relationship) which means for several conatctids we have one personid. My ultimte gaol is to get the unique counts of contacts in the table.

Azra

Dumb question - MSSql - what is the front end?

Hello there.
If I am developing an application that I want to use MSSQL 2000 Server as
the back end (Which I get as part of SBS 2003) what is the actual front end
to a MS SQL database?
I know Access is an option, but I am of the understanding that some people
say this is geared towards smaller applications.
I also know one can go away and develop a specific VB application that uses
a MSSQL server.
But if someone is going away to develop a database in MSSQL, what is the
assumed front end to make the actual forms for someone to use that database?
Would ASP.net be a modern front end?
Forgive me if this question is stupid.
Thanks
DaveWell, strictly speaking, in many current projects your browser (eg,
Internet Explorer) is your "front end", and asp.net is your
middleware.
But you're free to use Access, or to use VB.net or C# to create rich
or smart clients that go directly to SQLServer, plus or minus your own
middleware web services written as VB/C# components, then your rich
clients are smart clients. Or write Python command line or GUI apps,
or J2EE Jakarta/Struts or Atlast/Ajax, or Excel VBA, SQLServer is
happy in any case.
Josh
On Thu, 08 Jun 2006 00:39:50 GMT, "David Smithz"
<dave1900@.blueyonder.co.uk> wrote:
>Hello there.
>If I am developing an application that I want to use MSSQL 2000 Server as
>the back end (Which I get as part of SBS 2003) what is the actual front end
>to a MS SQL database?
>I know Access is an option, but I am of the understanding that some people
>say this is geared towards smaller applications.
>I also know one can go away and develop a specific VB application that uses
>a MSSQL server.
>But if someone is going away to develop a database in MSSQL, what is the
>assumed front end to make the actual forms for someone to use that database?
>Would ASP.net be a modern front end?
>Forgive me if this question is stupid.
>Thanks
>Dave
>|||Sqlserver is just a data store. Sure, you can develop a front-end (client
app) with vb/vb.net/c#/etc. to connect to sqlserver for data. If you're
going to develop something, you might as well use the current technology.
Asp.net is just a name for a web application written in .net. There's really
no difference in data access between a web app-to-sql and a typical client
app-to-sql.
Here is a link to some info that should get you started.
http://msdn.microsoft.com/asp.net/learning/learn/newtodevelopment/
-oj
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:qlKhg.82685$wl.15748@.text.news.blueyonder.co.uk...
> Hello there.
> If I am developing an application that I want to use MSSQL 2000 Server as
> the back end (Which I get as part of SBS 2003) what is the actual front
> end to a MS SQL database?
> I know Access is an option, but I am of the understanding that some people
> say this is geared towards smaller applications.
> I also know one can go away and develop a specific VB application that
> uses a MSSQL server.
> But if someone is going away to develop a database in MSSQL, what is the
> assumed front end to make the actual forms for someone to use that
> database?
> Would ASP.net be a modern front end?
> Forgive me if this question is stupid.
> Thanks
> Dave
>|||"oj" <nospam_ojngo@.home.com> wrote in message
news:uw2Z6bpiGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Sqlserver is just a data store. Sure, you can develop a front-end (client
> app) with vb/vb.net/c#/etc. to connect to sqlserver for data. If you're
> going to develop something, you might as well use the current technology.
> Asp.net is just a name for a web application written in .net. There's
> really no difference in data access between a web app-to-sql and a typical
> client app-to-sql.
> Here is a link to some info that should get you started.
> http://msdn.microsoft.com/asp.net/learning/learn/newtodevelopment/
OK thanks for that.
As an example, if someone came to you and said,
"I need to develop a database for my business. It will have about 10 users
(but 5 most of the time) and it must work quickly. I heard Java is good but
you know better then me".
Other details I know are although web access would be useful at times,
generally the database needs to run on the internal network primarily (or
anyway connected to the network via VPN)
Considering they have an installation of SBS 2003 (which I believe comes
with MS SQL 2000) what (as an example) would your answer to be to the
question.
Thanks for any input which will just help me to see if I am thinking along
the right lines compared to others with experience in this field (i.e. you
guys ;) )
Dave|||David Smithz wrote:
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uw2Z6bpiGHA.4912@.TK2MSFTNGP03.phx.gbl...
>> Sqlserver is just a data store. Sure, you can develop a front-end (client
>> app) with vb/vb.net/c#/etc. to connect to sqlserver for data. If you're
>> going to develop something, you might as well use the current technology.
>> Asp.net is just a name for a web application written in .net. There's
>> really no difference in data access between a web app-to-sql and a typical
>> client app-to-sql.
>> Here is a link to some info that should get you started.
>> http://msdn.microsoft.com/asp.net/learning/learn/newtodevelopment/
> OK thanks for that.
> As an example, if someone came to you and said,
> "I need to develop a database for my business. It will have about 10 users
> (but 5 most of the time) and it must work quickly. I heard Java is good but
> you know better then me".
> Other details I know are although web access would be useful at times,
> generally the database needs to run on the internal network primarily (or
> anyway connected to the network via VPN)
> Considering they have an installation of SBS 2003 (which I believe comes
> with MS SQL 2000) what (as an example) would your answer to be to the
> question.
> Thanks for any input which will just help me to see if I am thinking along
> the right lines compared to others with experience in this field (i.e. you
> guys ;) )
> Dave
>
Hi Dave
I'm not a programmer, but I think your question is better asked in a
programmer/developer forum. As already mentioned, SQL server just holds
the data and doesn't really care much about which program gets the data.
SQL server will just handle the request that comes from your application
and then do what's being requested. If these requests comes from a
program written in .net, C## or whatever doesn't really matter.
--
Regards
Steen Schlüter Persson
DBA

Dumb question - MSSql - what is the front end?

Hello there.
If I am developing an application that I want to use MSSQL 2000 Server as
the back end (Which I get as part of SBS 2003) what is the actual front end
to a MS SQL database?
I know Access is an option, but I am of the understanding that some people
say this is geared towards smaller applications.
I also know one can go away and develop a specific VB application that uses
a MSSQL server.
But if someone is going away to develop a database in MSSQL, what is the
assumed front end to make the actual forms for someone to use that database?
Would ASP.net be a modern front end?
Forgive me if this question is stupid.
Thanks
DaveWell, strictly speaking, in many current projects your browser (eg,
Internet Explorer) is your "front end", and asp.net is your
middleware.
But you're free to use Access, or to use VB.net or C# to create rich
or smart clients that go directly to SQLServer, plus or minus your own
middleware web services written as VB/C# components, then your rich
clients are smart clients. Or write Python command line or GUI apps,
or J2EE Jakarta/Struts or Atlast/Ajax, or Excel VBA, SQLServer is
happy in any case.
Josh
On Thu, 08 Jun 2006 00:39:50 GMT, "David Smithz"
<dave1900@.blueyonder.co.uk> wrote:

>Hello there.
>If I am developing an application that I want to use MSSQL 2000 Server as
>the back end (Which I get as part of SBS 2003) what is the actual front end
>to a MS SQL database?
>I know Access is an option, but I am of the understanding that some people
>say this is geared towards smaller applications.
>I also know one can go away and develop a specific VB application that uses
>a MSSQL server.
>But if someone is going away to develop a database in MSSQL, what is the
>assumed front end to make the actual forms for someone to use that database
?
>Would ASP.net be a modern front end?
>Forgive me if this question is stupid.
>Thanks
>Dave
>|||Sqlserver is just a data store. Sure, you can develop a front-end (client
app) with vb/vb.net/c#/etc. to connect to sqlserver for data. If you're
going to develop something, you might as well use the current technology.
Asp.net is just a name for a web application written in .net. There's really
no difference in data access between a web app-to-sql and a typical client
app-to-sql.
Here is a link to some info that should get you started.
http://msdn.microsoft.com/asp.net/l...wtodevelopment/
-oj
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:qlKhg.82685$wl.15748@.text.news.blueyonder.co.uk...
> Hello there.
> If I am developing an application that I want to use MSSQL 2000 Server as
> the back end (Which I get as part of SBS 2003) what is the actual front
> end to a MS SQL database?
> I know Access is an option, but I am of the understanding that some people
> say this is geared towards smaller applications.
> I also know one can go away and develop a specific VB application that
> uses a MSSQL server.
> But if someone is going away to develop a database in MSSQL, what is the
> assumed front end to make the actual forms for someone to use that
> database?
> Would ASP.net be a modern front end?
> Forgive me if this question is stupid.
> Thanks
> Dave
>|||"oj" <nospam_ojngo@.home.com> wrote in message
news:uw2Z6bpiGHA.4912@.TK2MSFTNGP03.phx.gbl...
> Sqlserver is just a data store. Sure, you can develop a front-end (client
> app) with vb/vb.net/c#/etc. to connect to sqlserver for data. If you're
> going to develop something, you might as well use the current technology.
> Asp.net is just a name for a web application written in .net. There's
> really no difference in data access between a web app-to-sql and a typical
> client app-to-sql.
> Here is a link to some info that should get you started.
> http://msdn.microsoft.com/asp.net/l...wtodevelopment/
OK thanks for that.
As an example, if someone came to you and said,
"I need to develop a database for my business. It will have about 10 users
(but 5 most of the time) and it must work quickly. I heard Java is good but
you know better then me".
Other details I know are although web access would be useful at times,
generally the database needs to run on the internal network primarily (or
anyway connected to the network via VPN)
Considering they have an installation of SBS 2003 (which I believe comes
with MS SQL 2000) what (as an example) would your answer to be to the
question.
Thanks for any input which will just help me to see if I am thinking along
the right lines compared to others with experience in this field (i.e. you
guys ;) )
Dave|||David Smithz wrote:
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uw2Z6bpiGHA.4912@.TK2MSFTNGP03.phx.gbl...
> OK thanks for that.
> As an example, if someone came to you and said,
> "I need to develop a database for my business. It will have about 10 users
> (but 5 most of the time) and it must work quickly. I heard Java is good bu
t
> you know better then me".
> Other details I know are although web access would be useful at times,
> generally the database needs to run on the internal network primarily (or
> anyway connected to the network via VPN)
> Considering they have an installation of SBS 2003 (which I believe comes
> with MS SQL 2000) what (as an example) would your answer to be to the
> question.
> Thanks for any input which will just help me to see if I am thinking along
> the right lines compared to others with experience in this field (i.e. you
> guys ;) )
> Dave
>
Hi Dave
I'm not a programmer, but I think your question is better asked in a
programmer/developer forum. As already mentioned, SQL server just holds
the data and doesn't really care much about which program gets the data.
SQL server will just handle the request that comes from your application
and then do what's being requested. If these requests comes from a
program written in .net, C## or whatever doesn't really matter.
Regards
Steen Schlter Persson
DBA