December 10, 2015 at 5:19 am
Hi I have doubt in sql server
CREATE TABLE Trantable(empid INT, deptid INT, Projectname VARCHAR(10), Transactionid INT)
INSERT INTO Trantable VALUES
(1,10, 'test' , 1),
(2,11, 'test1' , 2),
(2,10, 'jai' , 3);
GO
CREATE TABLE dimemp (empkey INT, empid INT, ename VARCHAR(10), flag INT)
INSERT INTO dimemp VALUES
(1, 1 , 'a' , 1),
(2, 2 , 'b' , 1),
(3, -1 , 'na' , 1);
GO
CREATE TABLE dimdept (deptkey INT, deptid INT, deptname VARCHAR(10), flag INT)
INSERT INTO dimdept VALUES
(1, 10 , 'hr', 1),
(2, 11 , 'ceo' , 1),
(3, -1 , 'NA', 1);
GO
CREATE TABLE Facttran ( empkey INT, deptkey INT, projectname VARCHAR(20),transactionid INT)
GO
BEGIN TRAN
merge into facttran target
using (select ISNULL(a.empkey, (select empkey from Dimemp where empid = -1)) empkey,
ISNULL(b.deptkey, (select deptkey from dimdept where deptid = -1)) deptkey,
c.projectname, c.transactionid
from trantable c
left join dimemp a on a.empid=c.empid and a.flag=1
left join dimdept b on b.deptid=c.deptid and b.flag=1
where not exists (select 1 from facttran where transactionid=c.Transactionid)
)source
on target.transactionid=source.transactionid
when not matched
then insert ([deptkey],[empkey],[projectname],[transactionid])
values(source.deptkey,source.empkey,source.projectname,source.transactionid)
;
UPDATE O
SET empkey=COALESCE((SELECT TOP 1 empkey FROM dimemp WHERE empid=(SELECT empid FROM TranTable WHERE TransactionId = O.transactionid) ORDER BY flag),empkey)
,deptKey =COALESCE((SELECT TOP 1 deptkey FROM dimdept WHERE deptid=(SELECT deptid FROM Trantable WHERE Transactionid = O.Transactionid) ORDER BY flag),deptkey)
,projectname=b.projectname
FROM
Facttran O left join trantable b on o.transactionid=b.transactionid
COMMIT
SELECT * FROM FACTTRAN
upto now its working fine
next day dimemp table dimdept table records are updated.both dimenstion are scdtype 2 implementation.just i insert new record in dimemp and dimdept as per understand
purpose dimemp table name changed a to aaa slow flage change 0 to 1 simila to dimdept table.
INSERT INTO dimemp VALUES
(4,1,'aaa' , 1);
INSERT INTO dimdept VALUES
(4,10,'hrdept', 1);
GO
-----------------
so second time dimensions table data look like
INSERT INTO dimemp VALUES
(1,1,'a' , 0),
(2,2,'b' , 1),
(3,-1,'na' , 1),
(4,1,'aaa' , 1);
INSERT INTO dimdept VALUES
(1,10,'hr',0),
(2,11,'ceo',1),
(3,-1,'NA',1),
(4,10,'hrdept', 1);
----------------------
INSERT INTO Trantable VALUES
(1,11, 'test' , 1 ),
(1,11, 'test123', 2 ),
(2,10, 'jai' , 3 ),
(1,10, 'cod' , 4 ),
(6,19,'test' ,6);
GO
trantable:
empid | deptid | Projectname | Transactionid
1 |11 | test | 1 ------record updated in source side here deptid changed from 10 to 11
1 |11 | test123 | 2 -------Here empid changed from empid 2 to 1 and projectname changed test1 to test123
2 |10 | jai | 3 ------here no records are not changed
1 |10 | cod | 4 ----------new record is came
6 |19 | test | 6 ---new record is came
then I ran same query again
here if any empid or deptid values not matched whih corresponding dimensiont table that we need to consider default key values
here each dimenion default values is -1 if any dimension values not matched then we need to retrive -1 corresponding key values of dimensions
BEGIN TRAN
merge into facttran target
using (select ISNULL(a.empkey, (select empkey from Dimemp where empid = -1)) empkey,
ISNULL(b.deptkey, (select deptkey from dimdept where deptid = -1)) deptkey,
c.projectname, c.transactionid
from trantable c
left join dimemp a on a.empid=c.empid and a.flag=1
left join dimdept b on b.deptid=c.deptid and b.flag=1
where not exists (select 1 from facttran where transactionid=c.Transactionid)
)source
on target.transactionid=source.transactionid
when not matched
then insert ([deptkey],[empkey],[projectname],[transactionid])
values(source.deptkey,source.empkey,source.projectname,source.transactionid)
;
UPDATE O
SET empkey=COALESCE((SELECT TOP 1 empkey FROM dimemp WHERE empid=(SELECT empid FROM TranTable WHERE TransactionId = O.transactionid) ORDER BY flag),empkey)
,deptKey =COALESCE((SELECT TOP 1 deptkey FROM dimdept WHERE deptid=(SELECT deptid FROM Trantable WHERE Transactionid = O.Transactionid) ORDER BY flag),deptkey)
,projectname=b.projectname
FROM
Facttran O left join trantable b on o.transactionid=b.transactionid
COMMIT
SELECT * FROM FACTTRAN
empkey| deptkey | projectname |Transactionid
1 | 2 | test | 1
1 | 2 | test1 |2
2 | 1 | jai |3
4 | 4 | cod |4
3 | 3 | test | 6 -----here we filled default -1 value corressponding key reason is transactionid =6 relation empid is 6 same empid not
avilable in dimemp table so we fill with default key similar to deptkey .
third day
INSERT INTO Trantable VALUES
(1,10,'test' ,6); here transactionid=6 record is update at source side empid changed from 6 to 1 and deptid changed from 19 to 10
then if i ran same query that time I did not get expected result in facttran table.I need below output
empkey| deptkey | projectname |Transactionid
1 | 2 | test | 1
1 | 2 | test1 |2
2 | 1 | jai |3
4 | 4 | cod |4
4 | 4 | test |6 ---here key will be chang empkey 3 to 4 and deptkey change 3 to 4.
but i did not get that expected result.
here mainlay when we go for insert or update we need to consider source side(trantable) transactonid is matched or not with facttran table
if transacton id is matched then again we need to check sourc(trantable) related empid is same or not in facttran table if source side(trantable) empid changed with
existing transactionid then we need to update lated flag=1 matched corresponding keys values.suppose if same transactionid related empid not changed at source side(trantable)
then we do not need updated that empkey column in facttran table.if existing transctionid only changed deptid not chagned empid that time
we need to update deptid key with latest flag=1 in in dimdept table we donot need update empkey because of source side(tranatable) empid not change.
if we update without changed empkey that time we will get wrong information in the fact table.because source side is not changed empid only deptid changed.
some time both empid and deptid changed in the existing transactionid that time we need update empkey and deptkey with latest flag=1 corresponding keys.
please tell me how to write update query to achive this task in sql server
December 10, 2015 at 9:14 am
The MERGE statement can be tricky to implement even for experienced developers. Since you're only using it for inserts it's overkill anyway. I'd recommend you use INSERT instead - it's far easier to get right.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2015 at 6:57 pm
insert statement working fine with merge or with out merge statement.mine s trucked with update statement based conditions .here mainly trantable related transactionid is exist or not in facttran table if exist again we need check same transactionid related each key corresponding value is same or not in facttrantable.if values is changed then we need to updated latest flag=1 corresponding key.if values is not changed then we donot need update that columns.
example:
firstday: trantable:
empid | deptid | Projectname | Transactionid
1 | 10 | test | 1
so we are loaded this record into facttran table.
next day dimensions side updates done due change of name in both dimensions so empid 1 related old key is 1 and flag=0 and new empid =1 related new key is 4 and similay to dimdept.
and trantable transactionid 1 related deptid changed/updated as source side from deptid=10 to 11
now in tran table we donot need updated empid corresponding key values we need update only deptid corresponding key with flag=1 value.
output for facttran :2nd day
empkey | deptkey| projectname|transactionid
1 (no need change) | 2 | test |1
same way need to check each key same or not trantable and facttran with same transactionid when we go for update.
please tell me how to write a update query to achieve this task in sql server.
December 11, 2015 at 2:10 am
baludw22 (12/10/2015)
insert statement working fine with merge or with out merge statement.mine s trucked with update statement based conditions .here mainly trantable related transactionid is exist or not in facttran table if exist again we need check same transactionid related each key corresponding value is same or not in facttrantable.if values is changed then we need to updated latest flag=1 corresponding key.if values is not changed then we donot need update that columns.example:
firstday: trantable:
empid | deptid | Projectname | Transactionid
1 | 10 | test | 1
so we are loaded this record into facttran table.
next day dimensions side updates done due change of name in both dimensions so empid 1 related old key is 1 and flag=0 and new empid =1 related new key is 4 and similay to dimdept.
and trantable transactionid 1 related deptid changed/updated as source side from deptid=10 to 11
now in tran table we donot need updated empid corresponding key values we need update only deptid corresponding key with flag=1 value.
output for facttran :2nd day
empkey | deptkey| projectname|transactionid
1 (no need change) | 2 | test |1
same way need to check each key same or not trantable and facttran with same transactionid when we go for update.
please tell me how to write a update query to achieve this task in sql server.
Compare the two tables facttran and trantable using the unique column transactionid as the join column. If any values are different, then update facttran. Is this all you are attempting to do? Your script is very hard to understand.
Your UPDATE statement uses a LEFT JOIN:
FROM Facttran O
left join trantable b on o.transactionid=b.transactionid
Change that to an inner join and you don't need COALESCE. Next, change the UPDATE to a SELECT so you can see what changes you are going to make:
SELECT
o.*,
NewEmpkey = x1.empkey,
NewdeptKey = x2.deptkey,
NewProjectname = b.projectname,
b.*
FROM Facttran O
INNER JOIN trantable b
ON o.transactionid=b.transactionid
OUTER APPLY (SELECT TOP 1 empkey FROM dimemp WHERE empid = b.empid ORDER BY flag) x1
OUTER APPLY (SELECT TOP 1 deptkey FROM dimdept WHERE deptid = b.deptid ORDER BY flag) x2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 11, 2015 at 3:23 am
Hi its very close to my expected result.we need to handled unmatched empid and deptid related values also
example trantable empid=23 this empid related dimemp table do not have empid=23 related key that time we need handle where ever empid=-1 that corresponding key we need to fill in facttran table.similary way deptid
if deptid values not available in dimdept table then in dimdept table have deptid=-1 that corresponding key we need to fill for unmatched values.
if we use top function in update statement that time we got get expected keys
example: 1 day process
trantable
empid|deptid|projectname|transactionid
1 |10 | test | 1
dimemp:
empkey |empid |name | flag
1 | 1 |1 |1
2 | -1 |na |1 ---this key for handled unmatched records key
dimdept:
deptkey |deptid | name | flag
1 |10 |hr |1
2 |-1 | na |1 ---this key for handled unmatched records key
based on above records we will get output in facttran table like below
so far its work fine
2nd day
trantable empkey| deptkey |projectname |transactionid
1 | 1 | test |1
empid|deptid|projectname|transactionid
1 |11 | test | 1 -----here deptid changed 10 to 11 but empid not changed
empkey |empid |name | flag
1 | 1 |a |0
2 | -1 |na |1 ---this key for handled unmatched records key
3 | 1 | aa |1
dimdept:
deptkey |deptid | name | flag
1 |10 |hr |0
2 |-1 | na |1 ---this key for handled unmatched records key
3 |10 |ceo |1
4 |11 |hmo |1
based on 2nd day we need out put in facttran table like below
empkey| deptkey |projectname |transactionid
1 | 4 | test |1 ----------------> here empkey no need to change and deptkey only change
but as per give top function(if we give ascending/descnding order) empkey aslo changed .its go wrong result facttrantable.
similar case applicable deptkey also.next third day again few values will changed and few values added in trantable.so every time when we go for updated facttran table we need check transactionid exist or not if exist again check each key related values matched or not if matched no need to update that key if not matched then updated with flag=1 corresponding key values in fact trantable.
please tell me update statement to achieve this task in sql server .
December 11, 2015 at 4:28 am
You mean, like this?
SELECT
o.*,
NewEmpkey = ISNULL(x1.empkey,-1),
NewdeptKey = ISNULL(x2.deptkey,-1),
NewProjectname = b.projectname,
b.*
FROM Facttran O
INNER JOIN trantable b
ON o.transactionid=b.transactionid
OUTER APPLY (SELECT TOP 1 empkey FROM dimemp WHERE empid = b.empid ORDER BY flag) x1
OUTER APPLY (SELECT TOP 1 deptkey FROM dimdept WHERE deptid = b.deptid ORDER BY flag) x2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 11, 2015 at 4:45 am
Hi for unmathced record will be handle like below syntax
Empkey = isnull(aa.empkey,(select empkey from dimemp where empid=-1)),
deptKey = isnull(bb.deptkey,(select deptkey from dimdept where deptid=-1))
it will work good.
when we go for update that time will be come issue with top function
example trantable have transactionid 1,2,3 with same empid 1 and deptid 10
that time will work fine. 2nd day tranctionid 1 related data does not chanaged but transactionid 2 and 3 changed
data.transactionid=2 related changed empid and deptid not changed transctionid=3 related changed deptid and not changed empid.
when we go for update statement we need to follow below steps
step1: transactionid exist or not if exist then we need to consider that record need updated statement
step 2: when we go for updated we can not updated at a time empkey and deptkey for transactionid because of dimension have history key with flag 0 and 1.
step 3:in the update statement we need to give if else condition if same transactionid and same empid then no need update empkey if same transactionid and empid differnt then update empkey only. similar to deptid related keys.
every time first check transaction and again check individual empkey and deptkey values exist or not
please tell me how to write query to achive this task in sql server.
December 11, 2015 at 6:09 am
baludw22 (12/11/2015)
Hi for unmathced record will be handle like below syntaxEmpkey = isnull(aa.empkey,(select empkey from dimemp where empid=-1)),
deptKey = isnull(bb.deptkey,(select deptkey from dimdept where deptid=-1))
it will work good.
when we go for update that time will be come issue with top function
example trantable have transactionid 1,2,3 with same empid 1 and deptid 10
that time will work fine. 2nd day tranctionid 1 related data does not chanaged but transactionid 2 and 3 changed
data.transactionid=2 related changed empid and deptid not changed transctionid=3 related changed deptid and not changed empid.
when we go for update statement we need to follow below steps
step1: transactionid exist or not if exist then we need to consider that record need updated statement
step 2: when we go for updated we can not updated at a time empkey and deptkey for transactionid because of dimension have history key with flag 0 and 1.
step 3:in the update statement we need to give if else condition if same transactionid and same empid then no need update empkey if same transactionid and empid differnt then update empkey only. similar to deptid related keys.
every time first check transaction and again check individual empkey and deptkey values exist or not
please tell me how to write query to achive this task in sql server.
I'm sorry but this is very difficult to understand. I suggest you read this article [/url]and post up some consumable data to show what it is you are attempting to do.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply