September 16, 2009 at 8:18 am
Me again 🙂
almost getting there... now I have a problem where my query is returning incorrectly old and new values.... code is shown below
--// lets create the source data
create table tblSource
(
ridint identity(1,1),
adtAction varchar(3),
transaction_id varchar(255),
Barcodeint,
statusint,
datestamp datetime
)
--// now lets mimic what the application is putting into the table
Declare @dtdatetime
Declare @guid uniqueidentifier
Declare @TransKey varchar(255)
Set @dt = getdate()
Set @guid= NEWID()
Set @TransKey = CONVERT(varchar(255), @guid)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('Old',@TransKey,111000,120,@dt)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('New',@TransKey,111000,120,@dt)
Set @dt = getdate()
Set @guid= NEWID()
Set @TransKey = CONVERT(varchar(255), @guid)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('Old',@TransKey,111000,120,@dt)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('New',@TransKey,111000,140,@dt)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('Old',@TransKey,222000,120,@dt)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('New',@TransKey,222000,140,@dt)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('Old',@TransKey,333000,120,@dt)
Insert into tblSource
(adtAction, transaction_id, barcode, status, datestamp)
values
('New',@TransKey,333000,140,@dt)
Select * from tblSource
Select oldv.transaction_id, oldv.fieldName, oldv.oldvalue, newv.newvalue, oldv.datestamp
From
(
Select Transaction_id,fieldName, oldvalue, datestamp
from (
Select
Transaction_id
, datestamp
, cast(barcode as SQL_VARIANT) as barcode
, cast(status as SQL_VARIANT) as status
from tblSource
Where CharIndex('Old',adtAction) > 0
and transaction_id is not null) as aam_src
unpivot
(oldvalue For fieldName in (barcode, status)
) as unpvt_aam
) as oldv
Inner Join
(
Select Transaction_id, fieldName, newvalue, datestamp
from (
Select
Transaction_id
, datestamp
, cast(barcode as SQL_VARIANT) as barcode
, cast(status as SQL_VARIANT) as status
from tblSource
Where CharIndex('New',adtAction) > 0
and transaction_id is not null) as aam_src
unpivot
(newvalue For fieldName in (barcode, status)
) as unpvt_aam
) as newv
on newv.transaction_id = oldv.transaction_id
and newv.fieldName = oldv.fieldName
and newv.datestamp = oldv.datestamp
drop table tblSource
the result set shows that the status gets changed... however it also showing barcode being changed as well. Barcode never gets changed.
How do refine this to correctly show whats actually being changed?
September 16, 2009 at 8:28 am
Does it? Or did you just forget the
WHERE oldv.oldvalue newv.newvalue
😉
[Edit:] Also, in your test code, you're not generating new unique identifiers for the last two old/new sets, which give 6 values to 1 unique identifier, which will also show a barcode change, as it encompasses 111000, 222000, 333000.
September 16, 2009 at 8:36 am
Garadin (9/16/2009)
Does it? Or did you just forget theWHERE oldv.oldvalue newv.newvalue
😉
hehe yes I did forget the where clause... but... and maybe im being a little dense... if you do a regular select the (and looking at the inserts)
the transaction pair of old and new for a transaction im not actually changing barcode ... however I dont think there is a way to identify or refine without fixing when I create a new transaction key... hmmm
I think I need to work on how I handle my transactions... thanks for pointing out the where clause tho...
September 16, 2009 at 8:41 am
Did you see my edit? You actually had 2 problems.
This code works perfectly fine:
--// lets create the source data
CREATE TABLE tblSource
(
rid int IDENTITY(1, 1) ,
adtAction varchar(3) ,
transaction_id varchar(255) ,
Barcode int ,
status int ,
datestamp datetime
)
--// now lets mimic what the application is putting into the table
DECLARE @dt datetime
DECLARE @guid uniqueidentifier
DECLARE @TransKey varchar(255)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
111000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
111000 ,
120 ,
@dt
)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
111000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
111000 ,
140 ,
@dt
)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
222000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
222000 ,
140 ,
@dt
)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
333000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
333000 ,
140 ,
@dt
)
SELECT *
FROM tblSource
SELECT oldv.transaction_id ,
oldv.fieldName ,
oldv.oldvalue ,
newv.newvalue ,
oldv.datestamp
FROM (SELECT Transaction_id ,
fieldName ,
oldvalue ,
datestamp
FROM (SELECT Transaction_id ,
datestamp ,
CAST(barcode AS sql_variant) AS barcode ,
CAST(status AS sql_variant) AS status
FROM tblSource
WHERE CHARINDEX('Old', adtAction) > 0
AND transaction_id IS NOT NULL
) AS aam_src UNPIVOT
( oldvalue FOR fieldName IN (barcode, status) ) as unpvt_aam
) AS oldv
INNER JOIN (SELECT Transaction_id ,
fieldName ,
newvalue ,
datestamp
FROM (SELECT Transaction_id ,
datestamp ,
CAST(barcode AS sql_variant) AS barcode ,
CAST(status AS sql_variant) AS status
FROM tblSource
WHERE CHARINDEX('New', adtAction) > 0
AND transaction_id IS NOT NULL
) AS aam_src UNPIVOT
( newvalue FOR fieldName IN (barcode, status) ) as unpvt_aam
) AS newv ON newv.transaction_id = oldv.transaction_id
AND newv.fieldName = oldv.fieldName
AND newv.datestamp = oldv.datestamp
WHERE oldv.oldvalue newv.newvalue
DROP TABLE tblSource
September 16, 2009 at 11:32 am
the where clause was one problem... what did you find was the other ?
September 16, 2009 at 11:36 am
Garadin (9/16/2009)
Did you see my edit? You actually had 2 problems.This code works perfectly fine:
--// lets create the source dataCREATE TABLE tblSource
(
rid int IDENTITY(1, 1) ,
adtAction varchar(3) ,
transaction_id varchar(255) ,
Barcode int ,
status int ,
datestamp datetime
)
--// now lets mimic what the application is putting into the table
DECLARE @dt datetime
DECLARE @guid uniqueidentifier
DECLARE @TransKey varchar(255)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
111000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
111000 ,
120 ,
@dt
)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
111000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
111000 ,
140 ,
@dt
)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
222000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
222000 ,
140 ,
@dt
)
SET @dt = GETDATE()
SET @guid = NEWID()
SET @TransKey = CONVERT(varchar(255), @guid)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('Old' ,
@TransKey ,
333000 ,
120 ,
@dt
)
INSERT INTO tblSource
(adtAction ,
transaction_id ,
barcode ,
status ,
datestamp
)
VALUES ('New' ,
@TransKey ,
333000 ,
140 ,
@dt
)
SELECT *
FROM tblSource
SELECT oldv.transaction_id ,
oldv.fieldName ,
oldv.oldvalue ,
newv.newvalue ,
oldv.datestamp
FROM (SELECT Transaction_id ,
fieldName ,
oldvalue ,
datestamp
FROM (SELECT Transaction_id ,
datestamp ,
CAST(barcode AS sql_variant) AS barcode ,
CAST(status AS sql_variant) AS status
FROM tblSource
WHERE CHARINDEX('Old', adtAction) > 0
AND transaction_id IS NOT NULL
) AS aam_src UNPIVOT
( oldvalue FOR fieldName IN (barcode, status) ) as unpvt_aam
) AS oldv
INNER JOIN (SELECT Transaction_id ,
fieldName ,
newvalue ,
datestamp
FROM (SELECT Transaction_id ,
datestamp ,
CAST(barcode AS sql_variant) AS barcode ,
CAST(status AS sql_variant) AS status
FROM tblSource
WHERE CHARINDEX('New', adtAction) > 0
AND transaction_id IS NOT NULL
) AS aam_src UNPIVOT
( newvalue FOR fieldName IN (barcode, status) ) as unpvt_aam
) AS newv ON newv.transaction_id = oldv.transaction_id
AND newv.fieldName = oldv.fieldName
AND newv.datestamp = oldv.datestamp
WHERE oldv.oldvalue newv.newvalue
DROP TABLE tblSource
Actually I think I just realized what the second problem was... 1 transaction over 2 or more records... Yeah I noticed that in my stored proc and adjusted it so that it would create a new transaction id for each new set of updates so instead of 1 trans id for 4 records I am now back to 1 trans id for the 2 records.. 🙂
Thanks !
September 16, 2009 at 11:38 am
Yeah, sorry, I have a bad habit of editing previous posts that people might have already read with more info. I really need to stop that.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply