Create a delta from downloaded info

Question

Create a delta from downloaded info

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Not confusing for me. You have existing data and you have new data. He want's a way to compare the data that changed.

I do absolutely agree that he needs to provide some readily consumable data. Two tables (one of existing data, one for new data) would be great.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 2

When the process starts I will be extracting all Invoices for the month. If I started today my first load file would be 8-1 thru 8-13

as it gets prior day. Now tomorrow 8-15 the process pulls 8-1 thru 8-14, but I only want to capture any new Invoices from 8-14, or if for some strange reason some value for today's extract is different than what was previously stored. I would like some sort of audit(report) that shows me the newly added data, as well as any record that changed, or any missing invoices that was previously loaded to the live table.

My thought was to have a Staging table which is where the daily Invoices get loaded into, then use that against what I call the live Table call it Daily_Invoices then there would be another table call it Invoices_Delta which would carry the new or updated data from the Staging to Live table process..

Invoicenbr,line,subline,invoicedate,shipdate,acctgrp,channel

-- New data from the 8/15 PULL   --- record bypassed since all data matches

78123,1,0,8-13-2021,8-10-2021,1002,rrb     -- Yesterday's data
78123,1,0,8-13-2021,8-10-2021,1002,rrb     -- Today's data
-- New data from the 8/15 PULL   --- record bypassed since all data matches
78124,1,0,8-13-2021,8-10-2021,1002,rrb     -- Yesterday's data
78124,1,0,8-13-2021,8-10-2021,1002,rrb     -- Today's data
--

-- New data from the 8/15 PULL   --- Data Inserted to Live table as well as Delta Table
78125,1,0,8-14-2021,8-10-2021,1002,rrb     -- Today's data
78126,1,0,8-14-2021,8-10-2021,1002,rrb     -- Today's data

-- New data from the 8/15 PULL   ---  Live table updated as well as Delta Table Inserted
-- Acctgrp changed from original extract record
78127,1,0,8-13-2021,8-10-2021,1002,rrb     -- Yesterday's data
78127,1,0,8-14-2021,8-10-2021,1001,rrb     -- Today's data
-- New data from the 8/15 PULL   ---  Live table updated as well as Delta Table Inserted
-- Acctgrpchannel changed from original extract record
78128,1,0,8-13-2021,8-10-2021,1002,rrb     -- Yesterday's data
78128,1,0,8-14-2021,8-10-2021,1021,rrz     -- Today's data

-- New data from the 8/15 PULL 
-- Send Alert Invoicenbr missing from today's extract
78129,1,0,8-13-2021,8-10-2021,1002,rrb     -- Yesterday's data
                                           -- Today's data missing(8/14)  --->  simulate a missed invoice from the extract process.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

That's nice. Again, please read and heed the first link in my signature line below for how to present some readily consumable data so that one of us can write the code to solve your problem without having to reform your text.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246970 More actions · Answer 4

You seem to be having trouble understanding what is being requested.

What Jeff is talking about is that you provide us with some sample code which we can cut and paste straight into SSMS and execute.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Bruin SSCrazy Points: 2058 More actions · Answer 5

Sorry misunderstood .. will supply

Thanks again!!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Jeff Moden wrote:

Not confusing for me. You have existing data and you have new data. He want's a way to compare the data that changed.
I do absolutely agree that he needs to provide some readily consumable data. Two tables (one of existing data, one for new data) would be great.

Well - he stated he wants a way to identify the delta, then he said he wants a new table - then he stated he will be pulling data from the first of the month through the end of the month day by day.

He was given an option to use EXCEPT to identify the new and changed rows. But - he doesn't seem to have even tried that approach, an since he hasn't provided any data it really isn't clear.

If the goal is to have a history table (as I suspect) - then a temporal table would fit nicely for that. Using EXCEPT to identify new and updated rows - with either an UPSERT or MERGE using the results from the EXCEPT statement would then allow for inserting/updating the data and the temporal table gives us the history table.

But...that is just a guess at this point.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

Bruin wrote:

Sorry misunderstood .. will supply

That means that you haven't actually read the article where that's actually explicitly stated even after being asked a couple of times on this thread and a couple other times on other threads of yours. Help us help you... read the article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

Jeffrey Williams wrote:

Jeff Moden wrote:
Not confusing for me. You have existing data and you have new data. He want's a way to compare the data that changed.
I do absolutely agree that he needs to provide some readily consumable data. Two tables (one of existing data, one for new data) would be great.
Well - he stated he wants a way to identify the delta, then he said he wants a new table - then he stated he will be pulling data from the first of the month through the end of the month day by day.
He was given an option to use EXCEPT to identify the new and changed rows. But - he doesn't seem to have even tried that approach, an since he hasn't provided any data it really isn't clear.
If the goal is to have a history table (as I suspect) - then a temporal table would fit nicely for that. Using EXCEPT to identify new and updated rows - with either an UPSERT or MERGE using the results from the EXCEPT statement would then allow for inserting/updating the data and the temporal table gives us the history table.
But...that is just a guess at this point.

Heh... I absolutely agree that this has been one of those posts. He did publish the following post, though quite late in the game.

https://www.sqlservercentral.com/forums/topic/create-a-delta-from-downloaded-info#post-3919792

And, IMHO, that did clarify it all quite well (at least for me) . After another request for data, he posted and even better explanation and now we just need him to post some readily consumable data. 😀

My response definitely wasn't meant as a disagreement. I just didn't want him to go down another rabbit hole. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 9

Here is the data for the #daily_invoices and a staging table where data imports will happen. Compare the STaging to Live and report

inserts-updates, and create a delta table of the diff's.

CREATE TABLE [dbo].[#daily_invoices](
[InvoiceNbr] [varchar](8) NOT NULL,
[Line] [int] NOT NULL,
[SubLine] [int] NOT NULL,
[InvoiceDate] [date] NOT NULL,
[ShipDate] [date] NOT NULL,
[MwAcctType] [varchar](25) NULL,
[Channel] [nvarchar](12) NULL,

 CONSTRAINT [pk_dly_invoices] PRIMARY KEY CLUSTERED 
(
[InvoiceNbr] ASC,
[Line] ASC,
[SubLine] ASC,
[InvoiceDate] ASC,
[ShipDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Insert into [#daily_invoices]
Values(78123,1,0,'8-13-2021','8-10-2021',1002,'rrb')
go
Insert into [#daily_invoices]   
values(78124,1,0,'8-13-2021','8-10-2021',1002,'rrb')     
go

Insert into [#daily_invoices]
values(78127,1,0,'8-13-2021','8-10-2021',1002,'rrb')
go
 
Insert into [#daily_invoices]
values(78128,1,0,'8-13-2021','8-10-2021',1002,'rrb')
go

Insert into [#daily_invoices]
values(78129,1,0,'8-13-2021','8-10-2021',1002,'rrb') 
go

Select * from
[#daily_invoices];



--



CREATE TABLE [dbo].[#staging](
[InvoiceNbr] [varchar](8) NOT NULL,
[Line] [int] NOT NULL,
[SubLine] [int] NOT NULL,
[InvoiceDate] [date] NOT NULL,
[ShipDate] [date] NOT NULL,
[MwAcctType] [varchar](25) NULL,
[Channel] [nvarchar](12) NULL)


Insert into [#staging]
Values(78123,1,0,'8-13-2021','8-10-2021',1002,'rrb')
go
Insert into [#staging]   
values(78124,1,0,'8-13-2021','8-10-2021',1002,'rrb')     
go
Insert into [#staging]
values(78125,1,0,'8-13-2021','8-10-2021',1002,'rrb')
go
Insert into [#staging]    
values(78126,1,0,'8-13-2021','8-10-2021',1002,'rrb')
go     

Insert into [#staging]
values(78127,1,0,'8-13-2021','8-10-2021',1001,'rrb')
go
 
Insert into [#staging]
values(78128,1,0,'8-13-2021','8-10-2021',1021,'rrz')
go


Select * from
#staging;

Bruin SSCrazy Points: 2058 More actions · Answer 10

Was this the info you where looking for?

Thanks.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

Bruin wrote:

Was this the info you where looking for?
Thanks.

Please provide details of your expected answer based on the demo data provided.....prevents any confusion on my part <grin>

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Bruin SSCrazy Points: 2058 More actions · Answer 12

Answering Post by Jeff

"Two tables (one of existing data, one for new data) would be great."

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 13

Bruin wrote:

Answering Post by Jeff
"Two tables (one of existing data, one for new data) would be great."

Are you absolutely sure that you want to include the invoice date and the ship date as a part of the PK? That DOES mean that you could end up with (for example) different invoice dates for the same invoice, which seems like a huge error to allow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 14

Bruin

SSCrazy

Points: 2058

August 17, 2021 at 6:05 pm

#3920883

With the same Line,subline?

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 15

Bruin wrote:

With the same Line,subline?

Look at the PK you've chosen. IMHO, only the first 3 columns of InvoiceNbr, Line, and SubLine should make up the PK.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)