Make a So-Called Scanned Copy Document with Your Phone

Sometimes when you deal with online registration you need to attach scanned copy of your printed out documents, like your birth certificate or your ID card. If you have a scanner there is no issue with that. The trouble comes when you don't. 

What can you do when you don't have a scanner? Here they are:

1. If you have iPhone you can download application namely 'My Scans'. This application capture your document using the camera then it edits the picture as if it was taken by a scanner. I'm not sure with Android but you can search for similar application. This application is free but it has watermark on the document. To remove it you have to do one time payment. 

2. If you have phone with camera you can take the picture and use MS. Office like Excel to edit it to be black and white and adjust whatever format necessary. 

There you are, the scanned copy document is ready. 

Sample from My Scans:

Calculate Working Hours Exclude Holiday and Various Breaks

Previously I posted about how to calculate working hours between two dates by excluding holidays and lunch break in SQL. It turns out that the requirement is expanded that I also need to consider if there are some days that have not lunch break. I also need to consider different office hours for different countries/companies.

I again modified the SQL and here are they ...

Create holiday table:

CREATE TABLE [dbo].[Holiday](    [CalID] varchar(2),  
                                [Holiday] [smalldatetime] NULL)
GO
INSERT INTO Holiday VALUES ('ID', '2014-01-01')

Here where you define the calendar. If there are many different calendars, you might want to create table for them?

ALTER FUNCTION [dbo].[fn_CalendarTable]
(@CalID VARCHAR(2), @StartDate DATETIME, @EndDate DATETIME)
RETURNS
    @Return TABLE (
        [Date]         DATETIME     NULL,
        [Day]          INT          NULL,
        [WorkingHours] FLOAT        NULL,
        [BeginTime]    VARCHAR (50) NULL,
        [EndTime]      VARCHAR (50) NULL,
        [BeginBreak]   VARCHAR (50) NULL,
        [EndBreak]       VARCHAR (50) NULL)
AS
BEGIN
       DECLARE @CurrentDate datetime
 
       SET @CurrentDate = CONVERT( varchar, @StartDate , 101)
 
       WHILE(@CurrentDate < @EndDate)
       BEGIN
 
              DECLARE @WorkingHours float
              DECLARE @Day int
              DECLARE @BeginTime varchar(50)
              DECLARE @EndTime varchar(50)
              DECLARE @BeginBreak varchar(50)
              DECLARE @EndBreak varchar(50)
 
              SET @Day = datepart(dw, @CurrentDate)
              IF @CalID = 'ID'
              BEGIN
                  IF @Day = 1 --Sunday
                         BEGIN
                               SET @WorkingHours = 0
                               SET @BeginTime = ''
                               SET @EndTime = ''
                               SET @BeginBreak = ''
                               SET @EndBreak = ''
                         END
                  ELSE IF @Day = 6 OR @Day = 7 --Friday and Saturday
                         BEGIN
                                 SET @WorkingHours = 4.5
                               SET @BeginTime = '7:30:00 AM'
                               SET @EndTime = '12:00:00 PM'
                               SET @BeginBreak = '12:00:00 PM'
                               SET @EndBreak = '12:00:00 PM'
                         END
                  ELSE --Monday - Thursday
                         BEGIN
                                 SET @WorkingHours = 7.5
                               SET @BeginTime = '7:30:00 AM'
                               SET @EndTime = '3:30:00 PM'
                               SET @BeginBreak = '12:30:00 PM'
                               SET @EndBreak = '1:00:00 PM'
                         END
               END
               ELSE --WHEN 'IN'
               BEGIN
                  IF @Day = 1 OR @Day = 7
                         BEGIN
                               SET @WorkingHours = 0
                               SET @BeginTime = ''
                               SET @EndTime = ''
                               SET @BeginBreak = ''
                               SET @EndBreak = ''
                         END
                  ELSE
                         BEGIN
     
                               SET @WorkingHours = 8
                               SET @BeginTime = '8:30:00 AM'
                               SET @EndTime = '5:30:00 PM'
                               SET @BeginBreak = '12:30:00 PM'
                               SET @EndBreak = '1:30:00 PM'
                         END
                END

              INSERT INTO @Return ([date], [day], WorkingHours, BeginTime, EndTime, BeginBreak, EndBreak)
              VALUES (@CurrentDate, @day, @WorkingHours, @BeginTime, @EndTime, @BeginBreak, @EndBreak)
 
              SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
       END
 
       RETURN
END

/*
SELECT * FROM dbo.fn_CalendarTable('ID', '2014-02-08 08:30', '2014-02-14 10:30')
*/

Here the main part, calculate working hours:

ALTER FUNCTION [dbo].[fn_BusinessHoursInTimeSpan]
(@CalID VARCHAR(2), @StartDate DATETIME, @EndDate DATETIME)
RETURNS TABLE
AS
RETURN(
       SELECT [date], [day], WorkingHours, BeginTime, EndTime, BeginBreak, EndBreak,
       CASE WHEN h.holiday IS NULL THEN 0 ELSE 1 END Holiday,
       CASE
--            is a bank holiday so nothing will be worked for this day
              WHEN h.holiday IS NOT NULL THEN
                     0

--            started after business hours so ignore this day
              WHEN @StartDate > date + EndTime THEN
                     0

--            ended before business hours on last day so ignore this day
              WHEN @EndDate > date and @EndDate < date + BeginTime THEN
                     0

--            started and ended on the same day within business hours
              WHEN @StartDate > date + BeginTime AND @EndDate < date + EndTime THEN
                CASE 
            --    started/ended before break time or started/ended after break time
                 WHEN (@StartDate <= date + BeginBreak AND @EndDate <= date + BeginBreak)
                  OR  (@StartDate >= date + EndBreak AND @EndDate >= date + EndBreak) THEN
                    DATEDIFF(minute, @StartDate, @EndDate) / 60.0
            --    started/ended within break time
                 WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak 
                  AND @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak THEN
                    0
            --    started within break time
                 WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak 
                  AND @EndDate >= date + EndBreak THEN
                    DATEDIFF(minute, date + EndBreak, @EndDate) / 60.0
                 WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak
                  AND @StartDate <= date + BeginBreak THEN
                    DATEDIFF(minute, @StartDate, date + BeginBreak) / 60.0
                 ELSE
                    DATEDIFF(minute, @StartDate, @EndDate) / 60.0 - DATEDIFF(minute, BeginBreak, EndBreak) / 60.0
                END

--            this is the first day so this will be less than a normal working day
              WHEN @StartDate > date + BeginTime THEN
                CASE
                    WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak THEN
                        DATEDIFF(minute, date + EndBreak, date + EndTime) / 60.0
                    WHEN @StartDate <= date + BeginBreak THEN
                        DATEDIFF(minute, @StartDate, date + EndTime) / 60.0 - DATEDIFF(minute, BeginBreak, EndBreak) / 60.0
                    ELSE
                        DATEDIFF(minute, @StartDate, date + EndTime) / 60.0
                END

--            this is the last day so this will be less than a normal working day
              WHEN @EndDate < date + EndTime THEN
                CASE
                    WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak THEN
                        DATEDIFF(minute, date + BeginTime, date + BeginBreak) / 60.0
                    WHEN @EndDate >= date + EndBreak THEN
                        DATEDIFF(minute, date + BeginTime, @EndDate) / 60.0 - DATEDIFF(minute, BeginBreak, EndBreak) / 60.0
                    ELSE
                        DATEDIFF(minute, date + BeginTime, @EndDate) / 60.0
                END

              ELSE
                    WorkingHours
       END WorkedHours
        , @StartDate StartDate, @EndDate EndDate
       from dbo.fn_CalendarTable(@CalID, @StartDate, @EndDate) cal
       left outer join Holiday h on cal.[date] = h.holiday AND h.CalID = @CalID
       )
GO

/*
SELECT * FROM dbo.fn_BusinessHoursInTimeSpan('ID', '2014-01-30 12:30', '2014-02-01 14:30')
*/

If you need to sum, here it is:

ALTER FUNCTION dbo.fn_BusinessHoursInTimeSpanSum
(
    @CalID VARCHAR(2),
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @Hours FLOAT
    SELECT @Hours = ROUND(SUM(WorkedHours),2) from dbo.[fn_BusinessHoursInTimeSpan](
        @CalID, @StartDate, @EndDate)
    RETURN @Hours
END

/*
SELECT dbo.fn_BusinessHoursInTimeSpanSum('ID', '2010-10-06 08:45', '2010-10-06 19:30') AS TotalHoursWorked
*/ 

Enjoy!

Calculate Working Hours Between Two Dates Excluding Lunch Break andHolidays

I got the ideas of the SQL stored procedures (SPs) for calculating working hours between two dates originally from someone's blog. I tried to re-search the blog to mention the link here, but to no avail. Anyway, thank you very much to the owner. If you read this please do let me know. 

Nevertheless, the SPs don't meet my exact requirement. Beside considering holidays I also need to consider the lunch break. So I modified the original stored procedures a bit. 

Firstly, create SP to create holiday tables. 

CREATE TABLE [dbo].[Holiday]( [Holiday] [datetime] NULL)

INSERT INTO Holiday VALUES ('2014-01-14')
-- you may add all holidays afterwards

Secondly, create SP to define the business hours and lunch break.

ALTER FUNCTION [dbo].[fn_CalendarTable]
(@StartDate DATETIME, @EndDate DATETIME)
RETURNS
    @Return TABLE (
        [Date]         DATETIME     NULL,
        [Day]          INT          NULL,
        [WorkingHours] FLOAT        NULL,
        [BeginTime]    VARCHAR (50) NULL,
        [EndTime]      VARCHAR (50) NULL,
        [BeginBreak]   VARCHAR (50) NULL,
        [EndBreak]       VARCHAR (50) NULL)
AS
BEGIN
       DECLARE @CurrentDate datetime
 
       SET @CurrentDate = CONVERT( varchar, @StartDate , 101)
 
       WHILE(@CurrentDate < @EndDate)
       BEGIN
 
              DECLARE @WorkingHours float
              DECLARE @Day int
              DECLARE @BeginTime varchar(50)
              DECLARE @EndTime varchar(50)
              DECLARE @BeginBreak varchar(50)
              DECLARE @EndBreak varchar(50)
 
              SET @Day = datepart(dw, @CurrentDate)
 
              IF @Day = 1 OR @Day = 7
                     BEGIN
                           SET @WorkingHours = 0
                           SET @BeginTime = ''
                           SET @EndTime = ''
                           SET @BeginBreak = ''
                           SET @EndBreak = ''
                     END
              ELSE
                     BEGIN
 
                           SET @WorkingHours = 8
                           SET @BeginTime = '8:30:00 AM'
                           SET @EndTime = '5:30:00 PM'
                           SET @BeginBreak = '12:30:00 PM'
                           SET @EndBreak = '1:30:00 PM'
                     END
 
              INSERT INTO @Return ([date], [day], WorkingHours, BeginTime, EndTime, BeginBreak, EndBreak)
              VALUES (@CurrentDate, @day, @WorkingHours, @BeginTime, @EndTime, @BeginBreak, @EndBreak)
 
              SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
       END
 
       RETURN
END

Third, create SP to span the dates and get the worked hours.

ALTER FUNCTION [dbo].[fn_BusinessHoursInTimeSpan]
(@StartDate DATETIME, @EndDate DATETIME)
RETURNS TABLE
AS
RETURN(
       SELECT [date], [day], WorkingHours, BeginTime, EndTime, BeginBreak, EndBreak,
       CASE WHEN h.holiday IS NULL THEN 0 ELSE 1 END Holiday,
       CASE
--            is a bank holiday so nothing will be worked for this day
              WHEN h.holiday IS NOT NULL THEN
                     0

--            started after business hours so ignore this day
              WHEN @StartDate > date + EndTime THEN
                     0

--            ended before business hours on last day so ignore this day
              WHEN @EndDate > date and @EndDate < date + BeginTime THEN
                     0

--            started and ended on the same day within business hours
              WHEN @StartDate > date + BeginTime AND @EndDate < date + EndTime THEN
                CASE  
            --    started/ended before break time or started/ended after break time 
                 WHEN (@StartDate <= date + BeginBreak AND @EndDate <= date + BeginBreak) 
                  OR  (@StartDate >= date + EndBreak AND @EndDate >= date + EndBreak) THEN 
                    DATEDIFF(second, @StartDate, @EndDate) / 60.0  / 60.0 
            --    started/ended within break time 
                 WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak  
                  AND @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak THEN 
                    0 
            --    started within break time 
                 WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak  
                  AND @EndDate >= date + EndBreak THEN 
                    DATEDIFF(second, date + EndBreak, @EndDate) / 60.0  / 60.0 
                 WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak 
                  AND @StartDate <= date + BeginBreak THEN 
                    DATEDIFF(second, @StartDate, date + BeginBreak) / 60.0  / 60.0 
                 ELSE 
                    DATEDIFF(second, @StartDate, @EndDate) / 60.0  / 60.0 - 1 
                END 

--            this is the first day so this will be less than a normal working day
              WHEN @StartDate > date + BeginTime THEN
                CASE
                    WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak THEN
                        DATEDIFF(second, date + EndBreak, date + EndTime) / 60.0 / 60.0
                    WHEN @StartDate <= date + BeginBreak THEN
                        DATEDIFF(second, @StartDate, date + EndTime) / 60.0 / 60.0 - 1
                    ELSE
                        DATEDIFF(second, @StartDate, date + EndTime) / 60.0 / 60.0
                END

--            this is the last day so this will be less than a normal working day
              WHEN @EndDate < date + EndTime THEN
                CASE
                    WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak THEN
                        DATEDIFF(second, date + BeginTime, date + BeginBreak) / 60.0 / 60.0
                    WHEN @EndDate >= date + EndBreak THEN
                        DATEDIFF(second, date + BeginTime, @EndDate) / 60.0 / 60.0 - 1
                    ELSE
                        DATEDIFF(second, date + BeginTime, @EndDate) / 60.0 / 60.0
                END

              ELSE
                    WorkingHours
       END WorkedHours
        , @StartDate StartDate, @EndDate EndDate
       from dbo.fn_CalendarTable(@StartDate, @EndDate) cal
       left outer join Holiday h on cal.[date] = h.holiday
       )
GO

/*
SELECT * FROM dbo.fn_BusinessHoursInTimeSpan('2010-10-06 09:00', '2011-03-29 13:30')
*/

If you need to sum, then create SP to sum it.

ALTER FUNCTION dbo.fn_BusinessHoursInTimeSpanSum
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @Hours FLOAT
    SELECT @Hours = ROUND(SUM(WorkedHours),2) from dbo.[fn_BusinessHoursInTimeSpan](
        @StartDate, @EndDate)
    RETURN @Hours
END

/*
SELECT dbo.fn_BusinessHoursInTimeSpanSum('2010-10-06 08:45', '2010-10-06 19:30') AS TotalHoursWorked
*/

Voila, enter the start date, end date and you'll get SQL calculating the business hours for you. 

When it comes to evaluating SLA or KPI this SPs are very useful.

How to Debug an SQL Stored Procedure

Have you come across this situation where you need to know in which statement the stored procedure executed in SQL gives you the slow performance?

If you have and you still can't find the answer, then you come to the right page.

This is how to debug SQL stored procedure:
1. Open SQL Server Profiler.
2. Click New Trace and enter the connection you want.
3. You will get "Trace Properties" window. In the second tab, "Event Selection", click "Show all events".
4. You will get the whole events. But no, you don't want all actually. So scroll down to the 'Stored Procedures" row. Tick "SP:StmtStarting" and "SP:StmtCompleted".
5. Click Run.

After activating the profiler, go back to the SQL Management Studio and run the SP you want to debug. The profiler will show you statement by statement executed by the SP.

Help! My notebook needs paracetamol ... It's having fever! (translate: notebook gets overheat)

Couple months ago my office notebook Lenovo Y400 got overheat that it shut down itself without even asking for my permission. Not even a single word!

I reported it to our hardware support. Suspecting it could be because of failed internal fan I suggested him to buy an external fan for laptop where I can place it at the bottom of my notebook. 

It did help a lot. For some months the frequency of overheat was less than previously. Nevertheless, now and then it still happened for some reasons that I couldn't figure it out. Maybe it was tired. Maybe it hated me for working too hard. Ha!

And then, recently it's getting worse. Even with the external fan plugged in, the notebook got overheat and shutdown like crazy. The dosage was almost like a baby. Every 3 hours it cried.  

But hey, you're wrong, I'm not a lady who gives up easily. To me, it's a sign that I should prepare a cup of coffee and observe the notebook. Watching it eye to eye (it's the troubleshooter in me!).

So there I was. With a cup of coffee. Started to observe. First of all, I installed a software called Core Temp so that I could monitor the temperature before it shut down. I noticed that it got overheat after some minutes after I plugged in the AC adapter. Noted. First clue. 

At first, I suspect it's related with hardware. Could be the battery. Or AC adapter. But simulating by removing the battery and also simulating charging when it was off proved me wrong.

On further observation, from that software I noticed that it was the CPU load that triggered the temperature to reach the maximum temperature i.e. 85 C. Noted. Second clue. 

I tried to change the msconfig and restart the notebook in diagnostic mode. Even with only basic services the notebook gots overheat. Third clue. And I needed more coffee. 

I run task manager, sorted the processes by CPU load column to observe which services were on top five. The Idle service will always be number one. So pay attention to the number two. It turned out that it was the index service that most of the times was the runner-up. This service is for indexing the files so that when we do search the result is faster. Somehow in my notebook this service was on. I didn't really need it actually. So I stopped it. 

And voilaaa ... my fever-notebook was cured!

I should have known it. Overheat was not always a hardware issue. There was unexpected service run automatically like crazy only when the notebook was plugged in. The index service loaded CPU worse by time because my files were also grown by time. 

Well, I have saved company money bcos I almost asked company to replace it. I should have been given an extra bonus! :)

How to Transpose Internal Table in SAP ABAP?

Suppose you have one internal table with 12 rows and 15 columns, and you want to transpose it to become 15 rows and 12 columns, how to do it in ABAP? In Excel it can be done easily by simply copy the table and select Paste Special and tick Transpose option.

Will it be such easy in SAP? Things can be easier if you do it with your heart. I heart you, internal table.

So, you're gonna need field-symbols, of course. And loop (can't live without it!). And one magic elegant word like 'ASSIGN COMPONENT idxcol OF STRUCTURE itab TO thefieldsymbol'.

Here is the sample of the codes:

Never Thought that Adding Space in Text in Smartforms is That Easy!

Have you ever crossed loosing white space you add inside the texts on generating reports created in the SAP smartforms? I have. I was wondering why smartforms doesn't like space. What did space do wrong? Why smartforms swallow spaces whenever I add it in the text to make indent effect.

Don't get frustated. It's very easy to add space. Yeah, it's easy after you know it. Type the spaces inside the text variable with quotes like:
&' 'myvariable&

Never thought it's that easy? Neither have I!

IT Department Role in a Company: as a Sales Promotion Girl or Customer Service?

There's no doubt (well, at least for me) if a company wants to move forward faster it should be supported by IT. Can you imagine a big company that does the whole things manually, in this information age? Reporting, accounting, calculating, monitoring, payroll processing with an Excel? I can't.

There's no doubt that when it comes to service, IT department in a company should provide service to other departments in the same company, and do the best to make they're fully satisfied. The service that fulfills all requirements from end users.

Now the question is, who should initiate the requirements?

Should IT department serve end users by agressively asking them, what you need, what you want, and also showing them this is what we have, what we achieve, do you want it, just like a Sales Promotion Girl?

Or should IT department serve end users by following up what end users report to IT, and provide services upon the reported matters, just like a Customer Service?

Sometimes people are mislead. They think IT department should be like Sales Promotion Girl. People think that they only need to wait, and taddaaa ... IT department will know what they need and organize things to make it happen. If you think that way, please oh please, brainwash your mind. IT department is not like an SPG. You don't want employees of IT dress up very sexy, stand up in front of your doors and offer IT products to you, right?

It's your call. If you need something, tell IT. Then start from there. You can't expect IT to know all you need without letting IT know. IT doesn't have that fortune-teller ball. But IT does have a Helpdesk where you can post everything you want. Everything you need. It's just a matter of clicking Send button. And if it's still too much for you, you can hire office boy to click that button for you!

How to Sort Data in Excel Pivot Table

I presume you already know how to create pivot table in Excel (Data>Pivot Table and Pivot Chart Report). So this posting is only to show you how to sort the data displayed in the pivot table, automatically. In other words, you don't have to sort them each time you refresh the pivot table.
This is how to do it. First, you right click the column header that you want to sort (on the left side of the table). Then click 'Field Settings'. Click 'Advanced'. Do you see 'AutoSort options' there? Yes, correct ... that's the place you can play around with. You can select 'Ascending' or 'Descending', depend on what you need. You can even select 'Top 10 AutoShow' there so that Excel will show you only the top 10!