October 28, 2016 at 11:55 pm
I have two tables
CREATE TABLE [dbo].[StatusAudit](
[RowID] [int] IDENTITY(1,1) NOT NULL primary Key,
[AccountID] [varchar](20) NOT NULL,
[Status] [int] NOT NULL,
[AuditDte] [datetime] NOT NULL)
and
CREATE TABLE [dbo].[Status](
[AccountID] [varchar](20) NOT NULL primary Key,
[Status] [int] NOT NULL,
[AuditDte] [datetime] NOT NULL)
and this is a process, the data is loaded into Status table, which will always have one record, when the Order status change, the old Status goes into StatusAudit table. Let's say the Status was 5, then changes to 2, we will only have 2 in Status table and 5 into StatusAudit. when the 2 in Status table changes into 7, we will have 7 in the Status table and 5 and 2 in the Status Audit.
Now I have to move data from Status and StatusAudit to Dim_Table. the process is, we load StatusAudit Table as it is, but before we load Status Table, we check first if the Status value in the Status Table does exist in the StatusAudit table, If it does, don't load it into the Dim(because we loaded StatusAudit already, so that value will be loaded as well), if it doesn't then load it to the Dim_Table.
This is what I have tried to do:
CREATE TABLE ##NewRecords
(
AccountIDint
,Statusint
,AuditDte datetime
)
insert into ##NewRecords
select
AccountID
,DEAStatus
,Auditdte = convert(datetime, Auditdte, 121)
FROM dbo.StatusAudit
insert into ##NewRecords
select
a.AccountID
,a.DEAStatus
,Auditdte = convert(datetime, a.Auditdte, 121)
from dbo.Status a
inner join ##NewRecords b
on a.AccountID = b.AccountID
where a.Auditdte = b.AuditDte
and a.Status not in (select Status from ##NewRecords c
where c.AccountID = a.AccountID)
The second part of my query gives me problems, I don't know where I'm getting it wrong
October 29, 2016 at 2:06 am
hoseam (10/28/2016)
I have two tablesCREATE TABLE [dbo].[StatusAudit](
[RowID] [int] IDENTITY(1,1) NOT NULL primary Key,
[AccountID] [varchar](20) NOT NULL,
[Status] [int] NOT NULL,
[AuditDte] [datetime] NOT NULL)
and
CREATE TABLE [dbo].[Status](
[AccountID] [varchar](20) NOT NULL primary Key,
[Status] [int] NOT NULL,
[AuditDte] [datetime] NOT NULL)
and this is a process, the data is loaded into Status table, which will always have one record, when the Order status change, the old Status goes into StatusAudit table. Let's say the Status was 5, then changes to 2, we will only have 2 in Status table and 5 into StatusAudit. when the 2 in Status table changes into 7, we will have 7 in the Status table and 5 and 2 in the Status Audit.
Now I have to move data from Status and StatusAudit to Dim_Table. the process is, we load StatusAudit Table as it is, but before we load Status Table, we check first if the Status value in the Status Table does exist in the StatusAudit table, If it does, don't load it into the Dim(because we loaded StatusAudit already, so that value will be loaded as well), if it doesn't then load it to the Dim_Table.
This is what I have tried to do:
CREATE TABLE ##NewRecords
(
AccountIDint
,Statusint
,AuditDte datetime
)
insert into ##NewRecords
select
AccountID
,DEAStatus
,Auditdte = convert(datetime, Auditdte, 121)
FROM dbo.StatusAudit
insert into ##NewRecords
select
a.AccountID
,a.DEAStatus
,Auditdte = convert(datetime, a.Auditdte, 121)
from dbo.Status a
inner join ##NewRecords b
on a.AccountID = b.AccountID
where a.Auditdte = b.AuditDte
and a.Status not in (select Status from ##NewRecords c
where c.AccountID = a.AccountID)
The second part of my query gives me problems, I don't know where I'm getting it wrong
Try this
insert into ##NewRecords
select
AccountID
,DEAStatus
,Auditdte --= convert(datetime, Auditdte, 121) -- This is not required. You are converting datetime to datetime
FROM dbo.StatusAudit
insert into ##NewRecords
select
a.AccountID
,a.DEAStatus
,a.Auditdte --= convert(datetime, a.Auditdte, 121)
from dbo.Status a
left join ##NewRecords b
on a.AccountID = b.AccountID
and a.Status = b.Status
and a.Auditdte = b.AuditDte
where b.AccountID is null
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply