Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:
> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Tuesday, March 27, 2012
DISTINCT Values
I am trying to run a query to one of two delete duplicates records. The
process I normally use is use
1) SELECT DISTINCT from the table into a second table
2) Delete all duplicate values in original table
3) Copy the disctinct values from the second table back into the original
table
Unfortunately, this time, my table has ntext fields in it. SELECT DISTINCT
does not work with ntext fields.
Does anyone have an alternative solution?
Thank you,
JLFlemingHave you thought about declaring a PRIMARY KEY?|||I have thought about it. I cannot declare a primary key if there are alread
y
duplicates in the table. Once I get rid of duplicates, I can put a primary
key in.
"--CELKO--" wrote:
> Have you thought about declaring a PRIMARY KEY?
>|||http://www.aspfaq.com/2431
http://www.aspfaq.com/2509
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:FAF44802-6129-435D-B7AE-EBBC0C042ECD@.microsoft.com...
> I am trying to run a query to one of two delete duplicates records. The
> process I normally use is use
> 1) SELECT DISTINCT from the table into a second table
> 2) Delete all duplicate values in original table
> 3) Copy the disctinct values from the second table back into the original
> table
> Unfortunately, this time, my table has ntext fields in it. SELECT
> DISTINCT
> does not work with ntext fields.
> Does anyone have an alternative solution?
> Thank you,
> JLFleming
process I normally use is use
1) SELECT DISTINCT from the table into a second table
2) Delete all duplicate values in original table
3) Copy the disctinct values from the second table back into the original
table
Unfortunately, this time, my table has ntext fields in it. SELECT DISTINCT
does not work with ntext fields.
Does anyone have an alternative solution?
Thank you,
JLFlemingHave you thought about declaring a PRIMARY KEY?|||I have thought about it. I cannot declare a primary key if there are alread
y
duplicates in the table. Once I get rid of duplicates, I can put a primary
key in.
"--CELKO--" wrote:
> Have you thought about declaring a PRIMARY KEY?
>|||http://www.aspfaq.com/2431
http://www.aspfaq.com/2509
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:FAF44802-6129-435D-B7AE-EBBC0C042ECD@.microsoft.com...
> I am trying to run a query to one of two delete duplicates records. The
> process I normally use is use
> 1) SELECT DISTINCT from the table into a second table
> 2) Delete all duplicate values in original table
> 3) Copy the disctinct values from the second table back into the original
> table
> Unfortunately, this time, my table has ntext fields in it. SELECT
> DISTINCT
> does not work with ntext fields.
> Does anyone have an alternative solution?
> Thank you,
> JLFleming
Sunday, March 25, 2012
Distinct Records
Hi All,
i want to Write the query which will give me the distinct top 1 records.
see i have my table and data in that table is in this way.
ReqNo Name Other
1 chirag
1 xyz
1 ABC
2 ZZZ
2 YYY
2 QQQ
3 NNN
i want the output as following
1 Chirag
2 ZZZ
3 NNN
the first records of each and every Reqno. i am trying this from a long time
but not able to get the same if you guys has any solution then please help m
e
out.Try this
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from MyTest group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hi
It should be
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from TableName
group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hello Chirag
You can do it as Madhivanan said if you have not created any views based on
this table. If you have any views based on this table then alterting the
table may affect the views. Views become invalid. In that case you can do
like this.
Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
Insert into #Temp Select * From TableName
Select * from #Temp where Slno in (Select min(Slno) from #Temp
group by ReqNo)
#Temp is a temporary table. So it will be deleted automatically.
Thank you
Baiju
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
> Hi
> It should be
> Alter table TableName add sno int identity
> go
> Select * from TableName where sno in (Select min(sno) from TableName
> group
> by ReqNo)
> go
> Alter table TableName drop column sno
>
> Madhivanan
>|||I would suggest you to create a running number and have it permantently.
creating a temp table, inserting data and doing a select on that, deleting
temp table..will be a workable solution..
but if your table has large number of records, this query takes its own
time..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Baiju" <baiju@.indus-systems.com> wrote in message
news:O#r6yd8HFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello Chirag
> You can do it as Madhivanan said if you have not created any views based
on
> this table. If you have any views based on this table then alterting the
> table may affect the views. Views become invalid. In that case you can do
> like this.
> Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
> Insert into #Temp Select * From TableName
> Select * from #Temp where Slno in (Select min(Slno) from #Temp
> group by ReqNo)
> #Temp is a temporary table. So it will be deleted automatically.
> Thank you
> Baiju
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
>|||try
select ReqNo, Max(Name) from ... group by ReqNo
"Chirag" wrote:
> Hi All,
> i want to Write the query which will give me the distinct top 1 records.
> see i have my table and data in that table is in this way.
> ReqNo Name Other
> 1 chirag
> 1 xyz
> 1 ABC
> 2 ZZZ
> 2 YYY
> 2 QQQ
> 3 NNN
> i want the output as following
> 1 Chirag
> 2 ZZZ
> 3 NNN
>
> the first records of each and every Reqno. i am trying this from a long ti
me
> but not able to get the same if you guys has any solution then please help
me
> out.|||If those are the only columns then there's no such thing as the "first"
for each ReqNo. You haven't identified "first" in your table and tables
have no fixed concept of order. The best you can do is probably with an
aggregate function:
SELECT reqno, MIN(name)
FROM YourTable
GROUP BY reqno
David Portas
SQL Server MVP
--
i want to Write the query which will give me the distinct top 1 records.
see i have my table and data in that table is in this way.
ReqNo Name Other
1 chirag
1 xyz
1 ABC
2 ZZZ
2 YYY
2 QQQ
3 NNN
i want the output as following
1 Chirag
2 ZZZ
3 NNN
the first records of each and every Reqno. i am trying this from a long time
but not able to get the same if you guys has any solution then please help m
e
out.Try this
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from MyTest group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hi
It should be
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from TableName
group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hello Chirag
You can do it as Madhivanan said if you have not created any views based on
this table. If you have any views based on this table then alterting the
table may affect the views. Views become invalid. In that case you can do
like this.
Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
Insert into #Temp Select * From TableName
Select * from #Temp where Slno in (Select min(Slno) from #Temp
group by ReqNo)
#Temp is a temporary table. So it will be deleted automatically.
Thank you
Baiju
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
> Hi
> It should be
> Alter table TableName add sno int identity
> go
> Select * from TableName where sno in (Select min(sno) from TableName
> group
> by ReqNo)
> go
> Alter table TableName drop column sno
>
> Madhivanan
>|||I would suggest you to create a running number and have it permantently.
creating a temp table, inserting data and doing a select on that, deleting
temp table..will be a workable solution..
but if your table has large number of records, this query takes its own
time..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Baiju" <baiju@.indus-systems.com> wrote in message
news:O#r6yd8HFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello Chirag
> You can do it as Madhivanan said if you have not created any views based
on
> this table. If you have any views based on this table then alterting the
> table may affect the views. Views become invalid. In that case you can do
> like this.
> Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
> Insert into #Temp Select * From TableName
> Select * from #Temp where Slno in (Select min(Slno) from #Temp
> group by ReqNo)
> #Temp is a temporary table. So it will be deleted automatically.
> Thank you
> Baiju
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
>|||try
select ReqNo, Max(Name) from ... group by ReqNo
"Chirag" wrote:
> Hi All,
> i want to Write the query which will give me the distinct top 1 records.
> see i have my table and data in that table is in this way.
> ReqNo Name Other
> 1 chirag
> 1 xyz
> 1 ABC
> 2 ZZZ
> 2 YYY
> 2 QQQ
> 3 NNN
> i want the output as following
> 1 Chirag
> 2 ZZZ
> 3 NNN
>
> the first records of each and every Reqno. i am trying this from a long ti
me
> but not able to get the same if you guys has any solution then please help
me
> out.|||If those are the only columns then there's no such thing as the "first"
for each ReqNo. You haven't identified "first" in your table and tables
have no fixed concept of order. The best you can do is probably with an
aggregate function:
SELECT reqno, MIN(name)
FROM YourTable
GROUP BY reqno
David Portas
SQL Server MVP
--
distinct query for table containing records more than 69347
What would be the best way to avoid distinct clause in query to get the result.My Query looks like
select distinct Col1 ,col2,col3,col4 from ABC where (1=1) group by Col1 ,col2,col3,col4 order by col1
ABC Contains records like
Col1 col2 col3 col4
a 1 1 1
a 2 1 1
B 2 2 2
B 1 1 1
;
;
;
how is the performance of disticnt query on Number of Rows ? HOw can i Improve my query performance if want to use distinct in my query? will adding indexes help to boost performance?
Thanks
You didn't tell us what is your desired outcome...
The WHERE clause can be removed.
The GROUP BY forces what you display.
Do you wish to collapse so that all 'a'/'B', etc is in one row?
If so, then something like:
Code Snippet
SELECT
Col1,
col2 = sum( Col2 ),
Col3 = sum( Col3 ),
Col4 = sum( Col4 )
FROM ABC
GROUP BY Col1
DISTINCT PROBLEM
i have two fields. One is id (otomatic number) and another is email..
there are some duplicate records in email records.
I want to use distinct query for email field to eliminate duplicate
records..I also want to get id fields of resultset of distinct email.
I thought that there are more than one id value for duplicate records. Can i
get max or min id valure of duplicate records or what algorithm sql server
does use ?Maybe something like...
SELECT email, MAX([ID]) AS [ID]
FROM table
GROUP BY email
ORDER BY email
HTH,
Ben
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
>i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
> i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>|||Untested...
Select A.ID, A.EMAIL from MYTABLE A
where exists (select 1 from MYTABLE B
where B.EMAIL = A.EMAIL
and B.ID < A.ID)
This should select all rows with duplicate EMAIL except for the EMAIL with
the lowest numbered ID.
You could also use:
select A.EMAIL, count(*) from MYTABLE A
group by A.EMAIL
having COUNT(*) > 1
Which will give you a list of all EMAILs occuring more than once, along with
how many times they occur. After you ge the data cleaned up you can add a
unique constraint which will prevetn duplicate emails from getting inserted
in the future.
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
> i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>sql
there are some duplicate records in email records.
I want to use distinct query for email field to eliminate duplicate
records..I also want to get id fields of resultset of distinct email.
I thought that there are more than one id value for duplicate records. Can i
get max or min id valure of duplicate records or what algorithm sql server
does use ?Maybe something like...
SELECT email, MAX([ID]) AS [ID]
FROM table
GROUP BY email
ORDER BY email
HTH,
Ben
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
>i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
> i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>|||Untested...
Select A.ID, A.EMAIL from MYTABLE A
where exists (select 1 from MYTABLE B
where B.EMAIL = A.EMAIL
and B.ID < A.ID)
This should select all rows with duplicate EMAIL except for the EMAIL with
the lowest numbered ID.
You could also use:
select A.EMAIL, count(*) from MYTABLE A
group by A.EMAIL
having COUNT(*) > 1
Which will give you a list of all EMAILs occuring more than once, along with
how many times they occur. After you ge the data cleaned up you can add a
unique constraint which will prevetn duplicate emails from getting inserted
in the future.
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
> i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>sql
DISTINCT OR GROUP BY
I have some records in my Sql Db.
There are some duplicate record in my email and adsoyad fields
Email Adsoyad
a@.a.com Savas
a@.a.com Pele
b@.b.com Savas
c@.c.om Ilim
d@.d.com Hasan
d@.d.com Hasan
I want to eliminate email address which can be duplicate and its Adsoyad
field.
There can be different adsoyad records for duplicate email records. I want
to select one of them which doesnt have any importance for me .
I also have soma email records which doesnt have @. character. I want to
eliminate those records too. Is there any command in SQL like INSTR ?Do you want to delete the row or update the fields?
For deleting the rows with email without '@.'
Delete from table where email not like '_%@._%'
I put the additional requirement that there must be at least one char in
front of and 1 char behind the @. sign...
The second thing you wanted to do is delete the dupe emails by picking the
Adsoyad which does not have meaning for you... You'll have to repst and
describe how you determine which Adsoyad doesn't have meaning and someone
will help you with the SQL...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Savas Ates" wrote:
> I have some records in my Sql Db.
> There are some duplicate record in my email and adsoyad fields
> Email Adsoyad
> a@.a.com Savas
> a@.a.com Pele
> b@.b.com Savas
> c@.c.om Ilim
> d@.d.com Hasan
> d@.d.com Hasan
> I want to eliminate email address which can be duplicate and its Adsoyad
> field.
> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
>
>|||> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
Use DISTINCT, then you can tell from your query that that is your purpose.
GROUP BY is typically used for aggregation.
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
Yes, look at CHARINDEX, PATINDEX. You might also consider a function or
even a check constraint that actually validates the format of an e-mail
address. Then you can't get any crap in there in the first place. Search
groups.google.com, there are plenty of examples out there ready to use.|||IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com>, haber iletisinde
unlar yazd:E9100F46-9958-4408-B527-2209F25B40B5@.microsoft.com...
> Do you want to delete the row or update the fields?
> For deleting the rows with email without '@.'
> Delete from table where email not like '_%@._%'
> I put the additional requirement that there must be at least one char in
> front of and 1 char behind the @. sign...
> The second thing you wanted to do is delete the dupe emails by picking the
> Adsoyad which does not have meaning for you... You'll have to repst and
> describe how you determine which Adsoyad doesn't have meaning and someone
> will help you with the SQL...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Savas Ates" wrote:
>|||> IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
> one of adsoyad records ?
Do you need adsoyad in the result? Can you provide more clear requirements
so we don't have to ask 80 follow-up questions? Please see
http://www.aspfaq.com/5006|||Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad
records whics is tied to one of the duplicate email addresses. I should say
that i dont need to chooese any adsoyad records exactly . Just wanna pick
up one of them .
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2366CMQ1TGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Do you need adsoyad in the result? Can you provide more clear
> requirements so we don't have to ask 80 follow-up questions? Please see
> http://www.aspfaq.com/5006
>|||(reposting using the Microsoft's site, since the Google Groups post was not
found here)
Hi, Savas
Try something like this (untested):
SELECT Email, MIN(Adsoyad)
FROM YourTable
WHERE Email LIKE '_%@._%._%' AND Email NOT LIKE '%@.%@.%'
GROUP BY Email
Note that the LIKE expression above (although more complex than what
you have suggested), still doesn't ensure a valid e-mail address (as
per RFC 822).
Razvan
There are some duplicate record in my email and adsoyad fields
Email Adsoyad
a@.a.com Savas
a@.a.com Pele
b@.b.com Savas
c@.c.om Ilim
d@.d.com Hasan
d@.d.com Hasan
I want to eliminate email address which can be duplicate and its Adsoyad
field.
There can be different adsoyad records for duplicate email records. I want
to select one of them which doesnt have any importance for me .
I also have soma email records which doesnt have @. character. I want to
eliminate those records too. Is there any command in SQL like INSTR ?Do you want to delete the row or update the fields?
For deleting the rows with email without '@.'
Delete from table where email not like '_%@._%'
I put the additional requirement that there must be at least one char in
front of and 1 char behind the @. sign...
The second thing you wanted to do is delete the dupe emails by picking the
Adsoyad which does not have meaning for you... You'll have to repst and
describe how you determine which Adsoyad doesn't have meaning and someone
will help you with the SQL...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Savas Ates" wrote:
> I have some records in my Sql Db.
> There are some duplicate record in my email and adsoyad fields
> Email Adsoyad
> a@.a.com Savas
> a@.a.com Pele
> b@.b.com Savas
> c@.c.om Ilim
> d@.d.com Hasan
> d@.d.com Hasan
> I want to eliminate email address which can be duplicate and its Adsoyad
> field.
> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
>
>|||> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
Use DISTINCT, then you can tell from your query that that is your purpose.
GROUP BY is typically used for aggregation.
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
Yes, look at CHARINDEX, PATINDEX. You might also consider a function or
even a check constraint that actually validates the format of an e-mail
address. Then you can't get any crap in there in the first place. Search
groups.google.com, there are plenty of examples out there ready to use.|||IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com>, haber iletisinde
unlar yazd:E9100F46-9958-4408-B527-2209F25B40B5@.microsoft.com...
> Do you want to delete the row or update the fields?
> For deleting the rows with email without '@.'
> Delete from table where email not like '_%@._%'
> I put the additional requirement that there must be at least one char in
> front of and 1 char behind the @. sign...
> The second thing you wanted to do is delete the dupe emails by picking the
> Adsoyad which does not have meaning for you... You'll have to repst and
> describe how you determine which Adsoyad doesn't have meaning and someone
> will help you with the SQL...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Savas Ates" wrote:
>|||> IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
> one of adsoyad records ?
Do you need adsoyad in the result? Can you provide more clear requirements
so we don't have to ask 80 follow-up questions? Please see
http://www.aspfaq.com/5006|||Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad
records whics is tied to one of the duplicate email addresses. I should say
that i dont need to chooese any adsoyad records exactly . Just wanna pick
up one of them .
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2366CMQ1TGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Do you need adsoyad in the result? Can you provide more clear
> requirements so we don't have to ask 80 follow-up questions? Please see
> http://www.aspfaq.com/5006
>|||(reposting using the Microsoft's site, since the Google Groups post was not
found here)
Hi, Savas
Try something like this (untested):
SELECT Email, MIN(Adsoyad)
FROM YourTable
WHERE Email LIKE '_%@._%._%' AND Email NOT LIKE '%@.%@.%'
GROUP BY Email
Note that the LIKE expression above (although more complex than what
you have suggested), still doesn't ensure a valid e-mail address (as
per RFC 822).
Razvan
Wednesday, March 21, 2012
distinct
How do i make this return only distinct records...
SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
dbo.tbl_purchase_orders.date_expected
FROM dbo.tbl_purchase_order_lines INNER JOIN
dbo.tbl_purchase_orders ON
dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id
WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
(dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
ORDER BY dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_orders.date_expected
help appreciated!!!!
chrisSELECT distinct
ol.stock_code,
ol.qty_ordered AS stock_ordered,
l.date_expected
FROM dbo.tbl_purchase_order_lines as ol INNER JOIN dbo.tbl_purchase_orders as l
ON ol.purchase_order_id =3D l.id
WHERE (l.delivery_complete =3D 0) AND
(l.date_expected > GETDATE() - 1)
ORDER BY stock_code, date_expected
ie just add the distinct, the top 100 percent looks effectively =redundant anyway, also corelation names (table aliases) make it easier =to read and less typing
Mike John
"Chris Dangerfield" <webmaster@.planetmicro.co.uk> wrote in message =news:p1iNa.219$CO4.17@.news-binary.blueyonder.co.uk...
> How do i make this return only distinct records...
> > > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
> dbo.tbl_purchase_orders.date_expected
> FROM dbo.tbl_purchase_order_lines INNER JOIN
> dbo.tbl_purchase_orders ON
> dbo.tbl_purchase_order_lines.purchase_order_id =3D =dbo.tbl_purchase_orders.id
> WHERE (dbo.tbl_purchase_orders.delivery_complete =3D 0) AND
> (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
> ORDER BY dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_orders.date_expected
> > > help appreciated!!!!
> chris
> >=20
SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
dbo.tbl_purchase_orders.date_expected
FROM dbo.tbl_purchase_order_lines INNER JOIN
dbo.tbl_purchase_orders ON
dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id
WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
(dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
ORDER BY dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_orders.date_expected
help appreciated!!!!
chrisSELECT distinct
ol.stock_code,
ol.qty_ordered AS stock_ordered,
l.date_expected
FROM dbo.tbl_purchase_order_lines as ol INNER JOIN dbo.tbl_purchase_orders as l
ON ol.purchase_order_id =3D l.id
WHERE (l.delivery_complete =3D 0) AND
(l.date_expected > GETDATE() - 1)
ORDER BY stock_code, date_expected
ie just add the distinct, the top 100 percent looks effectively =redundant anyway, also corelation names (table aliases) make it easier =to read and less typing
Mike John
"Chris Dangerfield" <webmaster@.planetmicro.co.uk> wrote in message =news:p1iNa.219$CO4.17@.news-binary.blueyonder.co.uk...
> How do i make this return only distinct records...
> > > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
> dbo.tbl_purchase_orders.date_expected
> FROM dbo.tbl_purchase_order_lines INNER JOIN
> dbo.tbl_purchase_orders ON
> dbo.tbl_purchase_order_lines.purchase_order_id =3D =dbo.tbl_purchase_orders.id
> WHERE (dbo.tbl_purchase_orders.delivery_complete =3D 0) AND
> (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
> ORDER BY dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_orders.date_expected
> > > help appreciated!!!!
> chris
> >=20
Monday, March 19, 2012
Displaying the Top N records count
Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj
The report looks something like this:
Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------
U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:
Total 10 15 25
I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.
Please give me the solution as soon as possibleOriginally posted by rajdotme
Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj
The report looks something like this:
Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------
U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:
Total 10 15 25
I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.
Please give me the solution as soon as possible
Hi,
Validate in Detail Format Section using with "RecordNumber" value is <= 5 (variable value).
yours friendly,
K.Babu|||Hi,
Thanks for the Reply. Could u please tell me the exact location where to change. I still couldnt find it.
Thanks and Regards,
Raj|||Hi rajdotme,
Use the formulae given below
Formula1 {@.Reset}
whileprintingrecords;
NumberVar x:=0;
NumberVar y:=0;
NumberVar z:=0;
Formula2 {@.Accepted}
whileprintingrecords;
Numbervar x:=x+{Table.Accepted}
Formula3 {@.Rejected}
whileprintingrecords;
Numbervar y:=y+{Table.Rejected}
Formula4 {@.Total}
whileprintingrecords;
Numbervar z:=z+{Table.Total}
Now, Place Formula1 in the group header and suppress it
Place other formulae in the group footer
Madhivanan
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj
The report looks something like this:
Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------
U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:
Total 10 15 25
I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.
Please give me the solution as soon as possibleOriginally posted by rajdotme
Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj
The report looks something like this:
Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------
U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:
Total 10 15 25
I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.
Please give me the solution as soon as possible
Hi,
Validate in Detail Format Section using with "RecordNumber" value is <= 5 (variable value).
yours friendly,
K.Babu|||Hi,
Thanks for the Reply. Could u please tell me the exact location where to change. I still couldnt find it.
Thanks and Regards,
Raj|||Hi rajdotme,
Use the formulae given below
Formula1 {@.Reset}
whileprintingrecords;
NumberVar x:=0;
NumberVar y:=0;
NumberVar z:=0;
Formula2 {@.Accepted}
whileprintingrecords;
Numbervar x:=x+{Table.Accepted}
Formula3 {@.Rejected}
whileprintingrecords;
Numbervar y:=y+{Table.Rejected}
Formula4 {@.Total}
whileprintingrecords;
Numbervar z:=z+{Table.Total}
Now, Place Formula1 in the group header and suppress it
Place other formulae in the group footer
Madhivanan
Sunday, March 11, 2012
displaying remaining records
Hello all. Quick rundown of what I need to do. I am pulling cartons(or
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets
displaying remaining records
Hello all. Quick rundown of what I need to do. I am pulling cartons(or
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.
I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets
|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.
I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets
|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets
displaying remaining records
Hello all. Quick rundown of what I need to do. I am pulling cartons(or
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets
displaying records in two tables
HI,
Need some help in displaying the records in two table( from a single
dataset).
To be precise i have a dataset which returns say 10 rows, i want to
display the odd numbered rows( i mean the alternate rows) in the first
table and then the even rows in the second table. I'm using RS 2K.
This is a do or die situation with RS reports, else i might have to all
my reports using crystal reports. Appreciate if some one can quickly
fill my hope.
Thanks in advance,
--VenkatSimple enough.
First create your initial table. Set the Visibility/Hidden property of
the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
will give you your odd numbered rows.
Copy that table and paste the copy into the report body. Edit the
Visibility/Hidden property of the copy's detail row to
=IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
your even numbered rows.
Make sure you set the property for the row and not individual cells or
you'll end up with weird white spaces showing up.
It's probably also possible to set up filters for the dataset that
would accomplish this but you would have to find an alternative to the
RowNumber() function as it can't be used in filters. If your datset
includes an incrementally numbered field that could work.
Best regards
toolman
venkat.oar@.gmail.com wrote:
> HI,
> Need some help in displaying the records in two table( from a single
> dataset).
> To be precise i have a dataset which returns say 10 rows, i want to
> display the odd numbered rows( i mean the alternate rows) in the first
> table and then the even rows in the second table. I'm using RS 2K.
> This is a do or die situation with RS reports, else i might have to all
> my reports using crystal reports. Appreciate if some one can quickly
> fill my hope.
> Thanks in advance,
> --Venkat|||Please note my editting below:
toolman wrote:
> Simple enough.
> First create your initial table. Set the Visibility/Hidden property of
> the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> will give you your odd numbered rows.
> Copy that table and paste the copy into the report body. Edit the
> Visibility/Hidden property of the copy's detail row to
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> your even numbered rows.
EDITTING: The above should be reversed.
=IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
mod 2 = 1, True,False) will HIDE the odd numbers.
Sorry for the mix-up.
> Make sure you set the property for the row and not individual cells or
> you'll end up with weird white spaces showing up.
> It's probably also possible to set up filters for the dataset that
> would accomplish this but you would have to find an alternative to the
> RowNumber() function as it can't be used in filters. If your datset
> includes an incrementally numbered field that could work.
> Best regards
> toolman
> venkat.oar@.gmail.com wrote:
> > HI,
> >
> > Need some help in displaying the records in two table( from a single
> > dataset).
> >
> > To be precise i have a dataset which returns say 10 rows, i want to
> > display the odd numbered rows( i mean the alternate rows) in the first
> > table and then the even rows in the second table. I'm using RS 2K.
> >
> > This is a do or die situation with RS reports, else i might have to all
> > my reports using crystal reports. Appreciate if some one can quickly
> > fill my hope.
> >
> > Thanks in advance,
> > --Venkat|||Thank you for your response, i will implement it to see if every thing
works fine, mean while can you tell me how to restrict the # of columns
displayed per table
Appreciate your help.
--Venkat
toolman wrote:
> Please note my editting below:
> toolman wrote:
> > Simple enough.
> > First create your initial table. Set the Visibility/Hidden property of
> > the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> > will give you your odd numbered rows.
> > Copy that table and paste the copy into the report body. Edit the
> > Visibility/Hidden property of the copy's detail row to
> > =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> > your even numbered rows.
> EDITTING: The above should be reversed.
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
> numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
> mod 2 = 1, True,False) will HIDE the odd numbers.
> Sorry for the mix-up.
> > Make sure you set the property for the row and not individual cells or
> > you'll end up with weird white spaces showing up.
> > It's probably also possible to set up filters for the dataset that
> > would accomplish this but you would have to find an alternative to the
> > RowNumber() function as it can't be used in filters. If your datset
> > includes an incrementally numbered field that could work.
> > Best regards
> > toolman
> >
> > venkat.oar@.gmail.com wrote:
> > > HI,
> > >
> > > Need some help in displaying the records in two table( from a single
> > > dataset).
> > >
> > > To be precise i have a dataset which returns say 10 rows, i want to
> > > display the odd numbered rows( i mean the alternate rows) in the first
> > > table and then the even rows in the second table. I'm using RS 2K.
> > >
> > > This is a do or die situation with RS reports, else i might have to all
> > > my reports using crystal reports. Appreciate if some one can quickly
> > > fill my hope.
> > >
> > > Thanks in advance,
> > > --Venkat
Need some help in displaying the records in two table( from a single
dataset).
To be precise i have a dataset which returns say 10 rows, i want to
display the odd numbered rows( i mean the alternate rows) in the first
table and then the even rows in the second table. I'm using RS 2K.
This is a do or die situation with RS reports, else i might have to all
my reports using crystal reports. Appreciate if some one can quickly
fill my hope.
Thanks in advance,
--VenkatSimple enough.
First create your initial table. Set the Visibility/Hidden property of
the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
will give you your odd numbered rows.
Copy that table and paste the copy into the report body. Edit the
Visibility/Hidden property of the copy's detail row to
=IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
your even numbered rows.
Make sure you set the property for the row and not individual cells or
you'll end up with weird white spaces showing up.
It's probably also possible to set up filters for the dataset that
would accomplish this but you would have to find an alternative to the
RowNumber() function as it can't be used in filters. If your datset
includes an incrementally numbered field that could work.
Best regards
toolman
venkat.oar@.gmail.com wrote:
> HI,
> Need some help in displaying the records in two table( from a single
> dataset).
> To be precise i have a dataset which returns say 10 rows, i want to
> display the odd numbered rows( i mean the alternate rows) in the first
> table and then the even rows in the second table. I'm using RS 2K.
> This is a do or die situation with RS reports, else i might have to all
> my reports using crystal reports. Appreciate if some one can quickly
> fill my hope.
> Thanks in advance,
> --Venkat|||Please note my editting below:
toolman wrote:
> Simple enough.
> First create your initial table. Set the Visibility/Hidden property of
> the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> will give you your odd numbered rows.
> Copy that table and paste the copy into the report body. Edit the
> Visibility/Hidden property of the copy's detail row to
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> your even numbered rows.
EDITTING: The above should be reversed.
=IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
mod 2 = 1, True,False) will HIDE the odd numbers.
Sorry for the mix-up.
> Make sure you set the property for the row and not individual cells or
> you'll end up with weird white spaces showing up.
> It's probably also possible to set up filters for the dataset that
> would accomplish this but you would have to find an alternative to the
> RowNumber() function as it can't be used in filters. If your datset
> includes an incrementally numbered field that could work.
> Best regards
> toolman
> venkat.oar@.gmail.com wrote:
> > HI,
> >
> > Need some help in displaying the records in two table( from a single
> > dataset).
> >
> > To be precise i have a dataset which returns say 10 rows, i want to
> > display the odd numbered rows( i mean the alternate rows) in the first
> > table and then the even rows in the second table. I'm using RS 2K.
> >
> > This is a do or die situation with RS reports, else i might have to all
> > my reports using crystal reports. Appreciate if some one can quickly
> > fill my hope.
> >
> > Thanks in advance,
> > --Venkat|||Thank you for your response, i will implement it to see if every thing
works fine, mean while can you tell me how to restrict the # of columns
displayed per table
Appreciate your help.
--Venkat
toolman wrote:
> Please note my editting below:
> toolman wrote:
> > Simple enough.
> > First create your initial table. Set the Visibility/Hidden property of
> > the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> > will give you your odd numbered rows.
> > Copy that table and paste the copy into the report body. Edit the
> > Visibility/Hidden property of the copy's detail row to
> > =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> > your even numbered rows.
> EDITTING: The above should be reversed.
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
> numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
> mod 2 = 1, True,False) will HIDE the odd numbers.
> Sorry for the mix-up.
> > Make sure you set the property for the row and not individual cells or
> > you'll end up with weird white spaces showing up.
> > It's probably also possible to set up filters for the dataset that
> > would accomplish this but you would have to find an alternative to the
> > RowNumber() function as it can't be used in filters. If your datset
> > includes an incrementally numbered field that could work.
> > Best regards
> > toolman
> >
> > venkat.oar@.gmail.com wrote:
> > > HI,
> > >
> > > Need some help in displaying the records in two table( from a single
> > > dataset).
> > >
> > > To be precise i have a dataset which returns say 10 rows, i want to
> > > display the odd numbered rows( i mean the alternate rows) in the first
> > > table and then the even rows in the second table. I'm using RS 2K.
> > >
> > > This is a do or die situation with RS reports, else i might have to all
> > > my reports using crystal reports. Appreciate if some one can quickly
> > > fill my hope.
> > >
> > > Thanks in advance,
> > > --Venkat
Displaying records in subreport
My report is grouped by the following fields.
Region
Sector
Interval
Area
Crew Unit
I have 8 different formulas that counts the records found under each and display them under each area by crew tech.
It looks something like this
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: ESU
crew/tech Equip Not Available Parts On Order Timely Complete etc.
AAA 5 0 2
------
Next page
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: COMM
crew/tech Equip Not Available Parts On Order Timely Complete etc.
BBB 0 0 3
CCC 5 0 1
----
Now, I have a subreport where I want to display the records found under
each column in detail but for both areas combined. So for "Equip Not Available" it needs to display 5+5 records under Area-ESU and COMM. Then on the next page it will display 2+3+1 records for "Timely Complete" for Area-ESU and COMM. What I did was in the subreport I created another formula {@.grpname} which had above formulas combined and grouped the subreport by that formula. That works fine.
But the problem is that - Out of the 8 formulas I want to display detail records for only 5 of them even if it finds records for the other 3 formulas.
This is how my {@.grpname} formula looks like -
If {pPMPERDET.COMPLETION_STATUS} = "X" AND {pPMPERDET.END_DATE_TIME} < {pPMPERDET.LATEST_DATE} then
"Equip Not Available" else
If {pPMPERDET.COMPLETION_STATUS}) = "O" AND
{pPMPERDET.INTERVAL_LENGTH = ''} then
"Timely Complete"
//etc for all the 5 groups
Right now it displays the records for the 5 groups but in addition to that it is also displaying the remaining records found under no heading. How should I stop it from displaying these records?
ThanksI'm not entirely sure what it is you're describing below, but it sounds like you want to look at suppressing either fields or entire subreports/sections.
To suppress a subreport : put it in it's own section, right click the section and go to Section Expert -> next to the Suppress option there is an x+2 button.
To suppress a field : right click it, go to Format Field (Common tab) and next to Suppress option is the x+2 box.
Suppression formula must return true or false. Use formula editor to get the result you're after, for example...
If IsNull( [your group name] ) then
true --yes, suppress the subreport/field
else
false --no, don't suppress.|||Got it. Works gr8! Thanks
Region
Sector
Interval
Area
Crew Unit
I have 8 different formulas that counts the records found under each and display them under each area by crew tech.
It looks something like this
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: ESU
crew/tech Equip Not Available Parts On Order Timely Complete etc.
AAA 5 0 2
------
Next page
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: COMM
crew/tech Equip Not Available Parts On Order Timely Complete etc.
BBB 0 0 3
CCC 5 0 1
----
Now, I have a subreport where I want to display the records found under
each column in detail but for both areas combined. So for "Equip Not Available" it needs to display 5+5 records under Area-ESU and COMM. Then on the next page it will display 2+3+1 records for "Timely Complete" for Area-ESU and COMM. What I did was in the subreport I created another formula {@.grpname} which had above formulas combined and grouped the subreport by that formula. That works fine.
But the problem is that - Out of the 8 formulas I want to display detail records for only 5 of them even if it finds records for the other 3 formulas.
This is how my {@.grpname} formula looks like -
If {pPMPERDET.COMPLETION_STATUS} = "X" AND {pPMPERDET.END_DATE_TIME} < {pPMPERDET.LATEST_DATE} then
"Equip Not Available" else
If {pPMPERDET.COMPLETION_STATUS}) = "O" AND
{pPMPERDET.INTERVAL_LENGTH = ''} then
"Timely Complete"
//etc for all the 5 groups
Right now it displays the records for the 5 groups but in addition to that it is also displaying the remaining records found under no heading. How should I stop it from displaying these records?
ThanksI'm not entirely sure what it is you're describing below, but it sounds like you want to look at suppressing either fields or entire subreports/sections.
To suppress a subreport : put it in it's own section, right click the section and go to Section Expert -> next to the Suppress option there is an x+2 button.
To suppress a field : right click it, go to Format Field (Common tab) and next to Suppress option is the x+2 box.
Suppression formula must return true or false. Use formula editor to get the result you're after, for example...
If IsNull( [your group name] ) then
true --yes, suppress the subreport/field
else
false --no, don't suppress.|||Got it. Works gr8! Thanks
Displaying records from today on (filtering out the old)
I am trying to filter out old records from a search. I tried this but
it still seems to display the old records.
gofind is the input box text query and it passes through fine. It
searches perfectly, just doesn't filter out old records.
It should show everything from today and into the future.
dDate = Date()-1
SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes' ORDER BY date ASC
Can someone help please?You need to enclose the two sections of your where clause in
parentheses. Try something like this:
SELECT * FROM events WHERE (title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes') OR (comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes') ORDER BY date ASC
As it was written simply a match with the comments LIKE '%" & gofind &
"%' would result in an evaluation of true.
james.shearer@.gmail.com wrote:
> I am trying to filter out old records from a search. I tried this but
> it still seems to display the old records.
> gofind is the input box text query and it passes through fine. It
> searches perfectly, just doesn't filter out old records.
> It should show everything from today and into the future.
> dDate = Date()-1
> SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
> & dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
> date >= " & dDate & " AND active='yes' ORDER BY date ASC
> Can someone help please?
>|||Thanks Steve but that doesn't seem to make any difference.
The query works fine apart from the part:
date >= " & dDate & "
That doesn't seem to make any difference at all.
It should show records dated from a date greater than yesterday but it
just shows everything that meets the rest of the criteria ignoring the
date request.
Any idea?|||On 8 Nov 2005 20:05:09 -0800, james.shearer@.gmail.com wrote:
>Thanks Steve but that doesn't seem to make any difference.
>The query works fine apart from the part:
>date >= " & dDate & "
>That doesn't seem to make any difference at all.
>It should show records dated from a date greater than yesterday but it
>just shows everything that meets the rest of the criteria ignoring the
>date request.
>Any idea?
Hi James,
I don't know what front end you are using. Best would be to pass the
date as a parameter instead of passing it as text. That would prevent
any possible conversion problems, since native date/time datatypes will
be used.
If you must pass it as text, then the best way to troubleshoot this is
to display the query string instead of executing it. The part with the
date comparison should look like this:
date >= '20051109'
or
date >= '2005-11-09T22:40:34.550'
That is: the date string must be enclosed in single quotes, and it must
adhere to one of the unambigous date formats, which are:
* yyyymmdd for date only
(note: no dashes, slashes, dots, or other punctuation)
* yyyy-mm-ddThh:mm:ss for date and time
(note: dashes in the date, colons in the time and an uppercase T [not
a space!] between the two parts)
* yyyy-mm-ddThh:mm:ss.mmm for date and time with millisecond precision
(note: same as above; add a dot and then the milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
it still seems to display the old records.
gofind is the input box text query and it passes through fine. It
searches perfectly, just doesn't filter out old records.
It should show everything from today and into the future.
dDate = Date()-1
SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes' ORDER BY date ASC
Can someone help please?You need to enclose the two sections of your where clause in
parentheses. Try something like this:
SELECT * FROM events WHERE (title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes') OR (comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes') ORDER BY date ASC
As it was written simply a match with the comments LIKE '%" & gofind &
"%' would result in an evaluation of true.
james.shearer@.gmail.com wrote:
> I am trying to filter out old records from a search. I tried this but
> it still seems to display the old records.
> gofind is the input box text query and it passes through fine. It
> searches perfectly, just doesn't filter out old records.
> It should show everything from today and into the future.
> dDate = Date()-1
> SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
> & dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
> date >= " & dDate & " AND active='yes' ORDER BY date ASC
> Can someone help please?
>|||Thanks Steve but that doesn't seem to make any difference.
The query works fine apart from the part:
date >= " & dDate & "
That doesn't seem to make any difference at all.
It should show records dated from a date greater than yesterday but it
just shows everything that meets the rest of the criteria ignoring the
date request.
Any idea?|||On 8 Nov 2005 20:05:09 -0800, james.shearer@.gmail.com wrote:
>Thanks Steve but that doesn't seem to make any difference.
>The query works fine apart from the part:
>date >= " & dDate & "
>That doesn't seem to make any difference at all.
>It should show records dated from a date greater than yesterday but it
>just shows everything that meets the rest of the criteria ignoring the
>date request.
>Any idea?
Hi James,
I don't know what front end you are using. Best would be to pass the
date as a parameter instead of passing it as text. That would prevent
any possible conversion problems, since native date/time datatypes will
be used.
If you must pass it as text, then the best way to troubleshoot this is
to display the query string instead of executing it. The part with the
date comparison should look like this:
date >= '20051109'
or
date >= '2005-11-09T22:40:34.550'
That is: the date string must be enclosed in single quotes, and it must
adhere to one of the unambigous date formats, which are:
* yyyymmdd for date only
(note: no dashes, slashes, dots, or other punctuation)
* yyyy-mm-ddThh:mm:ss for date and time
(note: dashes in the date, colons in the time and an uppercase T [not
a space!] between the two parts)
* yyyy-mm-ddThh:mm:ss.mmm for date and time with millisecond precision
(note: same as above; add a dot and then the milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Displaying multiple records from one record
Hi there,
I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.
Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?
User table
Userid Username
1 Bob
2 Luke
3 John
Store procedure result
Companyname Username
Acrm Corp Null
Maxwellarm Ltd Null
Regards,
Since my last post, I found that the CMS is storing individual ids.
Still it would be nice to know how others are doing this.
An addition to my post above, I believe if I wanted more than one firstname and surname to appear in the columns, that I would have to create a different stored procedure retrieving the unique id from the first stored procedure.
displaying more then one record from the same table in the same field
I was wondering if there was any way to make crystal reports grab multiple records from the same table and displahy them in one database field. Basicly I have a table that users would type info into each line of text that they type would equal one record on the table. When I run the report I need to display all the lines of text that they typed in. If I just refrence the table in the database field in crystal it will only pull the first record from the table and thats it.
Here is an example of the table
00000101 0001 This is a tes of the emergency broadcast system, if there was an actual emergency you would hear
00000101 0002 about it after the tones.This is a tes of the emergency broadcast system, if there was an actual
00000101 0003 emergency you would here about it after the tones.This is a tes of the emergency broadcast system
Now if I run the report the only thing it will dislay is the first line (record number "0001")
Thanks for any helpAdd a Textbox to your report. Then drag the first DB Field into the textbox, then drag the second DB Field into the same textbox.
I attached a picture of an Address field from one of my reports. It's a textbox and I dropped the 5 DB fields onto it (Name, Address, City, State, Zip)
Here is an example of the table
00000101 0001 This is a tes of the emergency broadcast system, if there was an actual emergency you would hear
00000101 0002 about it after the tones.This is a tes of the emergency broadcast system, if there was an actual
00000101 0003 emergency you would here about it after the tones.This is a tes of the emergency broadcast system
Now if I run the report the only thing it will dislay is the first line (record number "0001")
Thanks for any helpAdd a Textbox to your report. Then drag the first DB Field into the textbox, then drag the second DB Field into the same textbox.
I attached a picture of an Address field from one of my reports. It's a textbox and I dropped the 5 DB fields onto it (Name, Address, City, State, Zip)
Friday, March 9, 2012
Displaying max values of each group in SQL
Hello everyone;
I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.
There are 3 tables named CUST, ACCT, and TRAN:
CUST:
CNO NAME
CN101 DAN
CN102 AAA
ACCT:
ANO CNO
AN101 CN101
AN102 CN102
TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000
And the resultant table should look something like this:
ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000
Now, here's my code:
SELECT DISTINCT
B.NAME,
C.ANO,
MAX(A.TDATE)
FROM TRAN A,
CUST B,
ACCT C
WHERE B.CNO = C.CNO
AND C.ANO = A.ANO
GROUP BY B.CNO,
B.NAME,
C.ANO;
And the resultant table is:
NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006
The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:
ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000
I will really appreciate any help.
Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D
I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.
There are 3 tables named CUST, ACCT, and TRAN:
CUST:
CNO NAME
CN101 DAN
CN102 AAA
ACCT:
ANO CNO
AN101 CN101
AN102 CN102
TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000
And the resultant table should look something like this:
ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000
Now, here's my code:
SELECT DISTINCT
B.NAME,
C.ANO,
MAX(A.TDATE)
FROM TRAN A,
CUST B,
ACCT C
WHERE B.CNO = C.CNO
AND C.ANO = A.ANO
GROUP BY B.CNO,
B.NAME,
C.ANO;
And the resultant table is:
NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006
The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:
ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000
I will really appreciate any help.
Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D
Displaying huge heirarchial data in reports
I need to display huge data around 1 million records in report. Data needs to be represented as heirarchial. So there is somewhere around 6 level of grouping of data. Can somebody suggest a solution to get the data and display the data in a faster way.
Thanks in advance.
SSRS
Hi,
I don't knw if I understand your problem but you could use the document map functionnality for every level of grouping you have.
HTH,
Eric
|||Hi Eric,
Thanks for your reply. But, my actual problem is the huge data, fetching that data and displaying such huge data is taking lot of time. So I was looking for some solution where on demand if I can access the required data and display in the report. Like first I can get all the parents and once I click on any of the parent it fetches the child records for that parent and display it on the same report. Please help on the same. If you've still some doubts then do let me know. Because I do need some kind of solution for this problem
Thanks & Regards,
SSRS
Deepak
|||What about using sub report ?|||Subreports won't help much in this case, as they are processed at the same time as the main report.
You can try creating a master report that displays only aggreggate information per groups (the report query should use T-SQL group by, count and friends...)
Under each aggregate you can create a link (jump to report, aka drillthrough report) to a report containing the details for that particular group. And so on for all the grouping levels until you get to the details rows, which will hopefully be filtered out to a small enough number.
Hope that helps
Tudor Trufinescu
|||I am having similar problem with huge data. I have linked a detailed report using jump to report. When the data is less it is running fine. But when my parameter selection retrieves huge data in the main report the problem is coming. It seems like it is executing the linked report along with the main report. It is not waiting till the cell is clicked to link. Why is it happening that way? The reason that I feel the linked report is also being executed along with the main report is, for the same parameter selection if I don't link the detailed report it is working fine. Any advice in this regard is much appreciated.
Thank you.
Displaying huge heirarchial data in reports
I need to display huge data around 1 million records in report. Data needs to be represented as heirarchial. So there is somewhere around 6 level of grouping of data. Can somebody suggest a solution to get the data and display the data in a faster way.
Thanks in advance.
SSRS
Hi,
I don't knw if I understand your problem but you could use the document map functionnality for every level of grouping you have.
HTH,
Eric
|||Hi Eric,
Thanks for your reply. But, my actual problem is the huge data, fetching that data and displaying such huge data is taking lot of time. So I was looking for some solution where on demand if I can access the required data and display in the report. Like first I can get all the parents and once I click on any of the parent it fetches the child records for that parent and display it on the same report. Please help on the same. If you've still some doubts then do let me know. Because I do need some kind of solution for this problem
Thanks & Regards,
SSRS
Deepak
|||What about using sub report ?|||Subreports won't help much in this case, as they are processed at the same time as the main report.
You can try creating a master report that displays only aggreggate information per groups (the report query should use T-SQL group by, count and friends...)
Under each aggregate you can create a link (jump to report, aka drillthrough report) to a report containing the details for that particular group. And so on for all the grouping levels until you get to the details rows, which will hopefully be filtered out to a small enough number.
Hope that helps
Tudor Trufinescu
|||
I am having similar problem with huge data. I have linked a detailed report using jump to report. When the data is less it is running fine. But when my parameter selection retrieves huge data in the main report the problem is coming. It seems like it is executing the linked report along with the main report. It is not waiting till the cell is clicked to link. Why is it happening that way? The reason that I feel the linked report is also being executed along with the main report is, for the same parameter selection if I don't link the detailed report it is working fine. Any advice in this regard is much appreciated.
Thank you.
displaying found duplicate - both records
I would like to look at both records returned as duplicates. I am using the
following script to find the duplicates but can it only returns one record
not both. I would like to see the entire record so that I can make a
determination on more than just the criteria that I selected as the basis of
the record being a duplicate.
SELECT Patient.Last_Name,
Patient.First_Name,
Patient.Birthdate
COUNT(*) AS Dupes
FROM Patient
WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
Payor_Key = 36)
GROUP BY Last_Name, First_Name, Birthdate
HAVING (COUNT(*) > 1)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1In order to do that, you may need to put your current query as "Result
Table", something like:
SELECT P1.Last_Name,
P1.First_Name,
P1.Birthdate,
Dupes
FROM Patient P1,
(SELECT P2.Last_Name P2LastName,
P2.First_Name P2FirstName,
P2.Birthdate P2Birthday
COUNT(*) AS Dupes
FROM Patient P2
WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FROM
Patient_Elg WHERE
P2.Payor_Key = 36)
GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
HAVING (COUNT(*) > 1)) DupCount
where P1.Last_Name = P2LastName
and P1.First_Name = P2FirstName
and P1.Birthdate = P2Birthday
This code has not been tested yet but just an idear.
Perayu
"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:5685c45d8a0c6@.uwe...
>I would like to look at both records returned as duplicates. I am using the
> following script to find the duplicates but can it only returns one record
> not both. I would like to see the entire record so that I can make a
> determination on more than just the criteria that I selected as the basis
> of
> the record being a duplicate.
> SELECT Patient.Last_Name,
> Patient.First_Name,
> Patient.Birthdate
> COUNT(*) AS Dupes
> FROM Patient
> WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
> Payor_Key = 36)
> GROUP BY Last_Name, First_Name, Birthdate
> HAVING (COUNT(*) > 1)
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||This did work for me after I made some tweaks. Thanks allot.
Perayu wrote:
>In order to do that, you may need to put your current query as "Result
>Table", something like:
>SELECT P1.Last_Name,
> P1.First_Name,
> P1.Birthdate,
> Dupes
>FROM Patient P1,
> (SELECT P2.Last_Name P2LastName,
> P2.First_Name P2FirstName,
> P2.Birthdate P2Birthday
> COUNT(*) AS Dupes
> FROM Patient P2
> WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FRO
M
>Patient_Elg WHERE
> P2.Payor_Key = 36)
> GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
> HAVING (COUNT(*) > 1)) DupCount
>where P1.Last_Name = P2LastName
> and P1.First_Name = P2FirstName
> and P1.Birthdate = P2Birthday
>This code has not been tested yet but just an idear.
>Perayu
>
>[quoted text clipped - 13 lines]
Message posted via http://www.webservertalk.com
following script to find the duplicates but can it only returns one record
not both. I would like to see the entire record so that I can make a
determination on more than just the criteria that I selected as the basis of
the record being a duplicate.
SELECT Patient.Last_Name,
Patient.First_Name,
Patient.Birthdate
COUNT(*) AS Dupes
FROM Patient
WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
Payor_Key = 36)
GROUP BY Last_Name, First_Name, Birthdate
HAVING (COUNT(*) > 1)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1In order to do that, you may need to put your current query as "Result
Table", something like:
SELECT P1.Last_Name,
P1.First_Name,
P1.Birthdate,
Dupes
FROM Patient P1,
(SELECT P2.Last_Name P2LastName,
P2.First_Name P2FirstName,
P2.Birthdate P2Birthday
COUNT(*) AS Dupes
FROM Patient P2
WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FROM
Patient_Elg WHERE
P2.Payor_Key = 36)
GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
HAVING (COUNT(*) > 1)) DupCount
where P1.Last_Name = P2LastName
and P1.First_Name = P2FirstName
and P1.Birthdate = P2Birthday
This code has not been tested yet but just an idear.
Perayu
"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:5685c45d8a0c6@.uwe...
>I would like to look at both records returned as duplicates. I am using the
> following script to find the duplicates but can it only returns one record
> not both. I would like to see the entire record so that I can make a
> determination on more than just the criteria that I selected as the basis
> of
> the record being a duplicate.
> SELECT Patient.Last_Name,
> Patient.First_Name,
> Patient.Birthdate
> COUNT(*) AS Dupes
> FROM Patient
> WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
> Payor_Key = 36)
> GROUP BY Last_Name, First_Name, Birthdate
> HAVING (COUNT(*) > 1)
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||This did work for me after I made some tweaks. Thanks allot.
Perayu wrote:
>In order to do that, you may need to put your current query as "Result
>Table", something like:
>SELECT P1.Last_Name,
> P1.First_Name,
> P1.Birthdate,
> Dupes
>FROM Patient P1,
> (SELECT P2.Last_Name P2LastName,
> P2.First_Name P2FirstName,
> P2.Birthdate P2Birthday
> COUNT(*) AS Dupes
> FROM Patient P2
> WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FRO
M
>Patient_Elg WHERE
> P2.Payor_Key = 36)
> GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
> HAVING (COUNT(*) > 1)) DupCount
>where P1.Last_Name = P2LastName
> and P1.First_Name = P2FirstName
> and P1.Birthdate = P2Birthday
>This code has not been tested yet but just an idear.
>Perayu
>
>[quoted text clipped - 13 lines]
Message posted via http://www.webservertalk.com
Subscribe to:
Posts (Atom)