Articles - Joel Lipman .Com

Difference between two dates - the midnight hour

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

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: 17362

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: 11769

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: 8210

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: 38713

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: 24832

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: 23966

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: 11446

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: 7749

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: 43753

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!

PHP & MySQL Search Engine

: Joel Lipman; Personal Home Page; 09 March 2011; Hits: 7326

Do you hate sites that do not have a search feature? I do. I think it defeats the purpose of cramming information endlessly in cyberspace.

So we want to give a search engine to our users. This sounds really simple, we could try:

copy raw

$search_term_esc = AddSlashes($search_term);
$sql = "SELECT * FROM Content WHERE content_body LIKE '%$search_term_esc%'";

$search_term_esc = AddSlashes($search_term);
$sql = "SELECT * FROM Content WHERE content_body LIKE '%$search_term_esc%'";

Great! Few problems though, multiple terms are not supported; quotation marks and apostrophes may be an issue;

Category: Personal Home Page :: Article: 328

: Joel Lipman; Web-Development; 01 March 2011; Hits: 9140

So I'm starting to get the impression that I no longer tender for brand new projects competing with time and cost in order to win the bids. Recently, more and more people have been asking if I can take over a project that has been abandoned by its developer.

I've started writing this article because I find myself forgetting to ask something that could have saved an infinite number of man-hours (I exaggerate a little); but seriously, I needed a checklist that works for me.

Most Difficult?
My hardest project was (still is) one created by a PhD student who graduated and left the institution. It was a custom-built site written from scratch, and it's purpose was to manage Staff and Students within the Additional Learning Needs group. These are staff who take lecture notes for people of all disabilities and assist the students for the duration of their course. It had to allow for synchronizing with the official student record system and timetabling system (done via file upload). It included it's own timesheet / session management / invoicing system.

The website was held on a virtual host running PHP and MySQL. There was no documentation, logs, notes, and any code comments were in Hungarian (later found out it was slang or a dialect not understood by most Hungarians). Fellow students and system administrators could not accurately describe what the system was for and what it does. The developer had created the system to only last during one academic year, and the system itself only just about understood academic years (required tweaking twice a year). Then there were the error logs... some 20000 errors per use of a feature over 4 seconds. Do some developers never check the errors log?

By the time, I started maintaining the project, a revamp had been agreed with another web team. This has been delayed somewhat and still after 2 years there is no new site (blamed on the customer for not knowing what their application did in the first place... tut tut. How long have you been a developer? And this is new?).

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support