How to get this output

hi their
i create a temp table called tempEmpTran and it content a fileds like
Refrlno Description opt-refno

1474 New 5
1474 followup 3
1474 followup 3
1470 new 7

No my problem is i need to eleminate the refrlno which Description <> 'followup',but again the problem is if i see any referrals which repeating more than once then i need to check if any of that Rrefrlno got any Description which equal to the followup, if yes i need to eliminate the whole Referealno from the list,

Refrlno Description opt-refno
1474 New 5 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1470 new 7 -should include,cos its got description 'new'

so once all these condition satisfied then the output should looks like this

Refrlno Description opt-refno
1470 new 7

Any Idea ?
regards
Nirangatry this:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Formatting seems to be screwed on that post, heres the query again:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Hi sam,
Many thanks to u r fast respone,but in that query there is not line which eleminate the Description='Followup' ?
any idea ?
regards
Niranga|||Have you ran it?

It only returns records who have a single refrlno... so there shouldn't be any need to filter based on your sample data.

Have a play and try to understand what it does. Then you can expand on it |||

Hey, you could do this 2 ways:

Select *

from tempEmpTran

Where Refrlno not in (Select Refrlno from tempEmpTran where Description = 'Followup')

Select *

from tempEmpTran a

Where not exists (Select Refrlno from tempEmpTran b where Description = 'Followup' and a.Refrlno = b.Refrlno)

BobP

|||Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga|||

Niranga wrote:

Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga

Sorry about that, didn't bother creating the tables and testing. You just need to add an alias to the column name
count(*) becomes
count(*) as [Cnt]|||hi,

here's another alternative.
SELECT *
INTO #tempEmpTran
FROM (
SELECT 1474 AS Refrlno
, 'New' AS Description
, 5 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1470 AS Refrlno
, 'New' AS Description
, 7 AS [opt-refno]
) a

SELECT a.*
FROM #tempEmpTran a INNER JOIN
(
SELECT Refrlno
, IsNew = SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END))
FROM #tempEmpTran
GROUP BY
Refrlno
HAVING SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END)) = 0
) b ON a.RefrlNo = b.RefrlNo

DROP TABLE #tempEmpTran|||

here you go..

Code Snippet

Create Table #data (

[Refrlno] int ,

[Description] Varchar(100) ,

[opt-refno] int

);

Insert Into #data Values('1474','New','5');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1470','new','7');

Select

*

from

#Data Main

Where

Not Exists

(

Select

Sub.[Refrlno]

From

#Data Sub

Where

Main.[Refrlno]=Sub.[Refrlno]

And Sub.[Description] = 'followup'

)

How to get the Sequence number without using temp table

Here is my problem:
I have a table with following columns:
PersonID FirstName LastName
102 John Ben
103 Josh Parker
104 Mark Ben
Now if I type SELECT * FROM Person WHERE LastName = 'Ben' these two records will be displayed
PersonID FirstName LastName
102 John Ben
104 Mark Ben
But I want this to return with one additional Sequence column like this:
New Column PersonID FirstName LastName
1 102 John Ben
2 104 Mark Ben
How can I add this so called "New Column" ?


Here's one way:
SELECT
COUNT(*) AS [New Column],
P1.PersonId,
P1.FirstName,
P1.LastName
FROM Person AS P1
JOIN Person AS P2 ON
P2.LastName = P1.LastName
AND P2.PersonId <= P1.PersonId
WHERE P1.LastName = 'Ben'
GROUP BY
P1.PersonId,
P1.FirstName,
P1.LastName
|||You are AWESOME !

How to get the result of an Exec (@Sql) into a temp table.

Hi all.
I am working on some crosstab logic and needs to get my result into a
temporay table for further use and joins later in the prosedyre. It is
dynamic crosstabs so I don't know the number of columns on beforehand. After
my logic I can get a result that looks nice in QueryAnalyzer usning the Exec
command.
EXEC (@.sql)
What I want i for that result to get into a ad hock created temp table for
further use and joins. Just like
INSERT Col1 INTO #tmpTable FROM Tablename
Looks like the Exec command runs in another "space" so I can't reache the
#tmpTable even if my @.sql is correct with the INTO clause. If I print the
SQL, copies it and runs it it works fine ofcause.
Any ideas
thanx all
geirTry creating your temp table outside the Exec statement.
create table #mytemp
(
a int,
b int
)
Exec('insert ... into #mytemp')
select * from #mytemp
"Geir Holme" <geir@.multicase.no> wrote in message
news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand.
After
> my logic I can get a result that looks nice in QueryAnalyzer usning the
Exec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
> Any ideas
> thanx all
> geir
>|||Hi Jonny.
This works fine as long as you know the number of columns AND the name of
the columns. Since I am inserting a dynamic crosstab i don't know the name
of the columns and the number of columns. That's the big issue here.
Thank you for your interest so far. Mabe you have some more ideas?
regards
geir
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
> Try creating your temp table outside the Exec statement.
> create table #mytemp
> (
> a int,
> b int
> )
> Exec('insert ... into #mytemp')
> select * from #mytemp
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> After
> Exec
>|||Perhaps create a physical table in tempdb and then drop it when not needed.
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
the
the
>|||If you are doing a dynamic query like this, you will have to create a
dynamic temporary table. Consider building a permanent table in tempdb,
using a guid for the table name.
declare @.tableName varchar(40)
set @.tableName = newid()
declare @.query varchar(1000)
set @.query = 'create table tempdb..[' + @.tableName + '] ( column1
varchar(10))'
exec (@.query)
exec ('insert into tempdb..[' + @.tablename + '] values (''hello'') ')
exec ('select * from tempdb..[' + @.tablename + ']')
exec ('drop table tempdb..[' + @.tablename + ']')
Ugly, but it will work. You could also use select into instead of creating
the table. The most important thing is to use a permanent table. The guid
name of the table will ensure no name clashes.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
>|||Geir Holme wrote:
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand. Aft
er
> my logic I can get a result that looks nice in QueryAnalyzer usning the Ex
ec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps, instead of a procedure you'd like to try a function?
use Northwind
go
create FUNCTION udf_getOrders()
returns table
as
return(select top 100 * from orders)
go
select *
into #t
from dbo.udf_getOrders()
go
select * from #t
go
drop table #t
drop function dbo.udf_getOrders
go
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhv1KoechKqOuFEgEQLZcwCgyqhNQjMg+vPO
HFdAUtIi/AFH/5AAoJcq
LOei0tjH80SMhUbTd+uGEPfh
=EhCr
--END PGP SIGNATURE--

How to get row count from an inner query

Hi All,

I have the following SQL query:

select temp.emp_id, temp.rownum

from

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

) temp

where temp.rownum <=10

group by temp.emp_id

I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.

Hi,

I wrote something similar in here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2188269&SiteID=1

But the problem may be, that the Number of Rows in the outer Statement dosent match to the number of rows in the inner Statement.

Best Regards

Raimund

|||

How about this,

Code Block

with temp

as

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

)

,countfinder

as

(

select count(*) as [rowcount] from temp

)

select temp.emp_id, temp.rownum, (select [rowcount] from countfinder) [totalrowcount] from temp

where temp.rownum <=10 group by temp.emp_id

|||Brilliant.....this was a very novel way of doing it. Thank you very much. I will try out the same in my implementation.