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

JDE Julian Date in SQL

DECLARE @pDate date = GETDATE(),
                   @pJulianDate int = null,
                   @pCovDate date = null,
                   @pJulianYear int = null
--Date to JulianDate
SET            @pJulianDate = ((year(@pDate)-1900)*1000)+datepart(dayofyear,@pDate)
--JulianDate to Date
SET            @pCovDate = (DateAdd(Day, @pJulianDate%1000 - 1,
                                           CAST(CAST(CAST((@pJulianDate/1000+1900) AS Int)
                                           AS VarChar(4)) + '-01-01 '  AS Date)))
--Get year from JulianDate
SET            @pJulianYear = (@pJulianDate/1000) + 1900
SELECT    @pDate, @pJulianDate, @pCovDate, @pJulianYear

Open Full Screen Chrome using cmd

The command as below:

"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" --start-fullscreen www.google.com

Close and open IE using cmd

The command as below:

Close IE process
taskkill /f /im "iexplore.exe"

Open IE
"C:\Program Files\Internet Explorer\iexplore.exe"