Pls, help. TSQL way, Limit the select of a result set by type and quantity

/*
Thanks in advance for your time and knowledge you would share.
Definition:
I have actions, that are of certain type, and workload of these actions to
process
The request comes as a recordset in request table.
How do I produce a record set that has in it quantity of records less or
equal to request quantities by type
I know that I can open a cursor, use (set rowcount @.memvar) and loop to
accumulate final resultset.
I know that if I define an identity field (or select into temp table with
identity) and count these records by selfjoining
But is there a better non-temp table solution?
Thanks again for your ideas
*/
set nocount on
declare @.work table (orderID int, status int, actionID int, primary key
(orderid, status,actionID))
declare @.actions table (actionID int, actionType int, primary key
(actionID))
declare @.request table (actionType int, RecQty int)
declare @.temp table (rec int identity, actionType int , orderID int, status
int, actionID int)
insert @.request values (1,3) -- deliver at least 3 records of this type
insert @.request values (2,8) -- deliver at least 8 records of this type
insert @.request values (3,3) -- deliver at least 3 records of this type
insert @.actions values (1, 1)
insert @.actions values (2, 1)
insert @.actions values (3, 2)
insert @.actions values (4, 2)
insert @.actions values (5, 2)
insert @.actions values (6, 3)
insert @.work values (1, 1, 1)
insert @.work values (1, 1, 2)
insert @.work values (1, 1, 3)
insert @.work values (2, 1, 1)
insert @.work values (2, 1, 2)
insert @.work values (2, 2, 3)
insert @.work values (2, 2, 4)
insert @.work values (2, 1, 6)
insert @.work values (3, 1, 1)
insert @.work values (3, 3, 2)
insert @.work values (3, 1, 5)
insert @.work values (3, 1, 6)
insert @.work values (4, 1, 1)
insert @.work values (4, 1, 2)
insert @.work values (4, 1, 5)
insert @.work values (4, 1, 6)
insert @.work values (4, 2, 6)
insert @.work values (4, 3, 6)
-- Possible solution
-- Total records
select actionType, count(*) recordcount
from @.actions a
join @.work w on w.actionID= a.actionID
group by actionType
insert @.temp (actionType, orderID, status, actionID)
select a.actionType, orderID, status, w.actionID
from @.actions a
join @.work w on w.actionID= a.actionID
-- This is what I am after, but any way not to use temp table as this would
cause procedure recompiles and this is often called one.
select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID, count(*)
from @.temp s1
join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
join @.request r on r.actionType = s1.actionType
group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
having count(*) <= r.RecQtyTry that based on the example of the oubs database:
DECLARE @.RANK INT
SET @.rank = 5
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
HAVING count(*) < @.Rank
To be found on:
http://support.microsoft.com/defaul...b;en-us;Q186133
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
--
"Farmer" <someone@.somewhere.com> schrieb im Newsbeitrag
news:Of0pCjyUFHA.544@.TK2MSFTNGP15.phx.gbl...
> /*
> Thanks in advance for your time and knowledge you would share.
> Definition:
> I have actions, that are of certain type, and workload of these actions to
> process
> The request comes as a recordset in request table.
> How do I produce a record set that has in it quantity of records less or
> equal to request quantities by type
> I know that I can open a cursor, use (set rowcount @.memvar) and loop to
> accumulate final resultset.
> I know that if I define an identity field (or select into temp table with
> identity) and count these records by selfjoining
> But is there a better non-temp table solution?
> Thanks again for your ideas
> */
> set nocount on
> declare @.work table (orderID int, status int, actionID int, primary key
> (orderid, status,actionID))
> declare @.actions table (actionID int, actionType int, primary key
> (actionID))
> declare @.request table (actionType int, RecQty int)
> declare @.temp table (rec int identity, actionType int , orderID int,
> status int, actionID int)
> insert @.request values (1,3) -- deliver at least 3 records of this type
> insert @.request values (2,8) -- deliver at least 8 records of this type
> insert @.request values (3,3) -- deliver at least 3 records of this type
>
> insert @.actions values (1, 1)
> insert @.actions values (2, 1)
> insert @.actions values (3, 2)
> insert @.actions values (4, 2)
> insert @.actions values (5, 2)
> insert @.actions values (6, 3)
> insert @.work values (1, 1, 1)
> insert @.work values (1, 1, 2)
> insert @.work values (1, 1, 3)
> insert @.work values (2, 1, 1)
> insert @.work values (2, 1, 2)
> insert @.work values (2, 2, 3)
> insert @.work values (2, 2, 4)
> insert @.work values (2, 1, 6)
> insert @.work values (3, 1, 1)
> insert @.work values (3, 3, 2)
> insert @.work values (3, 1, 5)
> insert @.work values (3, 1, 6)
> insert @.work values (4, 1, 1)
> insert @.work values (4, 1, 2)
> insert @.work values (4, 1, 5)
> insert @.work values (4, 1, 6)
> insert @.work values (4, 2, 6)
> insert @.work values (4, 3, 6)
> -- Possible solution
> -- Total records
> select actionType, count(*) recordcount
> from @.actions a
> join @.work w on w.actionID= a.actionID
> group by actionType
> insert @.temp (actionType, orderID, status, actionID)
> select a.actionType, orderID, status, w.actionID
> from @.actions a
> join @.work w on w.actionID= a.actionID
> -- This is what I am after, but any way not to use temp table as this
> would cause procedure recompiles and this is often called one.
> select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID,
> count(*)
> from @.temp s1
> join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
> join @.request r on r.actionType = s1.actionType
> group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
> having count(*) <= r.RecQty
>

Please Help! How to get list of SQL Servers in LAN?

Hello,
Is it possible to get list of SQL Servers existed in the LAN (on VC++)?
Thanks in advance,
PolaPola
I'm an expert of VC++ but it works onVB6
Dim i As Integer
'Use the SQL DMO Application Object to find the
available SQL Servers
Set oSQLServerDMOApp = New SQLDMO.Application
'Don't show events
gShowServerEvents = False
Dim namX As NameList
Set namX = oSQLServerDMOApp.ListAvailableSQLServers
For i = 1 To namX.Count
txtServer.AddItem namX.Item(i)
Next
"Pola" <Pola@.discussions.microsoft.com> wrote in message
news:4B0C5500-CBBA-4573-B551-AFCAD1D647F8@.microsoft.com...
> Hello,
> Is it possible to get list of SQL Servers existed in the LAN (on VC++)?
> Thanks in advance,
> Pola|||Thank you,
Is it possible to get an example on SQL?
Thanks in advance,
Pola
"Uri Dimant" wrote:
> Pola
> I'm an expert of VC++ but it works onVB6
> Dim i As Integer
> 'Use the SQL DMO Application Object to find the
> available SQL Servers
> Set oSQLServerDMOApp = New SQLDMO.Application
> 'Don't show events
> gShowServerEvents = False
> Dim namX As NameList
> Set namX = oSQLServerDMOApp.ListAvailableSQLServers
> For i = 1 To namX.Count
> txtServer.AddItem namX.Item(i)
> Next
>
>
>
>
> "Pola" <Pola@.discussions.microsoft.com> wrote in message
> news:4B0C5500-CBBA-4573-B551-AFCAD1D647F8@.microsoft.com...
> > Hello,
> >
> > Is it possible to get list of SQL Servers existed in the LAN (on VC++)?
> >
> > Thanks in advance,
> > Pola
>
>|||Pola
CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
"Pola" <Pola@.discussions.microsoft.com> wrote in message
news:D5730D3E-9078-488A-B07E-59926C09F65F@.microsoft.com...
> Thank you,
> Is it possible to get an example on SQL?
> Thanks in advance,
> Pola
> "Uri Dimant" wrote:
>> Pola
>> I'm an expert of VC++ but it works onVB6
>> Dim i As Integer
>> 'Use the SQL DMO Application Object to find the
>> available SQL Servers
>> Set oSQLServerDMOApp = New SQLDMO.Application
>> 'Don't show events
>> gShowServerEvents = False
>> Dim namX As NameList
>> Set namX = oSQLServerDMOApp.ListAvailableSQLServers
>> For i = 1 To namX.Count
>> txtServer.AddItem namX.Item(i)
>> Next
>>
>>
>>
>>
>> "Pola" <Pola@.discussions.microsoft.com> wrote in message
>> news:4B0C5500-CBBA-4573-B551-AFCAD1D647F8@.microsoft.com...
>> > Hello,
>> >
>> > Is it possible to get list of SQL Servers existed in the LAN (on VC++)?
>> >
>> > Thanks in advance,
>> > Pola
>>|||thank you very much,
Pola
"Uri Dimant" wrote:
> Pola
> CREATE PROCEDURE dbo.ListLocalServers
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #servers(sname VARCHAR(255))
> INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
> DELETE #servers WHERE sname='Servers:'
> SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
> DROP TABLE #servers
> END
>
> "Pola" <Pola@.discussions.microsoft.com> wrote in message
> news:D5730D3E-9078-488A-B07E-59926C09F65F@.microsoft.com...
> > Thank you,
> > Is it possible to get an example on SQL?
> >
> > Thanks in advance,
> > Pola
> >
> > "Uri Dimant" wrote:
> >
> >> Pola
> >> I'm an expert of VC++ but it works onVB6
> >>
> >> Dim i As Integer
> >> 'Use the SQL DMO Application Object to find the
> >> available SQL Servers
> >> Set oSQLServerDMOApp = New SQLDMO.Application
> >> 'Don't show events
> >> gShowServerEvents = False
> >>
> >> Dim namX As NameList
> >> Set namX = oSQLServerDMOApp.ListAvailableSQLServers
> >> For i = 1 To namX.Count
> >> txtServer.AddItem namX.Item(i)
> >> Next
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Pola" <Pola@.discussions.microsoft.com> wrote in message
> >> news:4B0C5500-CBBA-4573-B551-AFCAD1D647F8@.microsoft.com...
> >> > Hello,
> >> >
> >> > Is it possible to get list of SQL Servers existed in the LAN (on VC++)?
> >> >
> >> > Thanks in advance,
> >> > Pola
> >>
> >>
> >>
>
>|||Hello Uri,
This script display list of SQL servers only from the domain.
Is it possible to get list of the SQL servers in LAN?
Thank you in advance,
Pola
"Pola" wrote:
> thank you very much,
> Pola
> "Uri Dimant" wrote:
> > Pola
> > CREATE PROCEDURE dbo.ListLocalServers
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > CREATE TABLE #servers(sname VARCHAR(255))
> > INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
> > DELETE #servers WHERE sname='Servers:'
> > SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
> > DROP TABLE #servers
> > END
> >
> >
> > "Pola" <Pola@.discussions.microsoft.com> wrote in message
> > news:D5730D3E-9078-488A-B07E-59926C09F65F@.microsoft.com...
> > > Thank you,
> > > Is it possible to get an example on SQL?
> > >
> > > Thanks in advance,
> > > Pola
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> Pola
> > >> I'm an expert of VC++ but it works onVB6
> > >>
> > >> Dim i As Integer
> > >> 'Use the SQL DMO Application Object to find the
> > >> available SQL Servers
> > >> Set oSQLServerDMOApp = New SQLDMO.Application
> > >> 'Don't show events
> > >> gShowServerEvents = False
> > >>
> > >> Dim namX As NameList
> > >> Set namX = oSQLServerDMOApp.ListAvailableSQLServers
> > >> For i = 1 To namX.Count
> > >> txtServer.AddItem namX.Item(i)
> > >> Next
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Pola" <Pola@.discussions.microsoft.com> wrote in message
> > >> news:4B0C5500-CBBA-4573-B551-AFCAD1D647F8@.microsoft.com...
> > >> > Hello,
> > >> >
> > >> > Is it possible to get list of SQL Servers existed in the LAN (on VC++)?
> > >> >
> > >> > Thanks in advance,
> > >> > Pola
> > >>
> > >>
> > >>
> >
> >
> >