-
SQL TIP-> Display seconds as HH:MM:SS
In many BI application you have stored a duration as seconds. To display that on the format HH:MM:SS just use the following SQL(Replace 100 with your column name): Wich yield the result As in my previous blogpost where I did the same in analysis services, this only works if the duration is below 24 hours,… Continue reading
-
Fun with SQL: Joining on column name and row field value
Ok, it is that time of the day where I’m starting to get high on caffeine 🙂 So this is the scenario. I have an application that is automatically generating a table in my database. It can add columns on the fly(But thankfully not delete). It could look like this: We can now query information… Continue reading
-
SQL Convert datetime to Salesforce date format
If anyone, as I, suffers the pain of generating SOQL to salesforce, here’s a little tip 🙂 If you have a datetime field, which you have to use in the SOQL, then convert it like so: SELECT CONVERT(NVARCHAR,GETDATE(),126) Then you get it in the format YYYY-MM-DDTHH:MM:SS.XXX As salesforce wants it 🙂 Continue reading
-
SSAS Tip -> Format seconds as HHHH:MM:SS
A common way of showing durations in SSAS, is to format them as HH:MM:SS. A common scenario is to store the durations in seconds. To display them in the HH:MM:SS format, first we have to convert the duration to hours, and then put on the format, like so CREATE MEMBER CURRENTCUBE.[MEASURES].[someMeasure] AS [Measures].[someMesureInSeconds]/86400,FORMAT_STRING = “HH:MM:SS”,… However,… Continue reading
-
SQL TIP ->Other uses of the SQL GO CMD
As most developers know, the SQL GO CMD signals the end of a batch of SQL statements. However, another use of it, is to use it to execute a SQL statement multiple times. Just add a number after it. For instance: Which will execute the Select statement 5 times. Or, something maybe more useful, create… Continue reading
-
SQL TIP -> Recursive CTEs
This morning I had to do some recursive common table experessions. It is acctually relativly simple to do. A common scenario: We have a parent child relation in a selfreferencing table. That is common when we have an employee manager relationship or organisation relationship. Let’s just look at the employee manager relationship. Let’s create an… Continue reading
-
Prepopulate a table with data in SSDT
The new database project in SSDT, is truly a great tool. It is not perfect thou, and one of its shortcomings, is that there is no out of the box way, to define some static/initial data in in a project, when you deploy it. In all the projects I do, I always want to have… Continue reading
-
Analysis services -> Clear Cache
Just a short note about analysis services and its cache 🙂 One of the reasons for analysis services fast respond time is its cahce. But what if you want to test a query without using the cache? Well, just use this XMLA Script: Also, if you do partial processing, the cache, can in some cases… Continue reading
-
Change ”select top 1000” to ”Select top XXX”
In SQL server management studio, when you right click on a table, there is a shortcut to select top 1000 or edit top 200 rows. Se pic below. It is an easy way, when you just want to see some of the data in a table, or if you want to edit some values in… Continue reading
