avg of most current 50 only

I have a query that returns the averages for a selected group of records.
select AVG(h.stkhstClose), h.stkhstcsisym
from stkhst h
JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
and u.unvmemUnvID = 29001
group by h.stkhstcsisym
order by h.stkhstcsisym
this works and returns 99 averages. However, I need it to average only the
last 50 records of each group based on the date. I'm looking for a clean way
to do this without using a temporary table or cursor. and ideas would be
appreciated
thanks
kesJust a guess. See my signature for a more precise answer.
SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
<whatever> ORDER BY datecolumn DESC) x
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> I have a query that returns the averages for a selected group of records.
> select AVG(h.stkhstClose), h.stkhstcsisym
> from stkhst h
> JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
> and u.unvmemUnvID = 29001
> group by h.stkhstcsisym
> order by h.stkhstcsisym
> this works and returns 99 averages. However, I need it to average only the
> last 50 records of each group based on the date. I'm looking for a clean
way
> to do this without using a temporary table or cursor. and ideas would be
> appreciated
> thanks
> kes|||Thank You Aaron (you seem to answer a lot of my postings and your suggestion
s
have always proven helpful)
this will get the average for one group but how about the rest? Would a
where stkhstDate IN (select top 50 stkhstdate from stkhst where stkhstid =
xx order by stkhstdate DESC)
thank you
kes
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||>> this will get the average for one group but how about the rest?
Did you read Aaron's post? To repeat:
See his signature for a more precise answer.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Anith|||ok, fair enough.
stkhst:
CREATE TABLE [stkhst] (
[stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
[stkhstCsiSym] [int] NULL ,
[stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstDate] [int] NULL ,
[stkhstOpen] [decimal](9, 4) NULL ,
[stkhstHi] [decimal](9, 4) NULL ,
[stkhstLow] [decimal](9, 4) NULL ,
[stkhstClose] [decimal](9, 4) NULL ,
[stkhstVol] [int] NULL ,
[stkhstDiv] [int] NULL ,
[stkhstX] [int] NULL ,
[stkhstO] [int] NULL ,
[stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
[stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstCPosL] [int] NULL ,
[stkhstCPosH] [int] NULL ,
[stkHstCCol] [int] NULL
) ON [PRIMARY]
GO
unvmem:
CREATE TABLE [unvmem] (
[unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
[unvmemCsiId] [int] NOT NULL ,
[unvmemUnvID] [int] NOT NULL ,
[unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
) ON [PRIMARY]
GO
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||noted, posted
thanks
kes
"Anith Sen" wrote:

> Did you read Aaron's post? To repeat:
> See his signature for a more precise answer.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> --
> Anith
>
>|||What about sample data and desired results?
The point is that we're not going to drive to Wichita or Kansas or wherever
you are to see what data is in your table and try to figure out what result
you want from that data. And we're certainly not going to spend our
afternoon inventing fictitious but possibly unrealistic data to populate
your empty table, then spend time developing a solution against that, only
to find out all the "buts" that come with the assumptions we made. Please
supply sample data in the form of INSERT statements, and the resultset you
want based on that data.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO|||Kurt,
Your tables seem to have no primary keys which is a critical design flaw.
Generally for such problems, others cannot test the solutions without sample
data. You have not provided that either. Also as a side note, if your scheme
allows, you may want to look closely at your naming convention as well.
Here is another attempt with guesswork:
SELECT AVG( stkhstClose ), stkhstcsisym
FROM ( SELECT TOP 50 h.stkhstClose, h.stkhstcsisym
FROM stkhst h
INNER JOIN unvmem u
ON h.stkhstcsisym = u.unvmemCsiId
WHERE u.unvmemUnvID = 29001
ORDER BY h.stkhstcsisym ) D ( stkhstClose, stkhstcsisym )
GROUP BY stkhstcsisym ;
Anith|||Kurt,
You want to extract and average the last 50 records for each group...
Just add a where clause that restricts the query to operate only on those
records which have 50 or less "partners" (in the same group) after them...
Select AVG(h.stkhstClose), h.stkhstcsisym
From stkhst h
Where (Select Count(*) From stkhst
Where stkhstcsisym = h.stkhstcsisym
And DateColumn >= h.DateColumn) <= 50
Select * From
"Kurt Schroeder" wrote:
> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO
> "Aaron [SQL Server MVP]" wrote:
>|||My apologies, I did not mean to imply that I needed more than advise. Please
understand that I do not feel it appropriate to ask for more than just that.
I feel it would be unfair to you or anyone else to do my work for me.
Aaron, your first posting to my question gave me what I needed to search for
the answer. My real query is much more complex, but this part of it was
simple enough to post for advice.
Again I wish to thank you for your help.
Humbly yours
kes
"Aaron [SQL Server MVP]" wrote:

> What about sample data and desired results?
> The point is that we're not going to drive to Wichita or Kansas or whereve
r
> you are to see what data is in your table and try to figure out what resul
t
> you want from that data. And we're certainly not going to spend our
> afternoon inventing fictitious but possibly unrealistic data to populate
> your empty table, then spend time developing a solution against that, only
> to find out all the "buts" that come with the assumptions we made. Please
> supply sample data in the form of INSERT statements, and the resultset you
> want based on that data.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>

Autonumbering the Records from XML

Hi,

I have a table "Del_Table", which must contain a column with autoincrement ability. I have added one column named as "Auto" and put it as Primary key, Identity and Integer. I am grabbing the data from an XML file. I have tried with the following Stored Procedure, but it gives an error as "An explicit value for the identity column in Del_Table can only be specified when a column list is used and IDENTITY_INSERT is ON". I have tried to to turn on IDENTITY_INSERT but still it does not work.

Code Snippet

CREATE PROCEDURE insertForecast
(@.OrderDoc ntext)
AS
DECLARE

@.hDoc int

EXEC sp_xml_preparedocument
@.hDoc output,
@.OrderDoc

INSERT INTO [Del_Table]
SELECT *
from OPENXML (@.hDoc,'Data/Delivery_Forecast/Forecast',3)
with
(
[ID] numeric '@.mp:id',
Document_Number char(35) '@.Document_Number',
Com_Date char(35) '@.Com_Date',
Code char (35) '@.code',
Port char (35) '@.port',
EPort char (35) '@.eport',
Number char (35) '@.number',
Instruction_Code char (35) '@.inst_Code',
Delivery_Date char (35) '@.del_date',
Quantity char (35) '@.quantity',
Status_Indicator char (35) '@.status_indicator'
)
ORDER BY
Document_Number

EXEC sp_xml_removedocument @.hDoc
GO

Any suggestions?

Thanks...

Specify the columns in the INSERT statement like the error message is stating (and still use the IDENTITY_INSERT of course).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Autonumber problems

Hello,
I have 2 problems with autonumber in a form. I use sql server as back-end,
but when adding records i don't see the autonumber.
Also i use primary field where it values comes from the autonumber, which
results in a error that it cannot insert null values.
How can i resolve this issue?
ThxHi,
Can you please send me the Table structure as well the script which try to
insert into table.
Normally the primary key field will be kept as identity and we do not want
to insert value for that column.
Thanks
Hari
MCDBA
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I have 2 problems with autonumber in a form. I use sql server as back-end,
> but when adding records i don't see the autonumber.
> Also i use primary field where it values comes from the autonumber, which
> results in a error that it cannot insert null values.
> How can i resolve this issue?
> Thx
>

---

|||Unlike Access/Jet, you don't see identity column numbers in a form
until AFTER the row is committed. You cannot insert nulls in a primary
key column. That causes an error in both Jet and SQL Server.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekil" <ezekiel@.lycos.nl>
wrote:

quote:

---

>Hello,
>I have 2 problems with autonumber in a form. I use sql server as back-end,
>but when adding records i don't see the autonumber.
>Also i use primary field where it values comes from the autonumber, which
>results in a error that it cannot insert null values.
>How can i resolve this issue?
>Thx
>

---

|||Hi,
I don't use any script to insert records. I add records directly into the
table. As for the table structure, see below:
1 AutoNr int 4 0 (autonumber)
2 PersonIdnr int 4 0 (primary)
0 CarIdnr int 4 0
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KB%23zwa5DHA.2300@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi,
> Can you please send me the Table structure as well the script which try to
> insert into table.
> Normally the primary key field will be kept as identity and we do not want
> to insert value for that column.
> Thanks
> Hari
> MCDBA
>
> "Ezekil" <ezekiel@.lycos.nl> wrote in message
> news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...
back-end,[QUOTE]
which[QUOTE]
>

---

|||Hi,
To overcome these , can you try the below to insert data,
insert into table(PersonIdnr,CarIdnr ) values (2,0)
It is not required to explicitly provide data for Identity (Autonumber)
column.
Thanks
hari
MCDBA
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:8ajf1010po8jlt48nuct0i64gmgch3637t@.
4ax.com...

quote:

---

> Unlike Access/Jet, you don't see identity column numbers in a form
> until AFTER the row is committed. You cannot insert nulls in a primary
> key column. That causes an error in both Jet and SQL Server.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekil" <ezekiel@.lycos.nl>
> wrote:
>
back-end,[QUOTE]
>

---

Autonumber problems

Hello,
I have 2 problems with autonumber in a form. I use sql server as back-end,
but when adding records i don't see the autonumber.
Also i use primary field where it values comes from the autonumber, which
results in a error that it cannot insert null values.
How can i resolve this issue?
ThxHi,
Can you please send me the Table structure as well the script which try to
insert into table.
Normally the primary key field will be kept as identity and we do not want
to insert value for that column.
Thanks
Hari
MCDBA
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have 2 problems with autonumber in a form. I use sql server as back-end,
> but when adding records i don't see the autonumber.
> Also i use primary field where it values comes from the autonumber, which
> results in a error that it cannot insert null values.
> How can i resolve this issue?
> Thx
>|||Unlike Access/Jet, you don't see identity column numbers in a form
until AFTER the row is committed. You cannot insert nulls in a primary
key column. That causes an error in both Jet and SQL Server.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekiël" <ezekiel@.lycos.nl>
wrote:
>Hello,
>I have 2 problems with autonumber in a form. I use sql server as back-end,
>but when adding records i don't see the autonumber.
>Also i use primary field where it values comes from the autonumber, which
>results in a error that it cannot insert null values.
>How can i resolve this issue?
>Thx
>|||Hi,
I don't use any script to insert records. I add records directly into the
table. As for the table structure, see below:
1 AutoNr int 4 0 (autonumber)
2 PersonIdnr int 4 0 (primary)
0 CarIdnr int 4 0
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KB%23zwa5DHA.2300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can you please send me the Table structure as well the script which try to
> insert into table.
> Normally the primary key field will be kept as identity and we do not want
> to insert value for that column.
> Thanks
> Hari
> MCDBA
>
> "Ezekiël" <ezekiel@.lycos.nl> wrote in message
> news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I have 2 problems with autonumber in a form. I use sql server as
back-end,
> > but when adding records i don't see the autonumber.
> > Also i use primary field where it values comes from the autonumber,
which
> > results in a error that it cannot insert null values.
> >
> > How can i resolve this issue?
> >
> > Thx
> >
> >
>|||Hi,
To overcome these , can you try the below to insert data,
insert into table(PersonIdnr,CarIdnr ) values (2,0)
It is not required to explicitly provide data for Identity (Autonumber)
column.
Thanks
hari
MCDBA
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:8ajf1010po8jlt48nuct0i64gmgch3637t@.4ax.com...
> Unlike Access/Jet, you don't see identity column numbers in a form
> until AFTER the row is committed. You cannot insert nulls in a primary
> key column. That causes an error in both Jet and SQL Server.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekiël" <ezekiel@.lycos.nl>
> wrote:
> >Hello,
> >
> >I have 2 problems with autonumber in a form. I use sql server as
back-end,
> >but when adding records i don't see the autonumber.
> >Also i use primary field where it values comes from the autonumber, which
> >results in a error that it cannot insert null values.
> >
> >How can i resolve this issue?
> >
> >Thx
> >
>

Autonumber field

Hi there,
Is there a way to have an autonumber field (as in MSAccess) or some sort of
a counter field that automatically numbers records?
Thank you in advance!What version of SQL Server and why do you need it? Whats the usage of that
column? You could use identity property but please explain the usage.
MC
"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:B6BD5C03-55C4-4FAB-A318-DB0763F0978D@.microsoft.com...
> Hi there,
> Is there a way to have an autonumber field (as in MSAccess) or some sort
> of
> a counter field that automatically numbers records?
> Thank you in advance!|||Well I'm busy writing a front-end in Access to populate records into an SQL
table. Firstly, the way I understand it, is to have a primary key set up on
one of the fields in order to update this table from an app like Access. But
none of the fields currently in this table can be used for a primary key, so
I've created a counter that must be the primary key. This then must obviously
be some sort of an automated numbered field...
"MC" wrote:
> What version of SQL Server and why do you need it? Whats the usage of that
> column? You could use identity property but please explain the usage.
>
> MC
>
> "PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
> news:B6BD5C03-55C4-4FAB-A318-DB0763F0978D@.microsoft.com...
> > Hi there,
> >
> > Is there a way to have an autonumber field (as in MSAccess) or some sort
> > of
> > a counter field that automatically numbers records?
> >
> > Thank you in advance!
>|||Okay. For that, you can use identity property. Usually, declare int column
and set identity. If you want to add it to exeisting table it would be
something like:
alter table Test
add ColumnName int identity(1,1) not null
MC
"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:0585DF2B-3173-44FE-8941-36D9A84C6DAB@.microsoft.com...
> Well I'm busy writing a front-end in Access to populate records into an
> SQL
> table. Firstly, the way I understand it, is to have a primary key set up
> on
> one of the fields in order to update this table from an app like Access.
> But
> none of the fields currently in this table can be used for a primary key,
> so
> I've created a counter that must be the primary key. This then must
> obviously
> be some sort of an automated numbered field...
> "MC" wrote:
>> What version of SQL Server and why do you need it? Whats the usage of
>> that
>> column? You could use identity property but please explain the usage.
>>
>> MC
>>
>> "PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
>> news:B6BD5C03-55C4-4FAB-A318-DB0763F0978D@.microsoft.com...
>> > Hi there,
>> >
>> > Is there a way to have an autonumber field (as in MSAccess) or some
>> > sort
>> > of
>> > a counter field that automatically numbers records?
>> >
>> > Thank you in advance!

Autonumber disadvantages

hi,
i am thinking of using autonumber in my table which is more likely to grow over time. but i am also concerned that what if the records get too much? is there is any other fields to use instead of autonumber? is uniqueidentifier a better option? i have a bit of discussion with my colleague about this matter.

any help is appreciatedIf you use BigInt as your ID number, you cannot exhaust the number of records in a lifetime, not even in a couple of hundred lifetimes.

This is how many records you can accomodate with BigInt as identifier:

9,223,372,036,854,775,807

9 Quintillion
9,223 Quadrillion
9,223,372 Trillion
etc...

You get the picture. I do not recommend using 'uniqueidentifier' as they take doubly more place than BigInt.

BigInt = 8 bytes
uniqueidentifier = 16 bytes

There are situations where 'uniqueidentifier' may come in handy, but more seldom than often.