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.