SQL Server Stamping date Trail

I dunno how you want to do this, but what I would do is:

modify table itran to add a column first_it_due  
and create a second table (if you need to have all the change history)
CREATE TABLE itran_history (
it_doc ???int? varchar?,
it_due datetime not null,
changeDate datetime not null DEFAULT (getdate())
)
(I assume it_doc is the primary key)

but how are the changes made? do you have control over what makes the changes?
if not, you can still manage using triggers on the itran table (if that's in SQL Server)

i import into sql from foxpro every 20 mins, so i would need the initial value from the first import, and then any changes made after that, a change maybe made 3 weeks from the initial date or not at all, but i would need all changes logged.

is what you have suggested the only way to do this.?

John

There's rarely only one way to do thing...

How is the import from foxpro done? do you control that part? How is it done exactly?
How does it decide to do INSERT or UPDATE?

I just use the SQL server import export wizard, the tables get droped and then the data gets inserted, I have the ability to edit mappings etc.

John

Table gets dropped? then you loose all previous data...
so that means we have to keep the all the it_due date somewhere else...

Could you give the CREATE table statement, so we ca see the table structure, datattype/primary key...

That way I'll be able to give you a correct create statement for a new table itran_history
and also create the trigger for the itran table so that any change to it_due is logged into itran_history...

here is the create statememt

CREATE TABLE [dbo].[itran] (
[it_doc] char(10) NOT NULL,
[it_stock] char(16) NOT NULL,
[it_desc] char(40) NOT NULL,
[it_anal] char(8) NOT NULL,
[it_quan] decimal(9,0) NOT NULL,
[it_price] decimal(10,0) NOT NULL,
[it_disc] decimal(5,2) NOT NULL,
[it_vat] char(1) NOT NULL,
[it_lineval] decimal(10,0) NOT NULL,
[it_sdisc] bit NOT NULL,
[it_odisc] bit NOT NULL,
[it_status] char(1) NOT NULL,
[it_date] date NOT NULL,
[it_recno] decimal(7,0) NOT NULL,
[it_exvat] decimal(10,0) NOT NULL,
[it_discval] decimal(10,0) NOT NULL,
[it_sett1] decimal(10,0) NOT NULL,
[it_sett2] decimal(10,0) NOT NULL,
[it_overall] decimal(10,0) NOT NULL,
[it_vatval] decimal(10,0) NOT NULL,
[it_vatpct] decimal(4,2) NOT NULL,
[it_memo] text NOT NULL,
[it_cost] decimal(10,0) NOT NULL,
[it_priorty] decimal(1,0) NOT NULL,
[it_due] date NOT NULL,
[it_qtyallc] decimal(9,0) NOT NULL,
[it_dteallc] date NOT NULL,
[it_qtypick] decimal(9,0) NOT NULL,
[it_dtepick] date NOT NULL,
[it_qtydelv] decimal(9,0) NOT NULL,
[it_dtedelv] date NOT NULL,
[it_numdelv] char(10) NOT NULL,
[it_qtyinv] decimal(9,0) NOT NULL,
[it_dteinv] date NOT NULL,
[it_numinv] char(10) NOT NULL,
[it_exdate] date NOT NULL,
[it_exref] char(20) NOT NULL,
[it_fcurr] char(3) NOT NULL,
[it_fcrate] decimal(10,6) NOT NULL,
[it_fcdec] decimal(1,0) NOT NULL,
[it_mark] char(1) NOT NULL,
[it_delt] char(3) NOT NULL,
[it_ntrn] char(2) NOT NULL,
[it_mtrn] char(1) NOT NULL,
[it_comcode] char(11) NOT NULL,
[it_supunit] decimal(7,3) NOT NULL,
[it_cntorig] char(2) NOT NULL,
[it_ntmass] decimal(7,3) NOT NULL,
[it_vattyp] char(1) NOT NULL,
[it_fcmult] bit NOT NULL,
[it_jcstdoc] char(10) NOT NULL,
[it_jphase] char(3) NOT NULL,
[it_jccode] char(16) NOT NULL,
[it_jline] char(10) NOT NULL,
[it_btchser] text NOT NULL,
[it_fromjc] bit NOT NULL,
[it_qtyover] decimal(9,0) NOT NULL,
[it_qtyorig] decimal(9,0) NOT NULL,
[it_massut] bit NOT NULL,
[it_cwcode] char(4) NOT NULL,
[it_bscost] decimal(12,2) NOT NULL,
[it_worder] bit NOT NULL,
[it_woref] char(10) NOT NULL,
[it_porder] bit NOT NULL,
[it_poref] char(10) NOT NULL,
[it_poline] decimal(7,0) NOT NULL,
[it_uorder] char(10) NOT NULL,
[it_uline] decimal(7,0) NOT NULL,
[it_pacc] char(8) NOT NULL,
[it_pprod] char(16) NOT NULL,
[it_pprice] decimal(12,0) NOT NULL,
[it_pfcurr] char(3) NOT NULL,
[it_pfcrate] decimal(10,6) NOT NULL,
[it_pdisc] decimal(9,2) NOT NULL,
[it_pqty] decimal(9,0) NOT NULL,
[it_ponow] bit NOT NULL,
[it_pochg] bit NOT NULL,
[it_wochg] bit NOT NULL,
[it_lineno] decimal(5,0) NOT NULL,
[sq_amtime] char(8) NOT NULL,
[sq_amdate] date NOT NULL,
[sq_amuser] char(10) NOT NULL,
[it_fundec] decimal(1,0) NOT NULL,
[it_pfundec] decimal(1,0) NOT NULL,
[it_delad] char(40) NOT NULL,
[it_delad1] char(30) NOT NULL,
[it_delad2] char(30) NOT NULL,
[it_delad3] char(30) NOT NULL,
[it_delad4] char(30) NOT NULL,
[it_deladpc] char(8) NOT NULL,
[it_narr1] char(75) NOT NULL,
[it_narr2] char(75) NOT NULL,
[it_pjcode] char(16) NOT NULL,
[it_pjline] char(10) NOT NULL,
[it_wonow] bit NOT NULL,
[it_delware] char(4) NOT NULL,
[id] decimal(10,0) NOT NULL,
[it_jlineid] decimal(10,0) NOT NULL,
[it_plineid] decimal(10,0) NOT NULL,
[it_jwiprid] decimal(10,0) NOT NULL,
[it_project] char(8) NOT NULL,
[it_job] char(8) NOT NULL,
[it_uqalloc] char(11) NOT NULL,
[it_multsu] bit NOT NULL,
[it_rcvat] decimal(11,2) NOT NULL,
[it_activid] int NOT NULL

the primary key is it_doc if that helps

john

ok, so here's SQL code to:
1. create a table to keep the history of it_due
simple table with it_doc, it_due, a mod_date column to know when the change occured, and a column is_first to make it easier to find the first it_due date for any it_doc.

2. create a trigger to fill the history table when you insert
The idea here is to insert all combination of it_doc & it_due that are not already present in itran_history, and at the same time, if it_doc is not already present in itran_history, it will set is_first to 1

CREATE TABLE dbo.itran_history (
[it_doc] char(10) NOT NULL,
[it_due] date NOT NULL,
[mod_date] date NOT NULL DEFAULT(getdate()),
[is_first] tinyint NOT NULL DEFAULT(0)
)
GO

CREATE  INDEX [ix_itran_history_it_doc] ON [dbo].[itran_history]([it_doc]) ON [PRIMARY]
GO

CREATE TRIGGER itran_OnInsert
ON dbo.itran
FOR INSERT
AS

SET NOCOUNT ON

INSERT INTO itran_history (it_doc, it_due, is_first)
SELECT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM INSERTED I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

SET NOCOUNT OFF

GO

Select allOpen in new window

Then if you want to have the first it_due date:

SELECT I.*, H.it_due as first_it_due
FROM dbo.itran I
LEFT JOIN dbo.itran_history H ON I.it_doc=H.it_doc AND H.is_first=1

This is great stalhw I will give thisna go tomorrow when I get back to work.

This may seem like a silly question but since I am new to this and only know how to create tables using import export wizard, do I place this code simply in an ssis package and run it just the once?

John

You have acces to sql server management?
From there you should be able to open a 'sql query window', paste it there and run it...

Hi stalhw

i may not have explained what i need clearly rather than stamping the date when it_due was changed
i need a record of all the dates that have been put into it_due

is that different to what you thought?

john

You get both with what I was doing...
The table history gives you the different values of it_due, and the date they were changed (so that they can be put in the order they were chnaged...)

Ok so i have created the table using the code that you gave pasted into a query and ran it.

is there anything else that i need to do? with the code that you have given only when there is a change would i expect to see results?

Thanks for all your help on this

john

So you created the table and the trigger?
If so try importing your stuff from foxpro and see if data appears in the new table.

If it doesn't it either means the trigger wasn't created properly, or the import/export wizard is not only emptying the table, but completely destroying it and recreating it, if that's the case there won't be much we can do...

So make sure you didn't check, the 'Drop and re-create destination table' in your import package.

Ok so i ran the code again to create the table etc then reimported foxpro data but nothing has been populated in the itran_history table.

i have a package that drops all the tables in sql and then recreates them. on importing from Foxpro.

are you saying to delete the entry of drop table in itran.

am i right in saying the newly created itran_history table gets created from the code you gave me and just sits there this does not have to be added to an import package etc.

john

, you are correct, the itran_history table needs to stay there, but the problem is that when your import package drops the itran table, it also drops the trigger....

So your import package needs to be changed... or we'll need to convert the trigger into a stored procedure that you will need to run after the import... (or we schedule it to run every x minutes)

stalhw

i have just realized whilst messing with this that the it_doc is not the primary key for that table the correct primary key is "id" so the full synatx is dbo.itran.id.

can you help with the stored procedure or the schedule?

john

is id an autoincrement field? if so, since you drop and re-create the table, that key is useless since it will change everytime the table is dropped and recreated...
So we'll keep usint it_doc and I assume this unequely identifies the document.

As for a stored procedure, here it is (not much change to make)

CREATE PROCEDURE dbo.add_itran_history
AS

INSERT INTO itran_history (it_doc, it_due, is_first)
SELECT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

GO

Select allOpen in new window

then you'll just need to add a Job in SQL Server Agent, and put the schedule you want... if you import every 20min, set it to run every 20min...
And if you're import thing is done with a SSIS Package you probably could add the command "EXEC add_itran_history" to it somehow..

Oh, and if you want to use id instead of it_doc, just change the definition of the table history, and replace it_doc by [id]

there are many it_docs with the same name ie DOC12345 each doc represents a line item of an order.

so if one order had 10 line items then there would be ten it_docs with the same name

is this a problem?

well question is, is there more than one it_due date per it_doc?
if not, then it_doc is good for what you need, it's actually better since it means less data in history table.

but then we need a small modification to the procedure to do a SELECT DISTINCT:

CREATE PROCEDURE dbo.add_itran_history
AS

INSERT INTO itran_history (it_doc, it_due, is_first)
SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

GO

Select allOpen in new window

ok i have run the procedure yet there is still nothing in my itran_history table

any thoughts

john

any errors? maybe the user you are using doesn't have rights on that procedure or table...

In a query window if you run:

SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null


do you get any results?

if i run that query i get the expected results

ok then when you try to run the stored procedure:
EXEC add_itran_history
what error message are you getting? must be a permission issue.

anyway, in the server agent Job, you can always put directly the code instead of using the procedure...

INSERT INTO itran_history (it_doc, it_due, is_first)
SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

ok that seems to have populated the table

so just to refresh my memory i have

it_doc,it_due,mod_date,is_first so what would i expect to see when a record is changed

When it_due changes, you get a new row in it_tran_history.

So if you want to see all changes made to it_due for a specific document, just
SELECT * FROM it_tran_history WHERE it_doc='DOC12345' ORDER BY mod_date

i think im going to need to go through this again i changed some data on my foxpro tables to reflect what we are doing and read them back into sql ran the procedure to expect to see an affected row but my change does not show. ill get back to you on that.

on something different in the query you asked me to run

SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

can you explain the syntax in particular this part

SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I

im not understanding the hd.it_doc or the l.it_due

John

Ok,
Let's say we want to add to the history table all the pairs of it_doc and it_due that are not already there (meaning it changed or it's a new it_doc)
then we do:
SELECT DISTINCT I.it_doc, I.it_due
FROM itran I
LEFT JOIN itran_history H ON I.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

That gives us every it_doc, it_due pair that doesn't currently exist in itran_history.

now, we also want to flag the it_due date of every new it_doc added to the history table, so I added a left join on a subquery listing all it_doc currently in the history table...
 ( SELECT DISTINCT it_doc FROM itran_history )

That combined gives the whole query, the DISTINCT is there to eliminate duplicates, and the
CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
is simply a check, if it can't find it_doc already in the history table, HD.it_doc will be null, so we set is_first to 1

what kind of result do you want exactly?

hi stalhw

i think it sort of works i might need to make a few adjustments with your help. One thing that i do not understand is if i create a new vue and select the table itran and just select it_doc it returns 149375
rows.

if i do the same for itran_history it only returns 50503 rows is this right there seems to be alot missing am i right in saying they should match?

No, it should match if you do:
SELECT DISTINCT it_doc FROM itran
Because like you said there's multiple rows in itran for each it_doc.

hi stalhw

ok i think i have it all working now really appreciate your efforts on this, could you just explain to me.

The trigger that we created was this just a 1 time thing to run to populate the table?,

also where are the triggers stored as i cannot find it anywhere

john

Also

i found that if the date gets changed in the same day then it will not add a row to the itran history table, is there anyway to get around this?

You can forget the trigger, we replace it with the job:
INSERT INTO itran_history (it_doc, it_due, is_first)
SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT DISTINCT it_doc FROM itran_history ) HD ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H ON HD.it_doc=H.it_doc AND I.it_due=H.it_due
WHERE H.it_doc is null

Because since your import thing DROPs the Table and recreates it, the trigger is lost, so we had to find another way.

As for "changed in the same day" thing, as long as you run the "INSERT INTO itran_history" code each time you do an import it should work, so make sure your schedule for the stored procedure is the same as your schedule to do imports.

Ok i get it now

with regarto the "changed in the same day" this does not work or if any date in itran_history has the date already then it does not append the same date, so it works fine if new dates are selected.

but if itran_history had a these dates

2011-10-10
2011-10-09

and i wanted to change it back to 2011-10-10 i would still only get 2 rows instead of 3.

also i did what you said earlier in the post and changed the primary key from it_doc to it.id

John

change the primary key of what?
no need, to change anything...

For the "same day", ok, i understand what you meant, if it goes back to a previous due date it's not added, we can correct that...

INSERT INTO itran_history (it_doc, it_due, is_first)
SELECT DISTINCT I.it_doc, I.it_due, CASE WHEN HD.it_doc is null THEN 1 ELSE 0 END
FROM itran I
LEFT JOIN ( SELECT it_doc, max(mod_date) as mod_date FROM itran_history GROUP BY it_doc) HD 
ON I.it_doc=HD.it_doc
LEFT JOIN itran_history H 
ON HD.it_doc=H.it_doc AND HD.mod_date=H.mod_date AND I.it_due=H.it_due
WHERE H.it_doc is null

Select allOpen in new window

Now it will work, what I did was modify the join on a subquery to also give us the latest date it_due was modified, and when it checks to see if the pair it_doc,it_due already exists in itran_history it will only check the last modification for each it_doc. So problem solved.

Hi  stalhw

when i said about changing the primary key i was reffering to your earlier post where you said

"if you want to use id instead of it_doc, just change the definition of the table history, and replace it_doc by [id]"

so that is what i am doing, all works as expected but the code you posted yesterday to allow for changes on the same day is not working, If you try to add add a date that is currently in itran_history it will not add a row to the table.

if we can crack that it will be 100% perfect.

appreciate your work

john

stalhw

 You are a Legend cant thank you enough for your help on this, i have learnt alot also.

Cheers

John

My pleasure :)

Hi stalhw

not sure if you want me to post this as a new question but i have aslight issue with the code that you might be able to help with.

If the sales team add a new order onto the system they might not straight away add something in it_due, they may add it later, by leaving it blank, When it is imported SQL recognises a blank date as
1899-12-30 and of course it will stamp the is_first date with '1' which is wrong.

is it possible to ignore impoted dates "1899-12-30" and not stamp them

John

sure it is possible...
simply modify the WHERE clause like:

WHERE H.it_doc is null AND I.it_due<>'1899-12-30'

Thanks again stalhw

have a good weekend.

john