November 7, 2003 at 9:59 am
I need help!
I have a table that has to be updated on a weekly basis. I only want to include the updates that represent changed information from the existing table, however the file I am sent sends everything that is current.
Here is the structure of the table:
CREATE TABLE [BUYER_LISTING] (
[UNQID] [int] IDENTITY (1, 1) NOT NULL ,
[FINELINE] [char] (4) NULL ,
[ORD_DEPT_NBR] [char] (2) NULL ,
[BUYER] [char] (7) NULL ,
[SUBCLASS] [char] (2) NULL ,
[BUYER_NAME] [varchar] (50) NULL ,
[TELEPHONE] [char] (16) NULL ,
[WEEK] [decimal](6, 0) NULL ,
[DEPT_DESC] [varchar] (35) NULL ,
[CAT_NBR] [char] (2) NULL ,
[MDSE_Category] [varchar] (35) NULL ,
[fineline_desc] [varchar] (40) NULL ,
CONSTRAINT [IX_BUYER_LISTING] UNIQUE CLUSTERED
(
[UNQID]
) WITH FILLFACTOR = 50 ON [AREF_DATA]
) ON [AREF_DATA]
If I have an entry in the table of:
Insert into buyer_listing(fineline, ord_dept_nbr, buyer, subclass, week, cat_nbr)
Values(‘0001’,'62',’jdoe’,'12',200313, '14')
And the new update includes this info, I do not want to insert the new info:
Insert into buyer_listing(fineline, ord_dept_nbr, buyer, subclass, week, cat_nbr)
Values(‘0001’,'62',’jdoe’,'12',200314, '14')
However, if the new update has a different Buyer then I want the record included:
Insert into buyer_listing(fineline, ord_dept_nbr, buyer, subclass, week, cat_nbr)
Values(‘0001’,'62',’jsmith’,'12',200314, '14')
If in week 200310 the buyer was jjones and in week 200311 the buyer changed to jdoe and in week 200312 the buyer changed back to jjones, I want to see this history in the table.
Here is what I have tried:
select fineline, ord_dept_nbr, subclass, cat_nbr, buyer, max(week) as week
into ##tempbuyer
from dbo.buyer_listing
group by fineline, ord_dept_nbr, subclass, cat_nbr, buyer
go
select a.buyer, a.ord_dept_nbr, a.subclass,
a.fineline, a.cat_nbr, a.week
into ##buyer_new
from dbo.buyer a –table of new update
join ##tempbuyer b on
a.fineline = b.fineline and
a.ord_dept_nbr = b.ord_dept_nbr and
a.subclass = b.subclass and
a.cat_nbr = b.cat_nbr
where a.buyer <> b.buyer
go
The problem with my logic is that in my table of the max(week) I end up with several entries for the fineline, ord_dept_nbr, subclass, week, cat_nbr combination if the buyer has changed. So then the new record is inserted even if in week 200312 the buyer is the same as week 200311.
Anybody have ideas on how to fix this?
TIA!
Michelle Morris
Michelle
November 7, 2003 at 6:37 pm
So, basically you need to update if (a) it is new, or (b) it is changed since last week, yes?
First, we need to get some code to identify the last week. You could build a table with the weeks, and each time a new week comes in, add it to the end, or you can try and calculate it.
To use the table, I would have something like:
Create Table Weeks (id Int identity(1,1), Week Decimal(6,0))
Create Index ix_Weeks ON Weeks (Week, id)
To use it, you would include it in your query something like:
select a.buyer, a.ord_dept_nbr, a.subclass,
a.fineline, a.cat_nbr, a.week
into ##buyer_new
from dbo.buyer a –table of new update
Inner join ##tempbuyer b on
a.fineline = b.fineline and
a.ord_dept_nbr = b.ord_dept_nbr and
a.subclass = b.subclass and
a.cat_nbr = b.cat_nbr
INNER JOIN (Select Top 1 * From Weeks w Where w.Week < a.Week Order By w.Week Desc, w.ID Desc) WW On b.Week = WW.week
where a.buyer <> b.buyer
November 10, 2003 at 6:55 am
I guess going along with this Topic, does anyone have a Design for a History table that would encompass any Table (all my tables have 1 Pri-Key)? I guess I would be looking to store:
1.) Who (UserID)
2.) What (Type of Update: Update, Insert, Delete)
3.) Where (Which Table / Key)
4.) When (Date)
5.) and Why? I guess because...
November 10, 2003 at 7:05 pm
Hi Michelle,
All tables that I design have the following columns:
DateCreated
UserCreated
DateModified
UserModified.
where the first two are not null and last two can have nulls.
The software I develop updates these columns with the relevant data (you could also set up triggers to carry out the updates).
This way, I know who created the data and who modified it last. I do not keep a "running log" as the rate of mods to data would mean a fairly large table.
If you have another table where you store the date you last updated the History table, you could then use this against the columns above to check if the History table needs updating.
Gabriel.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply