Tuesday, September 25, 2007

PIVOT in SQL Server

CREATE PROCEDURE [dbo].[spLoadEmpFiles]
@FromDate SMALLDATETIME = NULL,
@ToDate SMALLDATETIME = NULL,
@EmployeeCode NVARCHAR(50) = '',
@EmployeeName NVARCHAR(255) = '',
@CompanyName NVARCHAR(255) = '',
@DepartmentName NVARCHAR(255) = '',
@DeptUnitName NVARCHAR(255) = '',
@JobTitleName NVARCHAR(255) = '',
@TeamName NVARCHAR(255) = '',
@Gender NVARCHAR(1) = ''
AS
BEGIN
DECLARE @HRFileID INT
DECLARE @HRFileName NVARCHAR(50)

DECLARE Cur CURSOR FOR
SELECT HRFileID, HRFileName
FROM dbo.tblHRFile

OPEN Cur
FETCH FROM Cur
INTO @HRFileID, @HRFileName
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SET @strSQL = 'SELECT t1.EmployeeID, t1.EmployeeCode, t1.EmployeeName
, t1.Gender
, t4.CompanyName
, t5.DepartmentName
, t6.DeptUnitName
, t7.JobTitleName
, t8.TeamName '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = @strSQL +', MAX(CASE WHEN t3.HRFileID = ' + CAST(@HRFileID AS VARCHAR) + ' Then ''x'' ELSE '''' END) AS [' + @HRFileName + ']'

FETCH FROM Cur
INTO @HRFileID, @HRFileName
END
CLOSE Cur
DEALLOCATE Cur

SET @strSQL = @strSQL + ' FROM dbo.tblEmployee t1
LEFT JOIN dbo.fnAppoint(@ToDate) t2 ON t1.EmployeeID = t2.EmployeeID
LEFT JOIN (SELECT t31.EmployeeID, t31.HRFileID, t32.HRFileName
FROM tblEmployeeFile t31
INNER JOIN dbo.tblHRFile t32 ON t31.HRFileID = t32.HRFileID) t3 ON t1.EmployeeID = t3.EmployeeID
LEFT JOIN dbo.tblCompany t4 ON t2.CompanyID = t4.CompanyID
LEFT JOIN dbo.tblDepartment t5 ON t2.DepartmentID = t5.DepartmentID
LEFT JOIN dbo.tblDeptUnit t6 ON t2.DeptUnitID = t6.DeptUnitID
LEFT JOIN dbo.tblJobTitle t7 ON t2.JobTitleID = t7.JobTitleID
LEFT JOIN dbo.tblTeam t8 ON t2.TeamID = t8.TeamID
WHERE 1 = 1 '

IF LEN(@EmployeeCode) > 0
SET @strSQL = @strSQL + ' AND t1.EmployeeCode LIKE @EmployeeCode '
IF LEN(@EmployeeName) > 0
SET @strSQL = @strSQL + ' AND t1.EmployeeName LIKE @EmployeeName '
IF LEN(@Gender)> 0
SET @strSQL = @strSQL + ' AND t1.Gender LIKE @Gender '
IF LEN(@CompanyName) > 0
SET @strSQL = @strSQL + ' AND t4.CompanyName LIKE @CompanyName '
IF LEN(@DepartmentName) > 0
SET @strSQL = @strSQL + ' AND t5.DepartmentName LIKE @DepartmentName '
IF LEN(@DeptUnitName) > 0
SET @strSQL = @strSQL + ' AND t6.DeptUnitName LIKE @DeptUnitname '
IF LEN(@JobTitleName) > 0
SET @strSQL = @strSQL + ' AND t7.JobTitleName LIKE @JobTitleName '
IF LEN(@TeamName) > 0
SET @strSQL = @strSQL + ' AND t8.TeamName LIKE @TeamName '
SET @strSQL = @strSQL + ' GROUP BY t1.EmployeeID
, t1.EmployeeCode
, t1.EmployeeName
, t1.Gender
, t4.CompanyName
, t5.DepartmentName
, t6.DeptUnitName
, t7.JobTitleName
, t8.TeamName '

SELECT @params = N'@FromDate SMALLDATETIME
, @ToDate SMALLDATETIME
, @EmployeeCode NVARCHAR(50)
, @EmployeeName NVARCHAR(255)
, @CompanyName NVARCHAR(255)
, @DepartmentName NVARCHAR(255)
, @DeptUnitName NVARCHAR(255)
, @JobTitleName NVARCHAR(255)
, @TeamName NVARCHAR(255)
, @Gender NVARCHAR(1)'

EXEC sp_executesql @strSQL, @params
, @FromDate = @FromDate
, @ToDate = @ToDate
, @EmployeeCode = @EmployeeCode
, @EmployeeName =@EmployeeName
, @CompanyName =@CompanyName
, @DepartmentName = @DepartmentName
, @DeptUnitName =@DeptUnitName
, @JobTitleName =@JobTitleName
, @TeamName =@TeamName
, @Gender = @Gender
END

Friday, September 21, 2007

Convert numeric for viewing easily

CREATE FUNCTION [dbo].[fnDisplayNumber](@dblNumber real, @intNumberType smallint = 0, @intNoOfDigitsAfterDecimal smallint=0)
--@NumberTime : 0 (American), 1(Vietnamese)
RETURNS nvarchar(30) AS
BEGIN
declare @i tinyint
declare @strLeft nvarchar(30)
declare @strRight nvarchar(30)
declare @strResult nvarchar(30)

if @intNoOfDigitsAfterDecimal<0 intnoofdigitsafterdecimal =" 0">10
begin
set @intNoOfDigitsAfterDecimal = 10
end

set @strResult = ltrim(str(@dblNumber, 30, @intNoOfDigitsAfterDecimal))

if charindex('.', @strResult) > 0
begin
set @strLeft = left(@strResult, charindex('.',@strResult)-1)
end
else
begin
set @strLeft = @strResult
end

set @strRight = right(@strResult, @intNoOfDigitsAfterDecimal)

set @i=1
set @strResult = ''
while @i<=len(@strLeft) begin set @strResult = @strResult + substring(@strLeft, @i, 1) if @i % 3 = len(@strLeft) % 3 AND @i <> len(@strLeft)
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + '.'
end
else
begin
Set @strResult = @strResult + ','
end
end
set @i = @i + 1
end

if @intNoOfDigitsAfterDecimal>0
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + ',' + @strRight
end
else
begin
Set @strResult = @strResult + '.' + @strRight
end
end

return @strResult
END

Thursday, September 13, 2007

Calculate between two points on the earth

/*
Definitions:
South latitudes are negative, east longitudes are positive
Description:
This routine calculates the distance between two points (given the latitude/longitude of those points).
Passed to function:
@lat1, @lon1 = Latitude and Longitude of point 1 (in decimal degrees)
@lat2, @lon2 = Latitude and Longitude of point 2 (in decimal degrees)
@unit = the unit you desire for results
where: 'M' is statute miles
'K' is kilometers (default)
'N' is nautical miles
*/
CREATE FUNCTION dbo.fnGetDistance
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float,
@unit char(1)
)
RETURNS float
AS
BEGIN
DECLARE @theta FLOAT
DECLARE @dist FLOAT
DECLARE @pi FLOAT
SET @pi = 3.14159265358979323846
SET @theta = @lon1 - @lon2
SET @dist = sin(@lat1 * @pi/180) * sin(@lat2 * @pi/180)
+ cos(@lat1 * @pi/180) * cos(@lat2 * @pi/180) * cos(@theta * @pi/180)
SET @dist = acos(@dist)
SET @dist = @dist * 180/@pi
SET @dist = @dist * 60 * 1.1515
IF @unit = 'K'--Kilometer
SET @dist = @dist * 1.609344
ELSE IF @unit = 'N'--Nautical Miles
SET @dist = @dist * 0.8684

RETURN @dist
END