Can we connect to SQL server 2000 through Internet using a static IP?

Dear All,
Can we connect to SQL Server 2000 through internet using static ip?
We have a static IP in our office and we would like one of our customer to
connect to our database using this static IP. Is this possible?
Regards,
Peri> Can we connect to SQL Server 2000 through internet using static ip?
> We have a static IP in our office and we would like one of our customer to
> connect to our database using this static IP. Is this possible?
Yes, but typically not recommended. Without a firewall in place explicitly
allowing that specific IP in, your database is exposed to everyone on the
Internet.
So, the question on my mind is, have you tried it?
A|||Thanks a lot.
But can you please tell me which port should I open?
Regards,
Peri
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uXu0vpemGHA.2316@.TK2MSFTNGP04.phx.gbl...
> Peri wrote:
> Certainly possible, but use extreme caution. Doing this will require an
> open port through your firewall. Opening that port will expose your
> server to the outside world. I would HIGHLY recommend configuring a
> firewall rule to allow ONLY connections from your customer's IP to connect
> to the newly opened port. Get your network people involved to make sure
> this is done securely.

can use Where in Dts query?

i create a model mining to know percent of product, which is like more by customer is have percent biger than other products that is don't like by customer.

i use DTS to get result to sql server.

select flattened

predict([modelName].[tablePredict],Include_statistic)

from

[modelName]

prediction join

.....

i have result with a table A in sql server:

productid $support ... $adjustedprobability

1 0.0 ...

1 0.0 ...

1 63.3 .....

how can i write in Dts so that table A not have record with value $support=0.0

- second: percent people like that product is knowned by $adjustedprobability or $support and value small or big

1:

SELECT FLATTENED
(SELECT * FROM Predict([modelName.tablePredict,INCLUDE_STATISTICS) WHERE $support > 0) as p
FROM [modelName]
PREDICTION JOIN ....

2: If you want descending, you can just use the column in the query - e.g.
SELECT FLATTENED Predict(model.table, 5, $AdjustedProbability) FROM ...

If you want bottom you need to use BottomCount(Predict(model.table),include_statistics), $support,5)

You should check my syntax - I just typed this from memory

Thx

|||

thank you very much.

you right when tell me check my syntax, there are some mistake.

thank you again

|||

hi, where can i put where in this query. it gives error when i try to use where clause.

i want 5 products but also their $AdjustedProbability should be > 0.36 ex.

SELECT FLATTENED

TopCount(Predict([Customer Products],

INCLUDE_STATISTICS) ,

$AdjustedProbability ,

5)

FROM [Basket Analysis]

NATURAL PREDICTION JOIN

( SELECT (

SELECT 'Product A' AS [Product]

)

AS [Customer Products] ) AS t

can use Where in Dts query?

i create a model mining to know percent of product, which is like more by customer is have percent biger than other products that is don't like by customer.

i use DTS to get result to sql server.

select flattened

predict([modelName].[tablePredict],Include_statistic)

from

[modelName]

prediction join

.....

i have result with a table A in sql server:

productid $support ... $adjustedprobability

1 0.0 ...

1 0.0 ...

1 63.3 .....

how can i write in Dts so that table A not have record with value $support=0.0

- second: percent people like that product is knowned by $adjustedprobability or $support and value small or big

1:

SELECT FLATTENED
(SELECT * FROM Predict([modelName.tablePredict,INCLUDE_STATISTICS) WHERE $support > 0) as p
FROM [modelName]
PREDICTION JOIN ....

2: If you want descending, you can just use the column in the query - e.g.
SELECT FLATTENED Predict(model.table, 5, $AdjustedProbability) FROM ...

If you want bottom you need to use BottomCount(Predict(model.table),include_statistics), $support,5)

You should check my syntax - I just typed this from memory

Thx

|||

thank you very much.

you right when tell me check my syntax, there are some mistake.

thank you again

|||

hi, where can i put where in this query. it gives error when i try to use where clause.

i want 5 products but also their $AdjustedProbability should be > 0.36 ex.

SELECTFLATTENED

TopCount(Predict([Customer Products],

INCLUDE_STATISTICS) ,

$AdjustedProbability ,

5)

FROM [Basket Analysis]

NATURALPREDICTIONJOIN

( SELECT (

SELECT 'Product A' AS [Product]

)

AS [Customer Products] ) AS t

Can this be solved with replication.

Hi,
I have 5 offices accross the US. At the HQ we have a legacy system (unix)
with customer data. I want the customer data to be replicated to the offices
SQL servers (MSDE) automatically. The legacy system does not support
replication so I was thinking of running a DTS task nightly to copy the
entire table (there is no time stamp on legacy ststem table) to a SQL server
at the HQ and then replicate but what I am not sure of is I have to truncate
the customer table from the HQ SQL Server before running the DTS task. How
would this affect the replication to the offices? I am dealing with 8000 rows
here.
A truncate table does not get replicated, so if this were your loading
strategy, it would work once and then subsequent cycles would throw a pile
of errors. However, you could accomplish this with snapshot replication as
long as you can overwrite each subscriber each night.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:C1BE2F0C-0342-404D-B1F7-1345027F44EE@.microsoft.com...
> Hi,
> I have 5 offices accross the US. At the HQ we have a legacy system (unix)
> with customer data. I want the customer data to be replicated to the
> offices
> SQL servers (MSDE) automatically. The legacy system does not support
> replication so I was thinking of running a DTS task nightly to copy the
> entire table (there is no time stamp on legacy ststem table) to a SQL
> server
> at the HQ and then replicate but what I am not sure of is I have to
> truncate
> the customer table from the HQ SQL Server before running the DTS task. How
> would this affect the replication to the offices? I am dealing with 8000
> rows
> here.
|||How long would the snapshot take for 5 sites? Also can this be done at a
scheduled time or can I have the DTS execute the replication after the new
customer data has been imported?
Thanks
"Michael Hotek" wrote:

> A truncate table does not get replicated, so if this were your loading
> strategy, it would work once and then subsequent cycles would throw a pile
> of errors. However, you could accomplish this with snapshot replication as
> long as you can overwrite each subscriber each night.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:C1BE2F0C-0342-404D-B1F7-1345027F44EE@.microsoft.com...
>
>
|||Both. DTS can kick it off and it can also be scheduled. As for how long,
no idea. That would depend upon thevolume of data, connectivity between
each site, amount of available network bandwidth, if the servers are doing
anything else, and lots of other factors.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2735F953-D4F3-41F2-A3B9-69AF71A132AF@.microsoft.com...[vbcol=seagreen]
> How long would the snapshot take for 5 sites? Also can this be done at a
> scheduled time or can I have the DTS execute the replication after the new
> customer data has been imported?
> Thanks
> "Michael Hotek" wrote:
|||8000 rows is not that much (assuming the rows are not 'extra-wide').
An snapshot for that is probably smaller than 1mb or so (depending on your
structure, of course). So, the snapshot should be built really fast, and
downloaded in a few minutes (tops).
BTW: the snapshot is taken at the publisher, and it is only one unless you
are using dynamic snapshot/filters (merge).
You can have the DTS execute the snapshot after finishing. Of course, the
data in the sites is going to be wiped out every single time you do this.
That shouldn't be a problem (since it seems to be what you want).
Jos.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2735F953-D4F3-41F2-A3B9-69AF71A132AF@.microsoft.com...[vbcol=seagreen]
> How long would the snapshot take for 5 sites? Also can this be done at a
> scheduled time or can I have the DTS execute the replication after the new
> customer data has been imported?
> Thanks
> "Michael Hotek" wrote:
|||In these situations, I dump the table from the foreign system into sql
(publisher) periodically, then *compare the table* to an identical,
replicated table, except for the rowguid of course, then only delete,ins,
upd are replicated
I've made generic compare sp's to handle any number of tables
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23sOTAz$KGHA.3424@.TK2MSFTNGP12.phx.gbl...
> 8000 rows is not that much (assuming the rows are not 'extra-wide').
> An snapshot for that is probably smaller than 1mb or so (depending on your
> structure, of course). So, the snapshot should be built really fast, and
> downloaded in a few minutes (tops).
> BTW: the snapshot is taken at the publisher, and it is only one unless you
> are using dynamic snapshot/filters (merge).
> You can have the DTS execute the snapshot after finishing. Of course, the
> data in the sites is going to be wiped out every single time you do this.
> That shouldn't be a problem (since it seems to be what you want).
> Jos.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:2735F953-D4F3-41F2-A3B9-69AF71A132AF@.microsoft.com...
>
|||Thanks lads!
"Chris" wrote:

> Hi,
> I have 5 offices accross the US. At the HQ we have a legacy system (unix)
> with customer data. I want the customer data to be replicated to the offices
> SQL servers (MSDE) automatically. The legacy system does not support
> replication so I was thinking of running a DTS task nightly to copy the
> entire table (there is no time stamp on legacy ststem table) to a SQL server
> at the HQ and then replicate but what I am not sure of is I have to truncate
> the customer table from the HQ SQL Server before running the DTS task. How
> would this affect the replication to the offices? I am dealing with 8000 rows
> here.
|||Hi,
The Snapshot replications works fine. Thanks again!
"Chris" wrote:

> Hi,
> I have 5 offices accross the US. At the HQ we have a legacy system (unix)
> with customer data. I want the customer data to be replicated to the offices
> SQL servers (MSDE) automatically. The legacy system does not support
> replication so I was thinking of running a DTS task nightly to copy the
> entire table (there is no time stamp on legacy ststem table) to a SQL server
> at the HQ and then replicate but what I am not sure of is I have to truncate
> the customer table from the HQ SQL Server before running the DTS task. How
> would this affect the replication to the offices? I am dealing with 8000 rows
> here.
|||Hi,
How do I set snapshot to overwrite the the subscriber? Or this does
automatically?
Thanks
"Michael Hotek" wrote:

> A truncate table does not get replicated, so if this were your loading
> strategy, it would work once and then subsequent cycles would throw a pile
> of errors. However, you could accomplish this with snapshot replication as
> long as you can overwrite each subscriber each night.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:C1BE2F0C-0342-404D-B1F7-1345027F44EE@.microsoft.com...
>
>

can this be done?

We have a large customer database in MSSQL 2000, and we are looking at scheduling software that uses MS-Access for the DB. is there an easy way to take the customer data from our AR_CUST table in SQL and link/update/insert as into Access? I've seen all kinds of stuff about going from Access to SQL, btu not the other way. HELP!
AndyI believe Access has a table import utility which allows this

You will find it in
Files --> Get External data --> Import|||yup,
I see that. in order to use it though, we would have to import every time we add a customer before we could schedule them. I was looking for more of an ODBC/dynamic type thing.|||Don't import in to Access!

Perform an Link to SQL Server from Access...using Code or File>GetExternalData>Link

If you do the later you'll need to set up an ODBC Connection (Code is better)

DONT USE sa as your login|||HEY BRETT,
I've done the link thing, and the SQL table appears in the DB for the new software. HOWEVER, the DB already has a table (Customers) and the SQL table is AR_CUST. The field names don't match, and I can't seem to find a way to get the data from SQL INTO the customer table!|||The "application" has data tables already?

Ohh I get it...

1st make a backup copy of the application, and put it away somewhere safe..

How big is the app btw?

This may be tedious..actually I wrote code to do this...

But create what I call Alias Views for the table...

Queries and tables in Access are referenced the same way...

Take your AR_CUST, and rename it with an "X_" in front...

The create abn access query called AR_CUST..use the sql server customer table as the data source

Now map the fields...make sure that every column in the AR_CUST table is there...even if you don't have that field in sql server..

AND make sure the names are identical to AR_CUST

for example if SQL Sever has LName and AR_CUST is Last_Name

Map it in the QBE Grid as Last_Name: LNAME

Do that for every column in the table.

Now do it for every table

This way, you've "Faked" the code out...it'll never know it's talking to sql server...

Now, this is hookey at best...AND if the code/forms are written poorly, you'll notice a performance hit...

For example, if they have a dozen drop downs on a form, and they all talk to the backend, it'll open a connection per...if they're upodateable, it'll be 2 per...

GOOD LUCK|||thanks Brett,
I'll give it go. our customer DB has about 6000 records, and i can't see re-typing all the time...|||Lettuce know how it turns out;-)

btw...6,000 isn't big...|||No,
6000 isn't very big. There is a large amount of admin related to this business, tho, and trying to keep the office staff updating the schedule software will never work. I'll let you no!|||Ok,
started on this, and I'm a little confused. In SQL the table is AR_CUST.
in Access the table is 'Customers'. Now, I need to rename the the Access table 'Customers' to something like 'Customersx' or 'XCustomers'; anything but 'Customers', right? Then in Access create a new Query using the AR_CUST table in SQL as the data source named 'Customers'. That is how far I've got. Now how do I map the query to work correctly? Do I need to map it to a new 'Customers' table? I can't seem to find an area in Access to define the mapping! Talk about feeling like a DooF!
Andy|||If you want the data stored in SQL Server to be the main repository for the data from the Access database there are a couple of ways to do this.

1. If the data structure of the Customers table is the same in both the current Access database and the SQL Server database then you can archive the current Access database. Create a new copy of the Access database and remove the Access Customers table, then create a linked table to your AR_CUST table in SQL Server and rename the linked table in Access to Customers

2. If the data structure of the Customers table in Access is different to the data structure in SQL Server then create a view in SQL Server renaming all the columns to the correct Access column names. Then create a linked table within Access to the view and rename the view to Customers.

Using the above methods means that you won't need to change any code or forms in Access, unless the Access database is using ADO to access a Customers table somewhere, but this seems unlikely as it appears that the Access database is a stand-alone database at the moment.

If you do use ADO you will either have to change the Access forms, queries and everything to use AR_CUST or change SQL Server to use a table called Customers.|||Originally posted by sjp
If you want the data stored in SQL Server to be the main repository for the data from the Access database there are a couple of ways to do this.


No. You don't want to store the data in Access.

1. You want the Query to be named AR_CUST
2. You want to pick the linked SQL Server Table Customer as the Datasource
3. You want to make sure you have every column that exists in the AR_CUST Table in the query, AND Named exactly as the way they are in AR_CUST
4. You need to establish a mapping for every column, like, for example the customers last name..

The SQL would look Like

If the Customer table has the field called LName and AR_CUST is defined as Last_Name

SELECT LName AS Last_Name FROM dbo_Customer

In the QBE Grid it would look like

Last_Name: LName

You need to make sure that you have every single column that AR_Cust has. If not you'll have problems...|||gotcha Brett,
I'll let you know how I make out...

Can this be done using TSQL ?!

Hello
im writing an inventory application for a customer that needs to calculate
item cost by Moving Average method which requires calculating the cost after
each operation, i have a good experience with TSQL but so far i failed to
write the statement that can do this WITHOUT writing cursors
im trying to avoid calculating cost after each transaction to the inventory
. and by writing a stored procedure , get a list showing transactions and
item avg in a period
here is a description of moving avg method , also available here for those
who cant read html
http://www.fms.indiana.edu/auxiliary/inventory.asp
Moving Average--Perpetual
Continuous or moving average assigns a unit value to cost of goods available
for sale. In this scenario, the average cost determines cost of goods sold
at the time of each sale. This method requires a calculation of average unit
cost after each purchase as illustrated below.
# of Units Cost per Unit Total Cost Moving Avg. Cost
Beginning inventory, 7/1 200
$5,000
$25.00
Purchase, 8/10 100
$26.00
2,600
Inv. Balance 300
7,600
25.33
Sale, 9/15 (100)
25.33
(2,533)
Inv. Balance 200
5,067
Purchase, 12/7 600
27.00
16,200
Inv. Balance 800
21,267
26.58
Sale, 12/18 (300)
26.58
(7,975)
Inv. Balance 500
13,292
Sale, 2/22 (250)
26.58
(6,645
Inv. Balance 250
6,647
Purchase, 3/20 300
28.00
8,400
Inv. Balance 550
15,047
27.36
Sale, 5/15 (150)
27.36
(4,104)
Inv. Balance 400
27.36
10,943
Ending Inventory 400
10,943
Cost of Goods Sold 100
2,533
300
7,975
250
6,645
150
4,104
800
$21,257
Regards
Bassamcan you post DDL and some data..and also the example..pasted correctly..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Bassam" <egbas@.yahoo.com> wrote in message
news:ORrEOZODFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Hello
> im writing an inventory application for a customer that needs to calculate
> item cost by Moving Average method which requires calculating the cost
after
> each operation, i have a good experience with TSQL but so far i failed to
> write the statement that can do this WITHOUT writing cursors
> im trying to avoid calculating cost after each transaction to the
inventory
> . and by writing a stored procedure , get a list showing transactions and
> item avg in a period
> here is a description of moving avg method , also available here for those
> who cant read html
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> Moving Average--Perpetual
> Continuous or moving average assigns a unit value to cost of goods
available
> for sale. In this scenario, the average cost determines cost of goods sold
> at the time of each sale. This method requires a calculation of average
unit
> cost after each purchase as illustrated below.
> # of Units Cost per Unit Total Cost Moving Avg. Cost
> Beginning inventory, 7/1 200
> $5,000
> $25.00
> Purchase, 8/10 100
> $26.00
> 2,600
>
> Inv. Balance 300
> 7,600
> 25.33
> Sale, 9/15 (100)
> 25.33
> (2,533)
>
> Inv. Balance 200
> 5,067
>
> Purchase, 12/7 600
> 27.00
> 16,200
>
> Inv. Balance 800
> 21,267
> 26.58
> Sale, 12/18 (300)
> 26.58
> (7,975)
>
> Inv. Balance 500
> 13,292
>
> Sale, 2/22 (250)
> 26.58
> (6,645
>
> Inv. Balance 250
> 6,647
>
> Purchase, 3/20 300
> 28.00
> 8,400
>
> Inv. Balance 550
> 15,047
> 27.36
> Sale, 5/15 (150)
> 27.36
> (4,104)
>
> Inv. Balance 400
> 27.36
> 10,943
>
> Ending Inventory 400
> 10,943
>
> Cost of Goods Sold 100
> 2,533
>
> 300
> 7,975
>
> 250
> 6,645
>
> 150
> 4,104
>
> 800
> $21,257
>
>
> Regards
> Bassam
>|||Please post DDL and some INSERT statements of your sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:

>im writing an inventory application for a customer that needs to calculate
>item cost by Moving Average method which requires calculating the cost afte
r
>each operation, i have a good experience with TSQL but so far i failed to
>write the statement that can do this WITHOUT writing cursors
Hi Bassam,
I think you can get the moving average by a simple self-join with group
by. Check the following example:
-- First, create a table to hold all transactions
-- Opening balance is considered a transaction in this simplified example
CREATE TABLE Operations
(OpDate smalldatetime not null primary key,
Amount int not null, -- >0 purchase <0 sale
UnitPrice money not null)
go
-- Insert all data (same as on web page you mentioned)
INSERT Operations (OpDate, Amount, UnitPrice)
SELECT '20040701', 200, 25
UNION ALL
SELECT '20040810', 100, 26
UNION ALL
SELECT '20040915', -100, 25.33
UNION ALL
SELECT '20041207', 600, 27
UNION ALL
SELECT '20041218', -300, 26.58
UNION ALL
SELECT '20050222', -250, 26.58
UNION ALL
SELECT '20050320', 300, 28
UNION ALL
SELECT '20050515', -150, 27.36
go
-- Here's the statement that will calculate amount, value and moving
-- average after each of the transaction.
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
go
-- Done. Now clean up the mess.
DROP TABLE Operations
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your input but result of your statement will calculate
"Weighted Average" not "Moving Average"
difference is shown in examples in this link
http://www.fms.indiana.edu/auxiliary/inventory.asp
if you open this page and search for weighted average you fill find the
example which works with your statement but the just below example which is
for moving average won't work
i will post DDL and some data here to clear the case
Regards
Bassam
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:
>
calculate
after
> Hi Bassam,
> I think you can get the moving average by a simple self-join with group
> by. Check the following example:
> -- First, create a table to hold all transactions
> -- Opening balance is considered a transaction in this simplified example
> CREATE TABLE Operations
> (OpDate smalldatetime not null primary key,
> Amount int not null, -- >0 purchase <0 sale
> UnitPrice money not null)
> go
> -- Insert all data (same as on web page you mentioned)
> INSERT Operations (OpDate, Amount, UnitPrice)
> SELECT '20040701', 200, 25
> UNION ALL
> SELECT '20040810', 100, 26
> UNION ALL
> SELECT '20040915', -100, 25.33
> UNION ALL
> SELECT '20041207', 600, 27
> UNION ALL
> SELECT '20041218', -300, 26.58
> UNION ALL
> SELECT '20050222', -250, 26.58
> UNION ALL
> SELECT '20050320', 300, 28
> UNION ALL
> SELECT '20050515', -150, 27.36
> go
> -- Here's the statement that will calculate amount, value and moving
> -- average after each of the transaction.
> SELECT a.OpDate AS InvDate,
> SUM(b.Amount) AS Amount,
> SUM(b.Amount * b.UnitPrice) AS Value,
> SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
> FROM Operations AS a
> INNER JOIN Operations AS b
> ON b.OpDate <= a.OpDate
> GROUP BY a.OpDate
> ORDER BY a.OpDate
> go
> -- Done. Now clean up the mess.
> DROP TABLE Operations
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 7 Feb 2005 14:32:20 +0200, Bassam wrote:

>Hello Hugo,
>Thank you for your input but result of your statement will calculate
>"Weighted Average" not "Moving Average"
>difference is shown in examples in this link
>http://www.fms.indiana.edu/auxiliary/inventory.asp
>if you open this page and search for weighted average you fill find the
>example which works with your statement but the just below example which is
>for moving average won't work
Hi Bassam,
I did check that page, and the results of my query were equal to the
moving average quoted on that page (the table directly after the heading
"Moving Average--Perpetual").

>i will post DDL and some data here to clear the case
Excellent idea!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
I tested your statement, great , it works to a great detail !! , i found a
problem in the moving avg in date 2/22/05 , it should be exactly as the one
done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
put 20 sales transactions and see the result
but your statement gave max accurate to date to table.do you know a way to
overcome this small shift ?
thank you and welcome to any comments
Bassam|||On Mon, 7 Feb 2005 15:24:41 +0200, Bassam wrote:

>Hello Hugo,
>I tested your statement, great , it works to a great detail !! , i found a
>problem in the moving avg in date 2/22/05 , it should be exactly as the one
>done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
>26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
>put 20 sales transactions and see the result
Hi Bassam,
I noted the difference as well. This is caused by rounding errors.
Consider the first few rows in the sample data. The beginning inventory
shows 200 units at a total cost of $ 5,000 - exactle $ 25.00 on average.
After the first purchase, there are 300 units on stock and the total cost
is equal to $ 7,600. The average price is $ 25.333333333333333 (etc), but
it is rounded down to $ 25.33. This would mean that if the following sale
would not be for 100 units (as listed in the example), but for 300 units,
the total sale price would be $ 7,599 and the remaining stock would be 0
units, for a total price of $ 1.
The example on the web page graciously avoids this anomaly by only
including a new price after each purchase. It doesn't list the moving avg
cost after a sale, so I could not verify if the values given by my query
are correct or not.
If you need the moving average cost to reflect the situation after the
last purchase instead of after the last sale, try this (slightly more
complicated) query:
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
(Note: if you only need the date and the moving average, not the amount
and value of the inventory, you can remove the group by and the join to
"Operations AS b" - IOW, you can simplify to:
SELECT a.OpDate AS InvDate,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
ORDER BY a.OpDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Bassam,
Thanks to Hugo for posting the DDL for the table, I will assume it is
correct. Here is a try, but do not compare to the table in the link, there i
s
an error there.
Error in the link:
Sale, 12/18 (300) 26.58 (7,975)
well, -300 * 26.58 should be 7,974.
select
a.OpDate,
sum(b.Amount) as number_of_units,
sum(b.Amount * b.UnitPrice) as total_cost,
(
select
sum(c.Amount * c.UnitPrice) / sum(c.Amount)
from
Operations as c
where
c.OpDate <= (
select
max(d.OpDate)
from
Operations as d
where
sign(d.Amount) >= 0 and d.OpDate <= a.OpDate
)
) as moving_avg_cost
from
Operations as a
inner join
Operations as b
on a.OpDate >= b.OpDate
group by
a.OpDate
order by
a.OpDate
go
AMB
"Bassam" wrote:

> Hello Hugo,
> Thank you for your input but result of your statement will calculate
> "Weighted Average" not "Moving Average"
> difference is shown in examples in this link
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> if you open this page and search for weighted average you fill find the
> example which works with your statement but the just below example which i
s
> for moving average won't work
> i will post DDL and some data here to clear the case
> Regards
> Bassam
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> calculate
> after
>
>|||Hello Hugo,
Thank you, Clear and efficient ! , only last problem , what if a user need
to delete a purchasing happened at beginning of month - or adjust its unit
price value, that means all next averages used in next sales is wrong and
need to be recalculated, is there a way to recalculate unit price for sales
transactions ' i need to adjust that before using your statement again or
result will be wrong
to make situation more complicated is that it might also be more purchasing
down there with sales, i mean suppose user deleted 1 of 5 purchasing done at
beginning of the month , on day 1 , while other purchasing happened on day 5
, 8 , 12 , 14 remains , and there are sales in between. , how then i can
recalculate unit price (which is the moving average) for sales
transactions.in between ?
Thank you
Bassam