Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Tracking Sales Changes Over Time

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-05-15 : 12:41:28
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.

SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table

My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.

This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:

1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.

I realize this task is difficult, but am I at least starting in the right direction?

Experts Only Please. (jk)

jdaman
Constraint Violating Yak Guru

354 Posts
Posted - 2008-05-15 : 12:48:14
My initial thought would be to add effective dates to the existing tables if they arent already present and insert a new line with a new effective date when a change is made rather than overwrite the data with an update. This way you would be able to track changes by comparing the effective dates.

Could you provide some sample data and your table schema for the three tables you mentioned?
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2008-05-15 : 13:22:25
If you don't have a date column in your source tables you could write a trigger that copies the changes to a new table that has a data column. You might even want "changed from" and "changed to" Columns.

Then you could create some views to go against your new tables and your old tables to present the data in the way that management wants to see it.




An infinite universe is the ultimate cartesian product.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-05-15 : 13:29:05
quote:
Originally posted by DavidChel

My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.

SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table

My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.

This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:

1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.

I realize this task is difficult, but am I at least starting in the right direction?

Experts Only Please. (jk)

You could do this with an audit column in your tables. If you have column like datemodified you just need to extract those records from each table having a date greater than current day start (12 AM). so what ever records that were modified/created during the day will be picked up. Now compare those picked up records with that in your destination table (first time destination table will not have anything) and three cases happens

1.records picked up from source & not existing in destination. These are newly created ones so insert them
2.records that exist both in picked up set and also in destination. These are modified ones so update values onto destination.

In addition to this, if you have physical deletes happening on any of your source tables, you can have a deleted table for them just to keep track of ids that were deleted along with date deleted by means of a delete trigger. Each day you pick up deleted records with date as current day and delete them from your destination table.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-05-15 : 15:15:48
Thank you for the suggestions. I have reservations about making changes to the erp database as we regularly endure software upgrades which sometimes include changes to the database. I don't want to add a column to one of more of the tables only to find that they've been removed by the automated upgrade program.

I thought about the trigger idea but I am concerned that the trigger will slow performance if it runs every time a sales order is added or changed. Am I worrying about nothing?

This was why I thought about a nightly process which occurs when the database is normally idle and placing the sales order changes in a totally separate set of tables.

Those three tables have many colums, I'm wondering if detailing them would detract from the thread at this point since I'm only looking for general direction rather than explicit instructions.

However, I appreciate all the help I can get. Any other ideas?
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2008-05-15 : 15:26:12
What about a saved table version of your data with a date stamp in it. One table as opposed to 2 (yesterday's and today's data together). I personally would just continue adding to the new table with the date field, as long as disk space isn't an issue, as the powers that be will surely change their current request. Think of it as a history table until you can get a new erp system (or rewrite the current one). You can then either accept two date parameters into a stored procedure to produce the differences (say over a month or something) or hard code the dates in with getdate and getdate -1 (syntax in BOL!) if that will always be your requirement. Also, if you post your table layouts, we may be able to devise something better for you, especially if date columns already exist.

Terry

Edited - I hate typos!
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2008-05-16 : 03:06:33
Not exactly the same problem but this thread might give you some ideas: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

--
Lumbago
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts
Posted - 2008-05-16 : 12:40:59
quote:
I thought about the trigger idea but I am concerned that the trigger will slow performance if it runs every time a sales order is added or changed. Am I worrying about nothing?


It depends.

How big is the table? How many inserts/updates happen during peak periods? How long do these periods last? What kind of hardware is your database running on? Are there other databases on the same Server? How many clients are updating at once?



An infinite universe is the ultimate cartesian product.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-07-18 : 10:08:17
I've started investigating this again and I am of course taking it in steps. Please keep in mind I'm doing this on a test server and not my live server as I know that would be potentially dangerous.

I simply wanted to create a Sales Modifications Database and inside have copies of these tables:

SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table

I started doing so by going into my main company and right clicking on the SOMAST table and Script Object to Clipboard as Create. I then pasted this script into the query analyzer on my new table and ran it. It created a blank table with all of these fields and such.

However, when I try to do so on SOITEM I get the following script: 


CREATE TABLE [soitem] (
	[finumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fpartno] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fpartrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fsono] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fclotext] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fllotreqd] [bit] NOT NULL ,
	[fautocreat] [bit] NOT NULL ,
	[fcas_bom] [bit] NOT NULL ,
	[fcas_rtg] [bit] NOT NULL ,
	[fcommpct] [numeric](8, 2) NOT NULL ,
	[fcustpart] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fcustptrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fdet_bom] [bit] NOT NULL ,
	[fdet_rtg] [bit] NOT NULL ,
	[fduedate] [datetime] NOT NULL ,
	[fenumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ffixact] [numeric](17, 5) NOT NULL ,
	[fgroup] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[flabact] [numeric](17, 5) NOT NULL ,
	[fmatlact] [numeric](17, 5) NOT NULL ,
	[fmeasure] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fmultiple] [bit] NOT NULL ,
	[fnextinum] [numeric](4, 0) NOT NULL ,
	[fnextrel] [numeric](3, 0) NOT NULL ,
	[fnunder] [numeric](12, 5) NOT NULL ,
	[fnover] [numeric](12, 5) NOT NULL ,
	[fordertype] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fothract] [numeric](17, 5) NOT NULL ,
	[fovhdact] [numeric](17, 5) NOT NULL ,
	[fprice] [bit] NOT NULL ,
	[fprintmemo] [bit] NOT NULL ,
	[fprodcl] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fquantity] [numeric](17, 5) NOT NULL ,
	[fcfromtype] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fcfromno] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fcfromitem] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fquoteqty] [numeric](15, 5) NOT NULL ,
	[frtgsetupa] [numeric](17, 5) NOT NULL ,
	[fschecode] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fshipitem] [bit] NOT NULL ,
	[fsoldby] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fsource] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fstandpart] [bit] NOT NULL ,
	[fsubact] [numeric](17, 5) NOT NULL ,
	[fsummary] [bit] NOT NULL ,
	[ftaxcode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ftaxrate] [numeric](7, 3) NOT NULL ,
	[ftoolact] [numeric](17, 5) NOT NULL ,
	[ftnumoper] [numeric](6, 0) NOT NULL ,
	[ftotnonpr] [numeric](4, 0) NOT NULL ,
	[ftotptime] [numeric](15, 5) NOT NULL ,
	[ftotstime] [numeric](15, 5) NOT NULL ,
	[fulabcost1] [numeric](7, 2) NOT NULL ,
	[fviewprice] [bit] NOT NULL ,
	[fcprodid] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fschedtype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[timestamp_column] [timestamp] NULL ,
	[identity_column] [int] IDENTITY (1, 1) NOT NULL ,
	[fdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__soitem__fdesc_Te__116A8EFB] DEFAULT (' '),
	[fdescmemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__soitem__fdescmem__125EB334] DEFAULT (' '),
	[fac] [M2MFacility] NOT NULL ,
	[sfac] [M2MFacility] NOT NULL ,
	[ITCCOST] [M2MGenericCostPrice] NOT NULL ,
	[FcAltUM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__SOItem__FcAltUM__6F412AD7] DEFAULT (''),
	[FnAltQty] [numeric](17, 5) NOT NULL CONSTRAINT [DF__SOItem__FnAltQty__70354F10] DEFAULT (0),
	[fcudrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__soitem__fcudrev__52C4E7BA] DEFAULT (' '),
	[fndbrmod] [numeric](1, 0) NOT NULL CONSTRAINT [DF__soitem__fndbrmod__53B90BF3] DEFAULT (0),
	[fnlatefact] [numeric](4, 2) NOT NULL ,
	[fnsobuf] [numeric](2, 0) NOT NULL ,
	[ManualPlan] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


I get the following error: 

Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #61: Cannot find data type M2MFacility.


Is this a custom field type?
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2008-07-18 : 10:16:31
It appears it's a user defined data type. In Enterprise Manager (assuming S2K), expand the database and the look in user defined data types to see if it's been defined as one or select * from systypes. Again, assuming S2K as I don't have S2K5 here. If you find it, replicate it to your test environment.

edit - DOH! I'm stating "assuming S2K" and it's stated very clearly in your opening post that it is INDEED S2K. Need to read a little more closely.

Terry
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-18 : 10:17:43
quote:
Originally posted by DavidChel

I've started investigating this again and I am of course taking it in steps. Please keep in mind I'm doing this on a test server and not my live server as I know that would be potentially dangerous.

I simply wanted to create a Sales Modifications Database and inside have copies of these tables:

SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table

I started doing so by going into my main company and right clicking on the SOMAST table and Script Object to Clipboard as Create. I then pasted this script into the query analyzer on my new table and ran it. It created a blank table with all of these fields and such.

However, when I try to do so on SOITEM I get the following script: 


CREATE TABLE [soitem] (
	[finumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fpartno] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fpartrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fsono] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fclotext] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fllotreqd] [bit] NOT NULL ,
	[fautocreat] [bit] NOT NULL ,
	[fcas_bom] [bit] NOT NULL ,
	[fcas_rtg] [bit] NOT NULL ,
	[fcommpct] [numeric](8, 2) NOT NULL ,
	[fcustpart] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fcustptrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fdet_bom] [bit] NOT NULL ,
	[fdet_rtg] [bit] NOT NULL ,
	[fduedate] [datetime] NOT NULL ,
	[fenumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ffixact] [numeric](17, 5) NOT NULL ,
	[fgroup] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[flabact] [numeric](17, 5) NOT NULL ,
	[fmatlact] [numeric](17, 5) NOT NULL ,
	[fmeasure] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fmultiple] [bit] NOT NULL ,
	[fnextinum] [numeric](4, 0) NOT NULL ,
	[fnextrel] [numeric](3, 0) NOT NULL ,
	[fnunder] [numeric](12, 5) NOT NULL ,
	[fnover] [numeric](12, 5) NOT NULL ,
	[fordertype] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fothract] [numeric](17, 5) NOT NULL ,
	[fovhdact] [numeric](17, 5) NOT NULL ,
	[fprice] [bit] NOT NULL ,
	[fprintmemo] [bit] NOT NULL ,
	[fprodcl] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fquantity] [numeric](17, 5) NOT NULL ,
	[fcfromtype] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fcfromno] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fcfromitem] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fquoteqty] [numeric](15, 5) NOT NULL ,
	[frtgsetupa] [numeric](17, 5) NOT NULL ,
	[fschecode] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fshipitem] [bit] NOT NULL ,
	[fsoldby] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fsource] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fstandpart] [bit] NOT NULL ,
	[fsubact] [numeric](17, 5) NOT NULL ,
	[fsummary] [bit] NOT NULL ,
	[ftaxcode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ftaxrate] [numeric](7, 3) NOT NULL ,
	[ftoolact] [numeric](17, 5) NOT NULL ,
	[ftnumoper] [numeric](6, 0) NOT NULL ,
	[ftotnonpr] [numeric](4, 0) NOT NULL ,
	[ftotptime] [numeric](15, 5) NOT NULL ,
	[ftotstime] [numeric](15, 5) NOT NULL ,
	[fulabcost1] [numeric](7, 2) NOT NULL ,
	[fviewprice] [bit] NOT NULL ,
	[fcprodid] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fschedtype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[timestamp_column] [timestamp] NULL ,
	[identity_column] [int] IDENTITY (1, 1) NOT NULL ,
	[fdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__soitem__fdesc_Te__116A8EFB] DEFAULT (' '),
	[fdescmemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__soitem__fdescmem__125EB334] DEFAULT (' '),
	[fac] [M2MFacility] NOT NULL ,
	[sfac] [M2MFacility] NOT NULL ,
	[ITCCOST] [M2MGenericCostPrice] NOT NULL ,
	[FcAltUM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__SOItem__FcAltUM__6F412AD7] DEFAULT (''),
	[FnAltQty] [numeric](17, 5) NOT NULL CONSTRAINT [DF__SOItem__FnAltQty__70354F10] DEFAULT (0),
	[fcudrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__soitem__fcudrev__52C4E7BA] DEFAULT (' '),
	[fndbrmod] [numeric](1, 0) NOT NULL CONSTRAINT [DF__soitem__fndbrmod__53B90BF3] DEFAULT (0),
	[fnlatefact] [numeric](4, 2) NOT NULL ,
	[fnsobuf] [numeric](2, 0) NOT NULL ,
	[ManualPlan] [bit] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


I get the following error: 

Server: Msg 2715, Level 16, State 7, Line 1
Column or parameter #61: Cannot find data type M2MFacility.


Is this a custom field type?

M2MFacility is a user defined datatype defined in live db. You need to script out its definition and create it in your test server before creating tables having columns referring it. so first apply script of user defined data type on test db.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-07-18 : 10:23:25
Thanks Visakh.

Umm... stupid question....

I've found it under user defined datatypes, but how do I script out it's definition? All the script options are greyed out.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-07-21 : 09:52:09
Anybody? I could really use some help here.
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2008-07-21 : 10:42:05
You can right-click, via EM, select "all tasks" and then generate the script.

Terry
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-07-21 : 17:21:18
I appreciate the help. However, when I try that I get a screen with options. I hit OK and get the following: 

if exists (select * from dbo.systypes where name = N'M2MFacility')
exec sp_droptype N'M2MFacility'
GO

setuser
GO

EXEC sp_addtype N'M2MFacility', N'char (20)', N'not null'
GO

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[UW_StringDefault]', N'[M2MFacility]'
GO

setuser
GO


What am I looking at here?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-07-22 : 14:22:10
Anybody?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-07-22 : 14:22:54
Anybody?
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2008-07-22 : 15:37:16
The setuser is not necessary, at least from what I read in BOL. I ran the following:

EXEC sp_addtype N'M2MFacility', N'char (20)', N'not null' -- created the user-defined type

EXEC sp_bindefault N'[dbo].[UW_StringDefault]', N'[M2MFacility]' -- errored out

Look at sp_bindefault in BOL. It appears you are binding a default ([UW_StringDefault]) to your UDDF. Using EM, look in the defaults folder within your production database, script it out and create it in your dev environment. Then rerun your sp_bindefault script.

Terry
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site