Day of week calculation

With SQL Server you can get the day in week using this simple code:

SELECT DATEPART(weekday, getdate())

This gives you a number from 1 to 7, corresponding to the day in the week.

But which day is the first day in the week?

If you are European - it's Monday.
If you are American - it's Sunday.
If you are SQL Server - it depends...

There's a setting in SQL Server that controls this behavior and perhaps it is not set to your liking. And perhaps you don't want to change it and run the risk of side-effects - or perhaps you simply don't have the permissions.

Despair not!

This simple code will allow you to get the day in week independent of SQL Server's settings:

European:
SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 1) % 7

American:
SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 2) % 7

How to create lookup views for enum types in ERP system

Problem

Quite often we have to include fields in our data model that have no lookup table in the ERP system. They are usually some kind of "Type" and they are sometimes referred to as "Enum" types in the ERP system.

They have their text values stuck somewhere inside the ERP application layer rather than in real database tables.

Also often it is not desirable to create physical tables in ERP database. And even if we could they would be separated from both the ERP system and the data model we are creating, living their own lives so to speak.

So how do we get a lookup table with the text values corresponding to the enum types?

Solution

One possible solution to this is to create a Dynamic View in Modeller and simple create the record synthetically - with no physical tables involved.

All you need is the ERP system to look up the text values you need and then enter them in the Dynamic View.

Example

Let's say we have an enum called "LineType", which has these values:

0: Item
1: Text

We can create a Dynamic View for these values with this SQL:

SELECT 0 AS LineType_ID, 'Item' AS LineType_Name
UNION ALL
SELECT 1, 'Text'

For practical purposes there will usually be more than 2 values of your enum, so just keep adding "UNION ALL... SELECT..." statements for all the values you need.

That's all. Enjoy!