Bits and Pieces of IT
A lil bit about this, a lil bit about that
Thursday, May 1, 2014
Make a So-Called Scanned Copy Document with Your Phone
Thursday, February 20, 2014
Calculate Working Hours Exclude Holiday and Various Breaks
I again modified the SQL and here are they ...
Create holiday table:
CREATE TABLE [dbo].[Holiday]( [CalID] varchar(2),Here where you define the calendar. If there are many different calendars, you might want to create table for them?
[Holiday] [smalldatetime] NULL)
GO
INSERT INTO Holiday VALUES ('ID', '2014-01-01')
ALTER FUNCTION [dbo].[fn_CalendarTable]Here the main part, calculate working hours:
(@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')
*/
ALTER FUNCTION [dbo].[fn_BusinessHoursInTimeSpan]If you need to sum, here it is:
(@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')
*/
ALTER FUNCTION dbo.fn_BusinessHoursInTimeSpanSumEnjoy!
(
@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
*/
Saturday, February 15, 2014
Calculate Working Hours Between Two Dates Excluding Lunch Break andHolidays
CREATE TABLE [dbo].[Holiday]( [Holiday] [datetime] NULL)
INSERT INTO Holiday VALUES ('2014-01-14')
-- you may add all holidays afterwards
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
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')
*/
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
*/
Tuesday, December 24, 2013
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.
Sunday, December 1, 2013
Help! My notebook needs paracetamol ... It's having fever! (translate: notebook gets overheat)
Wednesday, August 29, 2012
How to Transpose Internal Table in SAP ABAP?
Tuesday, August 28, 2012
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!
Thursday, June 21, 2012
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!
Tuesday, May 8, 2012
How to Sort Data in Excel 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!