This is probably so easy it will be stupid but I cannot get anything to
work.
I need to find, from one table, all duplicate names. I pull the entire list
like this:
SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
FROM Attendants
What I need from this is a list where the names of the clients appear more
than once. I know there has to be an easy way to do this. Any quick help'SELECT First_Name,last_Name, count(*)
FROM Attendants
group by first_name, last_name
having count(*) > 1
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||SELECT ID, RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name) , Date_of_Birth
FROM Attendants AS a
WHERE EXISTS (SELECT RTRIM(b.First_Name) + ' ' + RTRIM(b.Last_Name)
FROM Attendants AS b
WHERE a.First_Name = b.First_Name and
a.Last_Name = b.Last_Name
GROUP BY RTRIM(b.First_Name) + ' ' +
RTRIM(b.Last_Name)
HAVING COUNT(*) > 1 )
ORDER BY RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name)
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||Thanks Geoff
Works GREAT!
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:u0lwu%237%23HHA.1164@.TK2MSFTNGP02.phx.gbl...
> SELECT First_Name,last_Name, count(*)
> FROM Attendants
> group by first_name, last_name
> having count(*) > 1
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
>> This is probably so easy it will be stupid but I cannot get anything to
>> work.
>> I need to find, from one table, all duplicate names. I pull the entire
>> list like this:
>> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
>> FROM Attendants
>> What I need from this is a list where the names of the clients appear
>> more than once. I know there has to be an easy way to do this. Any quick
>> help'
>
Showing posts with label entire. Show all posts
Showing posts with label entire. Show all posts
Wednesday, March 28, 2012
Finding due date from a six character long date field
Hi All,
Here is my problem.
I have a datefield as varchar and six characters long.
The date is shown as 200102 which means the entire month of February
in 2001.
I need to find the due date based on this adding 30 days and the date
will be the last date of the month.
For ex.
if the bill date is 200102 the due date will be 03/30/2001
if the bill date is 200112 the due date will be 01/31/2002
and so on.
Could anyone please tell me how can I write the query?
I have given the DDLs below:
CREATE TABLE [dbo].[Table1] (
[ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
)
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200101')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200102')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200103')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200112')
I was trying to run the following query:
SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
INVOICENO='500004'
of course, that did not give me the right answer.
I do highly appreciate your help.
Thanks a million in advance.
Best regards,
mamunHello Magmun !
Try this:
--German Version with dot seperated
Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
left(SUPLBILLDT,4)))) from Table1
--English Version with commy separated
Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
left(SUPLBILLDT,4)))) from Table1
HTH, Jens Süßmeyer.|||How about
SELECT due_date = DATEADD
(
MONTH,
2,
CONVERT(SMALLDATETIME, bill_date + '01', 112)
)-1
FROM table
(I assume you meant 03/31/2001, since 03/30 is not the last day of March.
And also, you're not adding 30 days to 20011201 and then setting the due
date to be the last day of that month, because 20011201 + 30 = 20011231 and
the last day of that month is 12/31, not 20020131. In other words, the
description of your requirements does not match your sample output... so if
the above query doesn't do what you want, you'll need to clarify;
preferably, with actual table structure, sample data (in the form of a
functional insert statement), and desired results.)
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0308220710.1ed91260@.posting.google.com...
> Hi All,
> Here is my problem.
> I have a datefield as varchar and six characters long.
> The date is shown as 200102 which means the entire month of February
> in 2001.
> I need to find the due date based on this adding 30 days and the date
> will be the last date of the month.
> For ex.
> if the bill date is 200102 the due date will be 03/30/2001
> if the bill date is 200112 the due date will be 01/31/2002
> and so on.
> Could anyone please tell me how can I write the query?
>
> I have given the DDLs below:
>
>
> CREATE TABLE [dbo].[Table1] (
> [ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> )
>
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200101')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200102')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200103')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200112')
> I was trying to run the following query:
> SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
> INVOICENO='500004'
> of course, that did not give me the right answer.
> I do highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||Why not return as a datetime, then you can wrap whatever presentation you
want around it using CONVERT and style. In your method, btw, you are
assuming that all of your string concatenations will not break datetime; I'm
not sure this is a healthy assumption, since you may not want to write code
that relies on a specific language / dateformat setting, and the machine's
regional settings.
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:eM$OtKMaDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello Magmun !
> Try this:
>
> --German Version with dot seperated
> Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
> left(SUPLBILLDT,4)))) from Table1
> --English Version with commy separated
> Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
> left(SUPLBILLDT,4)))) from Table1
>
> HTH, Jens Süßmeyer.
>|||OK, convinced :)). Didn´t thought about this solution, even we use it
(Yours, just implemented by ME) to keep track of our guarantee expiry dates
X-)).
Jens Süßmeyer.sql
Here is my problem.
I have a datefield as varchar and six characters long.
The date is shown as 200102 which means the entire month of February
in 2001.
I need to find the due date based on this adding 30 days and the date
will be the last date of the month.
For ex.
if the bill date is 200102 the due date will be 03/30/2001
if the bill date is 200112 the due date will be 01/31/2002
and so on.
Could anyone please tell me how can I write the query?
I have given the DDLs below:
CREATE TABLE [dbo].[Table1] (
[ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
)
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200101')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200102')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200103')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200112')
I was trying to run the following query:
SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
INVOICENO='500004'
of course, that did not give me the right answer.
I do highly appreciate your help.
Thanks a million in advance.
Best regards,
mamunHello Magmun !
Try this:
--German Version with dot seperated
Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
left(SUPLBILLDT,4)))) from Table1
--English Version with commy separated
Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
left(SUPLBILLDT,4)))) from Table1
HTH, Jens Süßmeyer.|||How about
SELECT due_date = DATEADD
(
MONTH,
2,
CONVERT(SMALLDATETIME, bill_date + '01', 112)
)-1
FROM table
(I assume you meant 03/31/2001, since 03/30 is not the last day of March.
And also, you're not adding 30 days to 20011201 and then setting the due
date to be the last day of that month, because 20011201 + 30 = 20011231 and
the last day of that month is 12/31, not 20020131. In other words, the
description of your requirements does not match your sample output... so if
the above query doesn't do what you want, you'll need to clarify;
preferably, with actual table structure, sample data (in the form of a
functional insert statement), and desired results.)
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0308220710.1ed91260@.posting.google.com...
> Hi All,
> Here is my problem.
> I have a datefield as varchar and six characters long.
> The date is shown as 200102 which means the entire month of February
> in 2001.
> I need to find the due date based on this adding 30 days and the date
> will be the last date of the month.
> For ex.
> if the bill date is 200102 the due date will be 03/30/2001
> if the bill date is 200112 the due date will be 01/31/2002
> and so on.
> Could anyone please tell me how can I write the query?
>
> I have given the DDLs below:
>
>
> CREATE TABLE [dbo].[Table1] (
> [ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> )
>
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200101')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200102')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200103')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200112')
> I was trying to run the following query:
> SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
> INVOICENO='500004'
> of course, that did not give me the right answer.
> I do highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||Why not return as a datetime, then you can wrap whatever presentation you
want around it using CONVERT and style. In your method, btw, you are
assuming that all of your string concatenations will not break datetime; I'm
not sure this is a healthy assumption, since you may not want to write code
that relies on a specific language / dateformat setting, and the machine's
regional settings.
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:eM$OtKMaDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello Magmun !
> Try this:
>
> --German Version with dot seperated
> Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
> left(SUPLBILLDT,4)))) from Table1
> --English Version with commy separated
> Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
> left(SUPLBILLDT,4)))) from Table1
>
> HTH, Jens Süßmeyer.
>|||OK, convinced :)). Didn´t thought about this solution, even we use it
(Yours, just implemented by ME) to keep track of our guarantee expiry dates
X-)).
Jens Süßmeyer.sql
Finding data
Is it possible to search an entire database for a string or number? If so
How?
- Hamilton
Here is one option: sp_grep
http://examples.oreilly.com/wintrnssql/readme.txt
--=20
Keith
"Hamilton" <hamilton@.polese.com> wrote in message =
news:%23mu9fm3FEHA.3252@.TK2MSFTNGP11.phx.gbl...
> Is it possible to search an entire database for a string or number? If =
so
> How?
>=20
> - Hamilton
>=20
>
How?
- Hamilton
Here is one option: sp_grep
http://examples.oreilly.com/wintrnssql/readme.txt
--=20
Keith
"Hamilton" <hamilton@.polese.com> wrote in message =
news:%23mu9fm3FEHA.3252@.TK2MSFTNGP11.phx.gbl...
> Is it possible to search an entire database for a string or number? If =
so
> How?
>=20
> - Hamilton
>=20
>
Friday, February 24, 2012
Find a word in entire database
SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canada
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canada
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
Find a word in entire database
SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canadahttp://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canadahttp://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
Subscribe to:
Posts (Atom)