Articles - Joel Lipman .Com

Inserting incremental weeks in MySQL

: Joel Lipman; MySQL; 15 April 2011; Hits: 7572

Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.

The current structure looks similar to this:

copy raw

ID        SetID            WeekNumber             StartDate
--------- ---------------- ---------------------- ----------------------------
1         2011/2012        1                      2011-07-18 00:00:00
...
52        2011/2012        52                     2012-07-09 00:00:00

ID SetID WeekNumber StartDate
--------- ---------------- ---------------------- ----------------------------
1 2011/2012 1 2011-07-18 00:00:00
...
52 2011/2012 52 2012-07-09 00:00:00

Joe you're an idiot!
You might say to me why not run the CMIS Facility application and add a new set, it will put these dates in automatically.

Herein lies the problem
The reason I'm doing this is for another system which decided to "cleverly" use the exports from CMIS Facility so that all the weeks correspond to the rest of the academic data. Unfortunately the developer wrote a system he felt would last the rest of his PhD degree, it's a shame he started in his last year. He used functions to mktime and simulate the dates. A function goes in with a normal calendar date and returns an academic week number and the week commencing date.

Problem?
The 1st of January 2010 was a Friday. The 1st of January 2011 was a Saturday. The developer felt that as long as you adjust the script each year you could make the system last another year. Shame he also forgot the academic year ends halfway in a normal people's calendar, so you actually have to adjust this twice a year.

Category: MySQL :: Article: 342

: Joel Lipman; Web-Development; 14 April 2011; Hits: 4892

To be honest I saw this on some Joomlaworks Blog who appropriately title it "A must see for professional webdesigners". If you're a developer relying on clients for your income, then at least watch some of this.

March 2011 San Francisco, CreativeMornings (creativemornings.com) was Mike Monteiro, Design Director, and co-founder of Mule Design Studio (muledesign.com). This event took place on March 25, 2011 and was sponsored by Happy Cog and Typekit (who also hosted the event at their office in the Mission).

2011/03 Mike Monteiro | F*ck You. Pay Me. from SanFrancisco/CreativeMornings on Vimeo.

Difference between two dates - the midnight hour

: Joel Lipman; Web-Development; 06 April 2011; Hits: 6883

I might already have something similar to this but this deserves its own article. Why? Well try to search the web for a PHP/MySQL solution which suggests on how to count the hours between two times on the same date, when one of the hours is on the other side of the midnight hour...

Now bear in mind the below is in European date format

Consider the following:

copy raw

Date       Customer        Time From       Time To          Hours
---------- --------------- --------------- ---------------- --------------
04/03/2011 Tweedle Dee     10:00           12:00            2.00
08/03/2011 Tweedle Dum     23:30           00:30            -23.00

Date Customer Time From Time To Hours
---------- --------------- --------------- ---------------- --------------
04/03/2011 Tweedle Dee 10:00 12:00 2.00
08/03/2011 Tweedle Dum 23:30 00:30 -23.00

Note the last row is obviously incorrect. This is because the script is not changing the date at the stroke of midnight. Take the last row as an example, the equation that's happening is:

copy raw

$thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession']));

$this_time_from1 = date("H:i", strtotime($sub_row['TimeFromSession']));
$this_time_to1 = date("H:i", strtotime($sub_row['TimeToSession']));

$this_time_from_sql=$thisDateSQL." ".$this_time_from1.":00";
$this_time_to_sql=$thisDateSQL." ".$this_time_to1.":00";

$sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2);

// Using the examples above this is doing the following:
2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00
2011-03-08 00:30:00 - 2011-03-08 23:30:00 = -23.00

$thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession']));
$this_time_from1 = date("H:i", strtotime($sub_row['TimeFromSession']));
$this_time_to1 = date("H:i", strtotime($sub_row['TimeToSession']));
$this_time_from_sql=$thisDateSQL." ".$this_time_from1.":00";
$this_time_to_sql=$thisDateSQL." ".$this_time_to1.":00";
$sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2);
// Using the examples above this is doing the following:
2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00
2011-03-08 00:30:00 - 2011-03-08 23:30:00 = -23.00

This is great for everything during that date as long as the "To Date" never goes past midnight into the next day... But what system doesn't do this (no Microsoft jokes please)?

Category: Web-Development :: Article: 339

: Joel Lipman; SQL Server Reporting Services; 05 April 2011; Hits: 17469

Situation
The requirement in a lot of my reports in SSRS has always been a date range rather than just the one date. The user must be allowed to select all results between two dates instead of just specifying the one day. If the second parameter (ToDate) is the same as the first (FromDate), then the range is for that particular date.

Solution

: Joel Lipman; SQL Server Reporting Services; 04 April 2011; Hits: 11828

This must have been so obvious to everyone else that nobody bothered to write an article on it... till now.

Situation
I have a report that returns room bookings based on a user and given a date range. The problem is that there are a few thousand users and Microsoft's SQL Server Reporting Services interface isn't the most fun to scroll endlessly down. Advanced users can type the name really fast for it to auto-scroll down to the desired name. Our advanced users are exceptions to the rule.

Problem

: Joel Lipman; SQL Server Reporting Services; 01 April 2011; Hits: 8266

Open a Windows Explorer and make a duplicate of the report that you want to use as a template.
Rename the copy (suffix with template?)
Remove objects unique to the report and leave elements for all reports.
File > Save selected item as...
For SSRS 2008: Save in the folder: "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject"
Done!

SSRS Hide results table if empty

: Joel Lipman; SQL Server Reporting Services; 29 March 2011; Hits: 38839

Update August 2011
So I don't know how many people were just saying why don't you do the following:

Bring up the "Tablix Properties" of the dataset
Look for the section "No Rows"
Put in a value for "NoRowsMessage".

This solution hides the entire Tablix which kinda defeats the purpose of having anything there. I want my end-users to have faith in my report. The report just returning blank can also mean "I don't know". I want the report to either return the correct value or say "No data found". My end-users would otherwise ask is the data value reliable if it's blank?

The long way of doing this
I would have called this article "Iteration within SQL Server Reporting Services Business Intelligence Development Studio (BIDS) version 2008 through combining Transact-SQL and MDX expressions" but boy what a mouthful, and it's not really iterating anymore. So it's "SSRS Hide results table if empty" though I will add that if you wanted to put a message instead of hiding the table then following the below will also let you do this (requirements: common sense or the IQ of a duck).

It's what I've been searching for for the past hour and although you may think I'm just adding to the cyberspace pile of useless info, at least I'm not just copying and pasting from other sites to add content to my own. And I'm not just adding content, the way I'm doing the below is nothing similar to what I googled (probably a bad sign but time is ticking and no one has forever).

: Joel Lipman; SQL Server Reporting Services; 25 March 2011; Hits: 24909

Re: Business Intelligence Development Studio 2008

Often happens to me and not sure why. You open the project and the window panel for the Report Data containing the parameters and datasets isn't there. I spent a bit of time enabling/disabling toolbars before I found this.

Open a Report solution (or have one open already?)
The menu link is "report data" and it's right at the bottom of the "View" menu.

Report Data at the bottom of the View menu when a report is loaded in the editor

Border settings not saving in SSRS

: Joel Lipman; SQL Server Reporting Services; 24 March 2011; Hits: 24171

This may sound silly to some but I've written a note because I spent time googling and still didn't find an answer.

My situation is that I wanted to put an expression to format the border (empty cells have a border and non-empty don't). This was so that a grid would show for empty spaces.

I wasn't getting very far because I'd even ask the border to be grey and save the project but when I come back to the setting, it's still black.

: Joel Lipman; SQL Server Reporting Services; 23 March 2011; Hits: 11500

Well I tried and I spent a few hours searching the net for a solution which displays the hours in the top columns, and undefined number of rooms in the rows. No all I was looking for was a calendar showing 1 day with the hours on the top. Working hours only as well...

Ok I admit I only googled for about 1 hour before I gave up and just wrote my own. Saying that it's taken me 4 hours just to do the below... hopefully it will be quicker next time now that I noted it all down.

: Joel Lipman; Transact-SQL; 23 March 2011; Hits: 7797

My setup

Windows XP SP3
MS SQL Server Management Studio 2008
SQL Server 2008 R2
Regional Setting: London BST (British Summer Time)

: Joel Lipman; Web-Development; 19 March 2011; Hits: 43788

Hi Everybody!

My name is Joe. I like to think of myself as a web-developer but then I like to get involved with anything related to computers. I've been writing websites and scripts commercially since 1997 and I'm using this website to store notes as I go along.

I specialise in taking over incomplete and/or bespoke systems which have little or no documentation. I like workarounds and solutions and persist in the knowledge that anything is possible, I only have to believe. I tend to write about issues that the rest of the crowd so easily give up on.

I am usually available for consultation and short term projects. If you think I can help you and your website then feel free to get in touch using my contact form.

Thanks for visiting! I hope this website is, as always, of some use to you!

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support