Creating and Managing SQL Scripts - Microsoft Business Intelligence Tools for Excel Analysts - page 201

The PaymentPeriodKey in this example is calculated using a simple algorithm. The algo-
rithm uses DateDiff to calculate the number of days between the OrderDate and the
PaymentDate, and divides that difference by 30 (30.0 is used to make SQL Server output
a decimal). The Ceiling function is added to force SQL Server to round up. The resulting
output is an integer that represents the period number where a period is 30 days.

Note

Data delivery scripting
In general, during data delivery you follow two common script patterns — append and update, or
load an empty table.

Append and update. For an incremental update, there is a specific scripting pattern to follow, as
shown in Listing 10-4. Always run your updates before adding new data. If you add new data to your
target table first, you end up updating that table again. The pattern involves matching the source
and target tables on the business key of the data and checking to see if any attributes have changed
for those records that matched. Data is updated for any changes from the source. The second step is
to look for all records that do not exist in the target and load them from the source.

Listing 10-4: Append and Update Data
Update t
Set CustomerFirstName=s.CustomerFirstName,
CustomerLastName=s.CustomerLastName,
CustomerEmail=s.CustomerEmail,
ModifyDate=GetDate()
From Deliver.Customer t
Join Prepare.Customer s
On t.CustomerKey=s.CustomerKey
Where CustomerFirstName<>s.CustomerFirstName
Or CustomerLastName<>s.CustomerLastName
Or CustomerEmail<>s.CustomerEmail

Insert Into Deliver.Customer
Select s.CustomerKey,
s.CustomerFirstName,
s.CustomerLastName,
s.CustomerEmail,
LoadDate=GetDate(),
ModifyDate=GetDate()
From Prepare.Customer s
Left Join Deliver.Customer t
On t.CustomerKey=s.CustomerKey
Where t.CustomerKey Is Null

You can use a Merge statement to accomplish an append and update to your data,
as well.

Tip