Convert GPS Location from DMS to DD via Excel

Sample for DMS
#Longitude: 99°51'05.2"E
#Latitude: 6°19'19.3"N

Sample for DD
#Longitude: 99.8514444444444
#Latitude: 6.32202777777778

Excel formulas
#Longitude (B2)
=(MID(B2,1,FIND("°",B2)-1)+(MID(B2,FIND("°",B2)+1,FIND("'",B2)-FIND("°",B2)-1)/60)+(MID(B2,FIND("'",B2)+1,FIND("""",B2)-FIND("'",B2)-1)/3600))*IF(OR(MID(B2,FIND("""",B2)+1,1)="E",MID(B2,FIND("""",B2)+1,1)="N"),1,-1)

#Latitude (D2)
=(MID(D2,1,FIND("°",D2)-1)+(MID(D2,FIND("°",D2)+1,FIND("'",D2)-FIND("°",D2)-1)/60)+(MID(D2,FIND("'",D2)+1,FIND("""",D2)-FIND("'",D2)-1)/3600))*IF(OR(MID(D2,FIND("""",D2)+1,1)="E",MID(D2,FIND("""",D2)+1,1)="N"),1,-1)

How to delete OEM partition with Diskpart

Step 1. Hit "Windows Key + R to open the run dialogue box, enter "diskpart" and click "OK" to open a black command prompt window.

Step 2. Type "list disk" to display all the disks of your computer. Type "select disk n" to identify which disk you need to work with. Here n stands for the disk letter.

Step 3. Type "list partition" to display all the volumes on the hard drive. Type "select partition n" to identify which partition you want to remove. Here n stands for the volume letter.

Step 4. Type "delete partition override" to remove the OEM partition. Finally, type "exit" to close the windows when you receive the message says, DiskPart successfully deleted the selected partition".

在cmd建立鏡像路徑 (subst)

subst w: "C:\Documents and Settings"
建立後即可用 w: 來取代 C:\Documents and Settings\

C:\Documents and Settings\xxx → w:\Documents and Settings\xxx

How to select top 1 record per group

抓取客戶最後一次購買此產品的日期


SELECT   product, cus, date
FROM      (
                 SELECT   product,
                                   cus,
                                   date,
                                   row_number() over (partition by product, cus order by date desc) as ranker
                 FROM      SalesTable
                 ) z
WHERE   ranker = 1

Schedule Auto Restart Server

C:\Windows\System32\shutdown.exe /r

Get mail account from AD at MSSQL

1. Create the linked server
EXEC sp_addlinkedserver @server = 'ADSI',
                                           @srvproduct = 'Active Directory Services 2.5',
                                           @provider = 'ADSDSOObject',
                                           @datasrc = 'adsdatasource'

2. Security

3. SQL Function Script
ALTER FUNCTION [dbo].[GetEmailFromAD]
(
     -- Add the parameters for the function here
     @pUserID varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
     -- Declare the return variable here
     DECLARE @pMail varchar(250)
     -- Add the T-SQL statements to compute the return value here
     SELECT @pMail = mail
     FROM OpenQuery (ADSI, '
                                      SELECT displayName,
                                      mail,
                                      sAMAccountName
                                      FROM ''LDAP://...:389/DC=...,DC=...,DC=...''
                                      WHERE objectCategory = ''User''
                                      ')
     WHERE sAMAccountName = RIGHT(@pUserID,LEN(@pUserID)-CHARINDEX('\',@pUserID))
     -- Return the result of the function
     RETURN @pMail
END

Declare Table at MSSQL

DECLARE  @RowsToProcess int,
                   @CurrentRow int
--Declare a temp table to stock the search result
DECLARE @TempTable TABLE
     (
          RowID int not null primary key identity,
          DataInt int default 0,
          DataNvarchar nvarchar(20) null,
          DataBit  bit null,
          DataDecimal decimal(15,2) default 0fault 0,
          ThisYearQuota decimal(15,2) default 0
     )
--Insert the search result to @TempTable
INSERT INTO @TempTable
SELECT *
FROM
           (
           SELECT *
           FROM DataTable
           ) z
--Max(Row)
SET @RowsToProcess = @@ROWCOUNT
--Start Row
SET @CurrentRow = 0
--Process row one by one
WHILE @CurrentRow < @RowsToProcess
     BEGIN
          SET @CurrentRow = @CurrentRow + 1
          --Select single rew data
          SELECT * FROM @TempTable WHERE RowID = @CurrentRow
     END