Help with Count

Hi all!

I have a table that has Order Number and an Account Number, I want to count all the account numbers. However there might be more then one order number with the same account number...

See example:

Table:

[Order Number] [Account Number]
12312 1234
13231 2342
14352 1311
23423 1313
11422 1234

Output should be: 4

I want to count([account number]) and get 4, notice there are 5 rows, the 1st and last have the same account number.

How do I write this query?

Thanks,

KenFound the anwser I was looking for!

select count(distinct [account number]) from table|||select count(distinct [Account Number])
from tbl|||Good timing! I found the answer just as you posted!

Thanks for the reply!

Ken

Help with case when

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",

p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.

What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.

I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"

Any ideas on how to write this?

Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
p.code as "Code",
p.detail_type as "Detail Type",
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>From Appointments a, Patientmedicalrecords p

Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
and a.Personal = 0
and a.Ingroup = 0
and a.Appttype not like 'TELE'
and a.Apptdate between '1-Jul-2006' and sysdate - 1
and a.Appttype not in ('AEP2','AEP4','AOD','TOCE2','TOCE4','ETOH2
Class','AEP4 Class','AOD1 Class')
and (substr(a.Complaint1,1,2) = 'TS'
or a.Complaint1 like 'Secretary Signed'
or a.Complaint1 is null
or a.Signoffinits is null)
and a.Patientnumber not in ('57629','82362','125163','139842')
Order by
a.Provider,
a.Apptdate

Thanks for your help. dwerden.dwerden (dwerden@.purdue.edu) writes:

Quote:

Originally Posted by

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",
>
p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.
>
>
What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.
>
I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"
>
Any ideas on how to write this?

CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Erland. Your code separated them like I wanted but I continue
to get 2 entries for each actual appointment (as shown below).

Provider--Session Date--Session Type--Code--Proceure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb

Is there a way to force these to combine into only one entry like this?

Provider--Session Date--Session Type--Code--Procedure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb

Erland Sommarskog wrote:

Quote:

Originally Posted by

CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>From Appointments a, Patientmedicalrecords p

Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
and a.Personal = 0
and a.Ingroup = 0
and a.Appttype not like 'TELE'
and a.Apptdate between '1-Jul-2006' and sysdate - 1
and a.Appttype not in ('AEP2','AEP4','AOD','TOCE2','TOCE4','ETOH2
Class','AEP4 Class','AOD1 Class')
and (substr(a.Complaint1,1,2) = 'TS'
or a.Complaint1 like 'Secretary Signed'
or a.Complaint1 is null
or a.Signoffinits is null)
/* Next line excludes Pyle, Kyler, Aunt Bee, Rowdy */
and a.Patientnumber not in ('57629','82362','125163','139842')
Order by
a.Provider,
a.Apptdate,
a.Lastname|||Assuming there is at most one row in Patientmedicalrecords with detail_type
= -2 and at most one with detail_type = -4 for each matching row in
Appointments, then
Change:
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

To:
Coalesce(p1.Code,'') As Code,
Coalesce(p2.Code,'') As Procedure,

And change:
From Appointments a, Patientmedicalrecords p
Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
<rest of where conditions>

to:

From Appointments a
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p1.appt_uniquenumber
And p1.detail_type = -2
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p12.appt_uniquenumber
And p1.detail_type = -4
Where a.Division = 3
and a.Inactive = 0
<rest of where conditions>

Tom

"dwerden" <dwerden@.purdue.eduwrote in message
news:1154457844.644843.182100@.m73g2000cwd.googlegr oups.com...

Quote:

Originally Posted by

Thanks, Erland. Your code separated them like I wanted but I continue
to get 2 entries for each actual appointment (as shown below).
>
Provider--Session Date--Session Type--Code--Proceure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb
>
Is there a way to force these to combine into only one entry like this?
>
Provider--Session Date--Session Type--Code--Procedure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

> CASE p.detail_type WHEN -2 THEN p.code END AS Code,
> CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
Select
/* Individual Client Task List */
>
a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>>From Appointments a, Patientmedicalrecords p

Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
and a.Personal = 0
and a.Ingroup = 0
and a.Appttype not like 'TELE'
and a.Apptdate between '1-Jul-2006' and sysdate - 1
and a.Appttype not in ('AEP2','AEP4','AOD','TOCE2','TOCE4','ETOH2
Class','AEP4 Class','AOD1 Class')
and (substr(a.Complaint1,1,2) = 'TS'
or a.Complaint1 like 'Secretary Signed'
or a.Complaint1 is null
or a.Signoffinits is null)
/* Next line excludes Pyle, Kyler, Aunt Bee, Rowdy */
and a.Patientnumber not in ('57629','82362','125163','139842')
Order by
a.Provider,
a.Apptdate,
a.Lastname
>

|||>p.code shows diagnostic code numbers and billing procedure numbers while p.detail_type indicate which is which. <<

Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?

What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.|||That's a great idea!

I think I remember seeing a squid looking automobile thingy in some animated
film recently.

Joe, Did you create the database used by the film company?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1154462822.641976.57700@.m73g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>p.code shows diagnostic code numbers and billing procedure numbers while
>>p.detail_type indicate which is which. <<

>
Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?
>
What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.
>

Help to deleting n numbers of records

Hi
One of our users have by mistake created a number of dublicate records in
the database. Now I need to delete the "faulty" records from the table and
just keep one of them. The records are linked to a customer table, so I have
a Recordid column in the table that I can use to group on. E.g. if I in the
table have 10 records with RecordID 1, I need to delete 9 of them and keep
1, 8 records with RecordID 2 I need to delete 7 and keep 1 etc.
I'll have to search the customer table to find the recordID's that is used
to link to the child table, so my plan is to use a cursor to find these and
put them into a variable. I'll then use this to find the record(s) in the
child table, get the number of records and then either delete them one by
one until I've only 1 pr. RecordID left or use SELECT Top x based on the
number of records found with each recordid and then delete all but 1 record.
It's not a huge number of records I need to delete so from a practical
and/or performance point of wiev it's not critical how I do it. It's more
that I'm currious to hear if my approach is the best one or if any of you
have any other ideas?
TIA
Regards
SteenTo answer this properly we'll need to know the keys and constraints in
your tables. Please post DDL (CREATE TABLE) and some sample data
(INSERTs) if you want help with the actual code.
You missed out one critical step from your solution: Add a new unique
constraint so that this can't happen again.
David Portas
SQL Server MVP
--|||Hi
First of all, it's a vendor application, so I can't change anything in the
database or application. I this case it's not a problem though, since it's
ok to create several records as they did, but in this case it's just because
they had some problems with a printer and therefore thay ran a wizard
several times which generated a number of records that where baiscally same.
It's these records thay now want to get deleted.
The 2 tables that's involved is called Lejer and Note.
CREATE TABLE [Lejer] (
[EjendomNr] [EjendomNr] NOT NULL ,
[LejemaalNr] [LejemaalNr] NOT NULL ,
[LejerNr] [LejerNr] NOT NULL ,
[LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
....and about 200 more column definitions
CREATE TABLE [Note] (
[NoteID] [RecordID] NOT NULL ,
[Tabelnavn] [TabelNavn] NOT NULL ,
[RecordID] [RecordID] NOT NULL ,
[Dato] [Dato] NOT NULL ,
[NoteType] [KodeId] NOT NULL ,
....and some more column definitions.
The fields that links the tables are Lejer.LejerID and Note.RecordID.
Below is ans example of sample data
Lejer:
Ejendomnr Lejemaalnr Lejernr LejerID
1 1 1 1000
1 2 2 1001
1 3 3 1002
2 1 1 1003
2 2 2 1004
3 1 1 1005
3 2 2 1006
Note
NoteID RecordID NoteType
1 1000 29000
2 1000 29000
3 1000 29000
4 1000 29000
5 1001 29000
6 1001 29000
7 1001 29000
8 1002 29000
9 1002 29000
10 1002 29000
11 1003 29000
12 1003 29000
I'd like to find the notes where the type is e.g. 29000 and are linked a
record in the Lejer table with the Ejendomnr of e.g. 1.
and then delete all notes but 1.
In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note records with NoteID 1 to 10. Out of these I'd like to delete 3 of the
ones with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with
RecordID 1002.
As mentioned in my original post, I could get all the RecordID's into a
cursor and then e.g. count the number of occurences for each of them and
then do a delete n-1 times. I just don't know if that's the smartest way to
do it or if there's a better approach?
Regards
Steen
David Portas wrote:
> To answer this properly we'll need to know the keys and constraints in
> your tables. Please post DDL (CREATE TABLE) and some sample data
> (INSERTs) if you want help with the actual code.
> You missed out one critical step from your solution: Add a new unique
> constraint so that this can't happen again.
> --
> David Portas
> SQL Server MVP|||How about
Delete Note
Where NoteType = '29000'
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Here I'm assuming that a given Note.RecordId must exist in Lejer.LejerId.
However, it is possible that this is not the case and you want to ensure tha
t
the record does exist in the Lejer table then you could add an Exists clause
like so:
Delete Note
Where NoteType = '29000'
And Exists(
Select *
From Lejer As L1
Where L1.LegerId = Note.RecordId
)
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Obviously, you should execute this code carefully to ensure that it is produ
ce
the results you want before you commit against production data.
HTH
Thomas
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi
> First of all, it's a vendor application, so I can't change anything in the
> database or application. I this case it's not a problem though, since it's
ok
> to create several records as they did, but in this case it's just because
they
> had some problems with a printer and therefore thay ran a wizard several t
imes
> which generated a number of records that where baiscally same. It's these
> records thay now want to get deleted.
> The 2 tables that's involved is called Lejer and Note.
>
> CREATE TABLE [Lejer] (
> [EjendomNr] [EjendomNr] NOT NULL ,
> [LejemaalNr] [LejemaalNr] NOT NULL ,
> [LejerNr] [LejerNr] NOT NULL ,
> [LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> .....and about 200 more column definitions
>
> CREATE TABLE [Note] (
> [NoteID] [RecordID] NOT NULL ,
> [Tabelnavn] [TabelNavn] NOT NULL ,
> [RecordID] [RecordID] NOT NULL ,
> [Dato] [Dato] NOT NULL ,
> [NoteType] [KodeId] NOT NULL ,
> ....and some more column definitions.
> The fields that links the tables are Lejer.LejerID and Note.RecordID.
>
> Below is ans example of sample data
> Lejer:
> Ejendomnr Lejemaalnr Lejernr LejerID
> 1 1 1 1000
> 1 2 2 1001
> 1 3 3 1002
> 2 1 1 1003
> 2 2 2 1004
> 3 1 1 1005
> 3 2 2 1006
> Note
> NoteID RecordID NoteType
> 1 1000 29000
> 2 1000 29000
> 3 1000 29000
> 4 1000 29000
> 5 1001 29000
> 6 1001 29000
> 7 1001 29000
> 8 1002 29000
> 9 1002 29000
> 10 1002 29000
> 11 1003 29000
> 12 1003 29000
>
> I'd like to find the notes where the type is e.g. 29000 and are linked a
> record in the Lejer table with the Ejendomnr of e.g. 1.
> and then delete all notes but 1.
> In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note
> records with NoteID 1 to 10. Out of these I'd like to delete 3 of the ones
> with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with Record
ID
> 1002.
> As mentioned in my original post, I could get all the RecordID's into a cu
rsor
> and then e.g. count the number of occurences for each of them and then do
a
> delete n-1 times. I just don't know if that's the smartest way to do it or
if
> there's a better approach?
> Regards
> Steen
>
> David Portas wrote:
>|||Hi Thomas
Thanks for you input. That was another way of doing it than I had in my
mind. I'll try it out in my test db.
Regards
Steen
Thomas Coleman wrote:
> How about
> Delete Note
> Where NoteType = '29000'
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Here I'm assuming that a given Note.RecordId must exist in
> Lejer.LejerId. However, it is possible that this is not the case and
> you want to ensure that the record does exist in the Lejer table then
> you could add an Exists clause like so:
> Delete Note
> Where NoteType = '29000'
> And Exists(
> Select *
> From Lejer As L1
> Where L1.LegerId = Note.RecordId
> )
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Obviously, you should execute this code carefully to ensure that it
> is produce the results you want before you commit against production
> data.
> HTH
>
> Thomas
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...