27 November 2013

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!).

15 November 2013

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!