Pointer to a function in SQL ?!

If you're coming from C then you would know that there's a thing called a  a function pointer.
It's good to put in a structure, and then whenever you need to "compare" you would call the function pointer. In the "sort" function call in one of the C's library you can see it, so you can compare apples to oranges to your heart's delight.
But what happens in SQL?
Let's say I have different funds (which I do) and they require different calculation according to their Fund type.
So funds A, B, C & D have calculation which is completely different the funds E, F & G.
Had I used a programming language like C I could say that a Fund could have a pointer to a function in their structure, and calculate of that. But how do I handle this in SQL?
I've designed a solution which would look like this:
I would add a field (column) to the Fund table called "CalcType" which would refer to the calculation type. Now, I can use the following code:


SELECT *
FROM PerformanceCalc1 (@param) --that's a function
INNER JOIN dbo.FUND
WHERE Fund.CalcType = 1
UNION ALL
SELECT *
FROM PerformanceCalc2 (@param) --that's a function
INNER JOIN dbo.FUND
WHERE Fund.CalcType = 2
UNION ALL
SELECT *
FROM PerformanceCalc3 (@param) --that's a function
INNER JOIN dbo.FUND
WHERE Fund.CalcType = 3



This calculation has its limitation: each Performance Calc functions run on all the funds, but the return set is limited to the appropriate Fund Type. The number of funds I have is as such that it doesn't really matter; if I had more it could have been an issue. Yet, what I like about this solution is that it is set based in its thinking, and not using Dynamics SQL (which requires elevated permissions of the end-user, if we want her to run it).

Rolling Average SQL

How many scripts you need to see for rolling average (AVG) in SQL? Well, it never hurts to see another one...
This is the beauty of Windows Functions, they take a complicated problem and put it in a simple script.

(BTW - I've taken the Sales numbers from here - a great webiste, but I believe it's for PostgresSQL, and then added the year 2016 and the extra numbers. I believe you'll be able to find the pattern.)

CREATE TABLE #temp  (StartDate DATE, Sales INT)

INSERT #temp (StartDate, Sales)
VALUES ('20150110', 5)
,('20150210', 3)
,('20150310', 7)
,('20150410', 8)
,('20150510', 2)
,('20150610', 3)
,('20150710', 6)
,('20160110', 8)
,('20160210', 6)
,('20160310', 10)
,('20160410', 11)
,('20160510', 5)
,('20160610', 6)
,('20160710', 9)



If you just want the rolling average (you can replace the AVG function with SUM, MIN, MAX as well) for all the time periods, you need to run the following code:


SELECT
StartDate,
AVG (sales) OVER ( order by StartDate ) AS AVGSales
FROM #temp


But if you want to partition by YEAR, for example, you'd better run the following code:


SELECT
StartDate,
AVG (sales) OVER (Partition  by YEAR (StartDAte) order by StartDate ) AS AVGSales
FROM #temp



Short & sweet!

(this is a simple example; I need to run this on a total assets for a fund. It's amazing how those extra digits get us confused!)

Don't forget :)

DROP TABLE #temp

My first cursor - faster than SET based operation

Of course, you should NEVER write cursor. Especially not for SQL server. Or so I was told.

The rational behind the code

I believe people hate them so much because usually you might find them in some legacy code, where there could be some other proper SET based operations, or at least they might exist nowadays. Nobody likes legacy code. Nothing too delightful about jumping into code that you don't know and you didn't write. Another reason is of course, for the usual T-SQL code that you write some smart people wrote optimizer for. If you run a Cursor the optimizer cannot kick in, so it's likely that the code would run slower.

But sometimes you do need to write cursors. The main use is when you need to go row by row. Then why not use client side programs, like C# or VB? Well, maybe it's because the developer feels more competent using SQL than by using C#? That's been my excuse for writing the code below.
So I had to go row by row for a one off. My SQL skills exceed my C# skills. I needed to run this code once and that's all. So I choose to write a cursor.

What does it do?

There had been some duplicated rows of data in the "attachment" table. The data is a file, saved in the "content" column as varbinary(VARMAX), and I just wanted to make sure that the duplicated file name didn't stem from a different files, but rather, represented the same file on multiple instances. I've made sure that all attachments were saved to a new table called "multiple_attachment" when "min_attachid" is the ref (there's obviously another field called "file name" which I didn't bother with this time).
Since it's hard to trace errors in Cursor I've added the "INSERT" statement.
Using SET based operation would not be quicker (tested!) due to the INNER JOIN of a table on itself, on top of the expensive operation of comparing varbinary(max).

The code

DECLARE first_cursor CURSOR
FOR
SELECT content, attachid, min_attachmentid
FROM dbo.multiple_attachment
WHERE attachid <> min_attachmentid

DECLARE @content VARBINARY (max)
DECLARE @attachid BIGINT
DECLARE @min_attachid BIGINT

OPEN first_cursor

FETCH NEXT FROM first_cursor
INTO @content, @attachid, @min_attachid

WHILE @@FETCH_STATUS = 0
BEGIN
IF @content <> (SELECT content
FROM dbo.attachment
WHERE attachid = @min_attachid)
INSERT  [dbo].[trace_errors]
           ([attachid])
     SELECT (@attachid)

FETCH NEXT FROM first_cursor
END

CLOSE first_cursor
DEALLOCATE first_cursor

A problem:

The code above got stuck with the error "An error occurred while executing batch. Error message is: Error creating window handle."
because there were over 2000 lines in the table and there was a limit to how much "Display Result" window panes you can create. For every line it produced a new message! the solution was to disable the results pane in the Query Option (Query -> Query Options -> Results
choose "Discard results after execution" in SQL Server 2012). The above proc run much quicker and I would recommend setting this option each time you need to write a Cursor.

Investigating undocumented database - part 2

I've written in the past about how to investigate an undocumented database, and here are a few more insights to my previous post.
First of all, if you need to list all the tables with data you can use the following script:


SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnuasedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
AND p.rows > 0 --As we want only tables with data
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name;

Warning: sometimes if a table is very large due to partitioning it could appear twice on the list! so you'd see both tables with the same name and only one schema... that's confusing, but it could happen.

When you look around the different tables, sometimes they are not all perfectly normalized! It's very frustrating, and in that case have a look in the triggers:

To List all triggers:

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'


Sometimes you need to have a printout of an "on delete trigger" to find out all the tables that are related to a certain tables, when foreign key isn't always defined.

Ah, and if there's a certain and not quite common data type that you need to find out about. I'm not talking about INT here, maybe it's XML or Money or in the following case, Varbinary:


select IC.TABLE_NAME, IC.COLUMN_NAME
 from INFORMATION_SCHEMA.COLUMNS IC where IC.DATA_TYPE = 'VARBINARY'


Ah, and BTW: SQL Server 2012 is FUN to work with, you're welcome to try out!

Calculating the Week Number for the Australian Financial Year

In Australia, the Financial Year Starts at the 1st of July.
In my cube, they wanted to browse by the week by of the Financial Year.
I've created two new columns: one for the Week of the Year, the other for the Week of the Financial year.

While the first column is easily populated

UPDATE dimDate
SET WeekYear = datepart (ww, myDateFormat)


The second one is a bit more complicated:
The year start at 1/7/2013, so I want that week to start as 1. So I need the WeekYear - week number of 1/7/2013.
I added 1 because we start at 1, not 0.


UPDATE [dimDate]
WeekFinancialYear = WeekYear - DATEPART (ww, CAST (CAST([CalanderYear] AS CHAR(4)) + '0701' AS DATE)) + 1
WHERE [month] >=7


But that's for months in the beginning of the financial year, months that their number is bigger than 7.

Otherwise, Just add 26 (this is the number of 1/2 a year).


UPDATE dimDate
SET WeekFinancialYear = WeekYear + 26
WHERE [month] < 7






(BTW if you're looking for a job here down under, between the last week of June and the First week of August then you're quite hopeless. Or Week 52 till week 5 of the Financial Year!).

Getting empty values with Sum, Left Join and Inner Join

I'm now working on a simple Training Record database. It's a bit of a downgrade from BI. It's a database with basically 3 tables:
Learners (=the employees in the company)
Courses
Participation (which learner took which course when).

My architecture was based on SQL server as the back end and MS-access as the front end. It makes the database robust and the front end design quick (though you have to pay a price for that, and about that in another post). Another tip is if you don't get the result set that you want running a query in MS-Access it's always easier to investigate the situation on the SQL itself.

The Participation requires the following fields: Learner, Course AND CPD, which is the number of points you get from this course. It could be less than the number of points that the Course acquires.

Sounds simple enough, doesn't it? Well, you'd be surprised how many problems can one encounter with a simple database like that.

Business asked for a simple request: when they see all the Learners, they want to see those without any courses as well. The problem is outlined below:



If you run the following query:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN dbo_Participation ON LearnerList.[id] = dbo_Participation.[Student]
WHERE (( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;


That gives me all the required participants, including those who has NULL as sumOfCPD

FullName            SumOfCPD  id
Amanda Smith         5.75        5
Amanda Jones         24.00       6
Catherine Smith NULL     7
John Smith          NULL      34



BUT, if I try to manipulate it to a certain time range, those didn't take any courses (or in SQL: they're SumOfCPD is NULL) disappears:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON  LearnerList.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;


FullName            SumOfCPD  id
Amanda Smith         5.75         5
Amanda Jones         24.00        6


If you move the condition of the "Where" clause back to the "Join" part, that sort out the problem:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD
FROM LearnerList LEFT JOIN dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id
AND  (dbo.course.Date)<='20131115' And (dbo.course.Date)>='20130701')
ON dbo.student.[id] = dbo.Participation.[Student]
WHERE
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student;


Results:
FullName            SumOfCPD  id
Amanda Smith         5.75         5
Amanda Jones         24.00       6
Catherine Smith NULL     7
John Smith          NULL     34

Running the above script in MS-Access would give me the "Error in the FROM clause". The above script run directly from the SQL.
Another option it to use the "NOT EXIST", which exist in MS-Access.
In this example, as I created the SQL as a string in the VBA engine, I didn't want to have the Date as a parameter of the JOIN, as I didn't know how Access would handle it (I wouldn't take a wild guess saying it wouldn't, would I?)


SELECT T1.FullName, T1.id
FROM LearnerList  T1
WHERE  NOT EXISTS
(SELECT T2.FullName,
Sum(dbo.Participation.CPD) AS SumOfCPD
FROM dbo.LearnerList  T2 LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON T2.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND ((T2.id)=5 Or (T2.id)=6 Or (T2.id)=7 Or (T2.id)=34)
AND T1.id = T2.id

GROUP BY T2.LearnerList , dbo.Participation.Student)
AND
(T1.id = 5 OR T1.id = 6 OR t1.id = 7 OR t1.id = 34)


It's important to note that correctly, if run in SQL Server, you'd get the warning of:
Warning: Null value is eliminated by an aggregate or other SET operation.

For all of the above queries except for the "not exist" one.

In MS-Access, on the other hand, even creating a sub-query wouldn't help. You'll get and "ODBC - Call Error" just from the LEFT JOIN. In INNER JOIN you wouldn't get any error.

And last but not least: as this had to be read as a report, and when you do the following

Dim RS As New ADODB.Recordset
'Execute the code to set the RS
Set Reports("rpt_participation_summary").Recordset = RS

You get "You can do this type of action only in an ADP" I just used "SELECT...INTO "a table which is "temporary" on the SQL server. Ah, the joys of using Access! remind me NEVER, but NEVER to do it again!

How To: extract from an undocumented database

Here's the problem which you encounter:
There's a database in your company that you need to extract data from. They  want to make that database redundant (and everybody mumbles around you "it's about time!") and you, as the database-know-how, required to create a report with the required fields.
I've done this daunting task twice: first, for a front end that run on Win 95 (in 2008!) That database was on a Syabase database, and believe me, that line isn't even on my CV. The second time I run it from MS-Access, and that was a way more pleasant experience. Those database had couple of things in common, and the most obvious one that they were shelf products, not custom made, and therefor had many tables that weren't actually in use.
And these are the steps I took:
First, ask your Business Analyst (or whomever had given you this task) which fields they need. They can usually give you at least one line of data, some names, etc, which could give you your reference point for investigation.
The second stage for me was to migrate the data to MS-SQL. This stage isn't strictly necessary; after all, if you can query one database you can query them all. But using my own local version has the benefit of (a) there's not a chance I'll mess up the data and (b) I'm using the SQL version I know well with its new features, and that enables me to focus on the problem, not on the differences between different SQL versions.
Now it's time for querying, but before that, a little script could help.
How do you get the size of all the tables in a database?

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnuasedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

The benefit of this script is clear: in a shelf database there could be heaps of tables that are not even in use. When you start investigating you don't need those. From my experience, start with the second largest table. The largest one is usually a log, and doesn't usually contain any necessary information. Of course you can ignore all the ones that have 0 data or only one line...

And now for the investigation itself. Look up that name. Look up those details. Try to locate foreign keys and indexes. That database was written by a human who has logic: how does that person's logic works?
Sure enough, you'll be able to present some results for your Business Analyst. They will probably be very happy with your results, and only have some comments. Modify the original script, and believe me, they'll be happy. Very happy, even.

How to find out how many decimals are in a column

This is the script to finding out the maximum number for a decimal in a column (how many decimals are there after the decimal point)

SELECT
MAX(LEN(CAST([aFloatColumn]AS VARCHAR(255))) - CHARINDEX('.',CAST([aFloatColumn]AS VARCHAR(255))))

FROM[dbo].[table_1]

Update on a linked table failed

The problem:
"Update or insert of view or function 'function_name' failed because it contains a derived or constant field (#4406)"
in my access database which is linked to SQL server

The reason:
The underlying view is of type "union"

The solution:
change to a single source (not a union select)

String or binary data would be truncated

Scuffling with ‘String or binary data would be truncated’

While the above post was absolutely right about this problem, origins & solution, mine was a bit different:
The solution lay not in the table, but with the table audit. While the description column was defined as [varchar (30)] in the original table, in the Audit table it had to be settled with [Varchar (20)]. Obviously, it wasn't enough: you give the user room to write more in the description column, and they'll use it.

(Yes, of course I had to put the description in the audit table, as the definitions of what's being audit changes all the time)

summing up decimals

I've just created a view that summs up percent value, that is saved in my SQL back-end as Decimal (5,4).
It resulted in the datatype (DECIMAL (38, 4)). Those 37 non-necessary digits were read by the Access database as Text, not number. If the number is treated as Text, then that causes ceveral problems to the access database: for example, it cannot format it as percent.
Since I don't really need those 37 digits, I've changed it to Decimal (6,4) (with an extra digit just to be on the safe side ; the sum shouldn't anyway exceed the 100%) and re linked the table. That sort this problem out.