datediff | Simple module to calculate difference between two date

Coming from an MSSQL background myself and now more often working in Postgres I feel your pain =)

The "trouble" with Postgres is that it supports only 'basic' SQL commands (SELECT, INSERT, UPDATE, CREATE, ALTER, etc...) but the moment you want to add logic (IF THEN, WHILE, variables, etc.) you need to switch to pl/pgsql which you can only use inside functions (AFAIK). From a TSQL POV there are quite some limitations and in fact, some things suddenly don't work anymore (or need to be done differently.. e.g. SELECT * INTO TEMPORARY TABLE tempTable FROM someTable will not work but CREATE TABLE tempTable AS SELECT * FROM someTable will)

Something I learned the hard way too is that CURRENT_TIMESTAMP (or Now()) will return the same value within a transaction. And since everything inside a function runs inside a transaction this means you have to use clock_timstamp()

Anyway, to answer your question, I think this should get you going:

Just create a permanent table called Quarters.

date_or_time_part must be one of the values listed in Supported Date and Time Parts (e.g. month). The value can be a string literal or can be unquoted (e.g. 'month' or month).

You can use a CASE expression

The main exception is:

Invalid character value for cast specification.

The value could not be converted because of a potential loss of data.

Check the destination SQL table columns data types and the columns mapping on the destination.

As the Error description provided in the question shows, it was thrown by the OLE DB Destination component. This means that Derived Column Transformations are working fine.

I suggest reading the following article to learn more about SSIS data types, data conversion transformation, implicit and explicit conversion:

• SSIS Data types: Change from the Advanced Editor vs. Data Conversion Transformations

Suppose the data type mismatch is caused by the values generated by the Derived Column. In that case, you can try using a casting operator or a Data Conversion Transformation to solve the conflict.

Besides, you should be aware that the Derived Column transformation outputs are of type DT_WTR or DT_STR, so they should be mapped to a VARCHAR or NVARCHAR columns in the destination table.

Based on our discussion on your related question: Derived Column to handled mulitple conditions in CASE statement?. It looks like you are trying to insert a string value within a tinyint column as you are trying to replace the type code with the equivalent string value and reinsert it into the SQL table.

You should not edit your data since it is well designed and stored. You should create another table (example: TypeReference(Id tinyint, Description VARCHAR(50)) and store all the string values that you are using in your CASE statement. Then you should join both tables once you need to retrieve the string values.

For example:

Use the window functions MIN OVER and MAX OVER in order to see whether a year is the first/last year for a client and service.

For me I find it easiest to process in this order:

• highlight all cases where previous row is more than a day earlier
• for every row where the gap is more than a day, output a 1, instead of a zero
• from there, add up all the preceding values to create a group for each gap - since every gap will have a 1, each new gap will make the cumulative sum increase by 1, but every subsequent 0 will not, so it will keep all the consecutive days in an "island"
• finally, use partition by to apply a new row number sequence to each group.

Resulting query is:

You can't refer to an alias in a WHERE clause defined in the same SELECT. MySQL overloads its HAVING clause to allow it to use aliases. The following version should work:

You can try converting both hours and mins to varchar and concate both to make new column.

as per comments, try this: