[SQL] 錯誤的伺服器名稱

安裝 SQL Server 時，會將伺服器名稱設為電腦名稱，可以利用 @@SERVERNAME 或 SERVERPOPERTY() 的 ServerName 屬性來查詢。

• @@SERVERNAME：SQL Server 的本機伺服器名稱。
• SERVERPROPERTY() 的 ServerName 屬性：Windows 伺服器和執行個體名稱。

SELECT @@SERVERNAME
SELECT SERVERPROPERTY('ServerName')

當變更執行 SQL Server 的電腦名稱後，@@SERVERNAME 並不會變更，必須使用 sp_addserver 和 sp_dropserver 來手動更新。

把電腦名稱由 Test 改為 Demo，並利用上述語法查詢 ServerName

 利用 sp_addserver 和 sp_dropserver 來變更 ServerName，更新後請重新啟動 SQL Server

EXEC sp_dropserver 'TEST\SQL2005'
GO
EXEC sp_addserver 'DEMO\SQL2005' , 'local'
GO

• MSDN 參考資料
• 重新命名主控 SQL Server 獨立式執行個體的電腦
• @@SERVERNAME
• sp_addserver
• sp_dropserver
• SERVERPROPERTY
• 參考資料
• 重新命名 SQL Server 作業系統的電腦名稱後之修正作業；修正 @@servername 之問題
• 修改電腦、伺服器名稱後，SQL Server 的修正作業--補遺篇
• 如何更換 SQL Server 的主機名稱

[Challenge] 串接多行資料

Beyond Relational TSQL Beginners Challenge 4

This challenge has been seen in one of the SQL Server forums. The challenge is all about merging the data based on certain conditions.

• Sample Data

ID Name
-- ------------------
1  Deepak Kumar Goyal
2  Niladri Biswas
2  Pratik Shaw
3  Sumi Girijan
3  Suresh Beldar
3  Jeeva Baby

The challenge is that if the Id’s are exactly two in number (e.g. count of ID = 2 is exactly two ) then the names should be concatenated with 'OR'. When Ids are more than two (e.g. Id # 3 has a count of 3) , the values should be concatenated with 'AND'

• Expected Results

ID Name
-- ---------------------------------------------
1  Deepak Kumar Goyal
2  Niladri Biswas OR Pratik Shaw
3  Sumi Girijan AND Suresh Beldar AND Jeeva Baby

• Rules
1. The program should run in SQL SERVER 2005+.
2. Column names should respect the desired output shown.
3. There should be exactly 1 space before and after the OR/AND clause.
4. Output should be in the order of Ascending Id.
5. Names should not be repeated even if it is given in the sample input i.e. if X is appearing more than once in the input for id = 10, the desired output will have only one such record.
6. Names cannot be blank e.g. for id = 10 if the Name field is ‘’(blank), the program should ignore that.

Microtek ArtixScan DI 2010SD - 更換分紙墊

• 品管小姐把下面的畫面 scan 給我看，要我處理一下，才知道原來還有分紙墊這個耗件需要定期更換。

• 在當初買的說明文件內發現這張說明和備品零件，還好資料都有完整地留下來，要不然還真的不知道分紙墊零件是哪個。

• 更換下來的分紙墊零件比較

• 更換完之後，就是重設分紙墊計數器，打開 scan 程式 => 掃描器 => 取得掃描器資訊，點選出去後，會再出現一次重設分頁紙掃描次數的畫面。

• 點選分紙墊掃描次數的重設按鈕

• 確認視窗，點選確定即完成重設

[SQL] Dynamic PIVOT

在 [SQL] PIVOT 和 UNPIVOT 內有人詢問，在不確定轉置選項的情況下，能否使用 PIVOT 來滿足轉置需求。

利用動態組字串方式來達到此需求。

• T-SQL 語法

CREATE TABLE #PIVOT (Employee char(8),Date datetime,Kind varchar(10),Hours numeric(4,2))
INSERT INTO #PIVOT VALUES ('AAAAA','20101001','事假',3.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101002','事假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101003','事假',4.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101010','病假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101020','病假',1.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101025','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101026','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101001','事假',6.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101009','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101025','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101026','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101027','陪產假',8.0)

DECLARE 
  @pivotcol nvarchar (4000) , -- 轉置欄位變數
  @selectCol nvarchar(4000) , -- PIVOT 後要把 NULL 資料轉為 0 用
  @tsql1 nvarchar(4000) ,     -- 動態組出的 T-SQL 語法（資料 NULL 呈現）
  @tsql2 nvarchar(4000)       -- 動態組出的 T-SQL 語法（資料 0 呈現）

-- 把 Kind 資料塞進 @pivotcol 和 @selectcol 變數內
SELECT 
  @pivotcol = ISNULL(@pivotcol + ' , ' , '') + QUOTENAME(Kind) ,
  @selectcol = ISNULL(@selectcol + ' , ' , '') + 'ISNULL(' + QUOTENAME(Kind) + ',0) AS ' + QUOTENAME(Kind)
FROM #PIVOT
GROUP BY Kind

 -- 方法一：資料 NULL 呈現
SET @tsql1 = 
  '
    SELECT *
    FROM 
      (
        SELECT Employee,Kind,Hours
        FROM #PIVOT
      ) AS P
    PIVOT
      (
        SUM(Hours) FOR Kind IN (' + @pivotcol + ')
      ) AS PV
  '

-- 方法二：資料 0 呈現
SET @tsql2 = 
  '
    SELECT Employee , ' + @selectcol + '
    FROM 
      (
        SELECT Employee,Kind,Hours
        FROM #PIVOT
      ) AS P
    PIVOT
      (
        SUM(Hours) FOR Kind IN (' + @pivotcol + ')
      ) AS PV
  '
EXEC sp_executesql @tsql1
EXEC sp_executesql @tsql2

[Challenge] 找出句子中的回文字

This challenge is to find the palindromic words from sentence(s). Palindrome is a word reading the same backward as forward. While finding the palindromic words from the sentence(s), the noise words should be removed if any is present in the sentence. There is a table "Noise" that defines Noise words

For e.g. MADAM is a palindrome but MADAM! is not. So we need to first remove noise character "!" from MADAM!

There are 2 tables in challenge. One contains the data from which we need to find the Palindromes and another contains noise words.

• Sample Data

Table - Sentence

ID Sentence
-- -------------------------------
1  Hello Madam! how are you Madam?
2  She peep through the window
3  This is without any Palindrome

Table - Noise

ID Noise
-- -----
1  ?
2  !

• Expected Results

ID Sentence                  PalindromeFound PalandromicWords FoundAt
-- ------------------------------- --------------- ---------------- -------------
1  Hello Madam! how are you Madam? 2         Madam      Position : 2,6
2  She peep through the window    1         peep          Position : 2
3  This is without any Palindrome  0         NULL          NULL

• Rules
1. ID should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. If no palindrome found in the sentence, then PalindromeFound column will be 0,PalandromicWords and FoundAt column will be NULL.
4. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of ID.

[VS] 建立時儲存新專案

對於 Visual Studio 每次建立一個專案，就必須儲存該專案的設計，實在是覺得非常麻煩。

• 工具 => 選項

• 專案和方案 => 取消［建立時儲存新專案］

• 這樣建立新專案時，就不用儲存，但離開該專案時，會詢問是否要儲存該專案。

• 20140722 補

此功能只適用於 Console 和 WinForm 專案

參考資料
• Visual Studio Tricks: 建立臨時專案

[SQL] CONCAT()

CONCAT() 為 SQL Server 2012 新函數。

功能：傳回串連兩個以上之字串值的結果字串。
語法：CONCAT ( string_value1, string_value2 [, string_valueN ] )
重點：

1. 至少需要兩個輸入值
2. 所有引數會以隱含式轉換為字串
3. NULL 值會轉換為空字串
4. 所有引數皆為 NULL 時，會回傳 varchar(1) 的空字串

• 利用 AdventureWorks2012 的 [Person].[Person] 和 [HumanResources].[Employee] 來說明

SELECT  
  P.FirstName , 
  P.MiddleName ,    -- 資料內有 NULL
  P.LastName ,
  E.BirthDate ,     -- 資料型態為 date
  E.VacationHours , -- 資料型態為 smallint
  CONCAT(P.FirstName , P.MiddleName , P.LastName , E.BirthDate , E.VacationHours) AS Concat
FROM [Person].[Person] AS P
  JOIN [HumanResources].[Employee] AS E ON P.BusinessEntityID = E.BusinessEntityID

從上圖資料可以發現

1. MiddleName 資料內有 NULL，NULL 值轉為空字串。
2. BirthDate 資料型態為 date、VacationHours 資料型態為 smallint，經過隱藏式轉換變成字串。

參考資料
• 論壇問題出處
• MSDN CONCAT 
• SQL Server – String Concatenation – Faster Method

[Challenge] 確認技術人員維護的重疊時間

Beyond Relational TSQL Challenge 49

Several technicians are sent to a customer premises to do a certain maintenance work. Your task is to process the activity log entered by each technician and identify overlaps in the time sheet.

The company sends one or more technicians to perform maintenance work at customer premises. Each technician takes care of a specific task and maintains a time sheet. Technicians are categorized into different Grades. A technician with a lower Grade is billed at a ‘higher’ rate. It is quite possible that the time sheet submitted by the technicians overlap.

The company, however, does not want to have overlapping time sheet while billing the customer. In case of an overlap, the company wants to bill the customer only for the time spent by the technician with the highest billing rate. For example, if there is an overlap with a Grade1 technician and Grade3 technician, the company will bill only the time of the Grade1 technician for the overlapping period.

• Sample Data

JobID Technician Grade StartTime           EndTime
----- ---------- ----- ------------------- -------------------
1     Michael    1     2010-01-01 09:08:00 2010-01-01 12:00:00
1     John       2     2010-01-01 13:00:00 2010-01-01 16:24:00
1     Steve      3     2010-01-01 08:53:00 2010-01-01 16:53:00

• Expected Results

JobID Technician Grade StartTime           EndTime
----- ---------- ----- ------------------- -------------------
1     John       2     2010-01-01 13:00:00 2010-01-01 16:24:00
1     Michael    1     2010-01-01 09:08:00 2010-01-01 12:00:00
1     Steve      3     2010-01-01 08:53:00 2010-01-01 09:08:00
1     Steve      3     2010-01-01 12:00:00 2010-01-01 13:00:00
1     Steve      3     2010-01-01 16:24:00 2010-01-01 16:53:00

• Rule
1. The duration of a job may be a few hours, or even a few days.
2. A single technician can have multiple non-overlapping time entries for the same job with all grade values being the same.
3. The output should be ordered by JobID, Technician, StartTime.
4. The time sheet will contain time values rounded to the nearest minute.
5. A Job cannot have two technicians having the same Grade .

[Excel] 列與欄位標題

同事詢問我，為什麼他的 Excel 列印時，都會出現欄位名稱和資料筆數的問題。

• 列印時出現欄位名稱和資料筆數

• 版面設定 => 工作表 => 取消［列與欄位標題］。

• 再次列印時就不會出現啦

版本為 Office 2007。

[SQL] WHERE 中的動態篩選條件

T-SQL 撰寫中看見動態篩選的 WHERE 條件，是利用 COALESCE()、ISNULL()、IIF() 或 CASE WHEN 來作到，主要是為了減少 T-SQL 的撰寫或是要避免根據使用者輸入條件來串出 T-SQL，此作法可以獲得相同結果，但最大影響在於效能。

• 利用 AdventureWorks2012 的 Sales.SalesOrderHeader 和 Sales.SalesOrderDetail 來說明看見的情況

CREATE PROCEDURE uspDemo
(
  @訂單時間 AS datetime , 
  @顧客編號 AS int ,
  @產品編號 AS int
)
AS
  BEGIN

    SELECT
      OH.SalesOrderID ,
      OH.CustomerID ,
      OD.ProductID
    FROM [Sales].[SalesOrderHeader] AS OH
      JOIN [Sales].[SalesOrderDetail] AS OD ON OH.SalesOrderID = OD.SalesOrderID
    WHERE OH.OrderDate = ISNULL(@訂單時間 , OH.OrderDate)
      AND OH.CustomerID = ISNULL(@顧客編號 , OH.CustomerID) -- 動態篩選條件
      AND OD.ProductID = ISNULL(@產品編號 , OD.ProductID)

  END

動態篩選條件是利用 ISNULL() 來判斷 @顧客編號 的值，值為 NULL，WHERE 條件就會變成 OH.CustomerID = OH.CustomerID 也就是 1 = 1 的條件，永遠成立，此篩選條件就沒有作用，利用此特性 Store Procedure 就可以根據使用者輸入的條件，來控制 WHERE 的篩選條件。

[Challenge] 計算每項產品最低價的折扣組合

Beyond Relational TSQL Challenge 11

The context of this challenge is about dealing with combinations and how to extract valuable data. For this challenge we will work with two data tables.

The products with their prices:

ID NAME    PRICE
-- ------- ---------
1  PROD 1  100,00
2  PROD 2  220,00
3  PROD 3  15,00
4  PROD 4  70,00
5  PROD 5  150,00

And the discount coupons:

ID NAME         VALUE  IS_PERCENT
-- -----------  ------ ----------
1  CP 1 : -15$  15     0
2  CP 2 : -5$   5      0
3  CP 3 : -10%  10     1
4  CP 4 : -12$  12     0

On the IS_PERCENT column, you should understand, if 0 value is considered as real money value, if 1 then it is a percent value of the price.

The Challenge

For this shopping application, customers could use one to two coupons for the same product but the discount price can not be less than 70% of the original price and the total amount of the discount can not exceed 30$.

It is important to note that coupons are applied in a cumulative way. The second coupon is applied on the result of the original price + first coupon.

With these conditions, the boss ask for a report that shows for each product the minimum price that should be paid for a product using any combination of the discount coupons. For this report you need also to show:

• Original price (PRICE)
• Discount price (DISC_PRICE)
• Total amount of the discount (TOT_DISC)
• Total rate of the discount (RATE)
• An info field with names of coupons applied (COUPON_NAMES)

Regarding the original data tables, here is the final report you should produce:

ID NAME    PRICE    DISC_PRICE  TOT_DISC  RATE    COUPON_NAMES
-- ------  -------- ----------- --------- ------- -------------------------
1  PROD 1  100.00$  73.00$      27.00$    27.00%  CP 4 : -12$ + CP 1 : -15$
2  PROD 2  220.00$  193.00$     27.00$    12.27%  CP 4 : -12$ + CP 1 : -15$
3  PROD 3  15.00$   13.50$      1.50$     10.00%  CP 3 : -10%
4  PROD 4  70.00$   49.50$      20.50$    29.28%  CP 1 : -15$ + CP 3 : -10%
5  PROD 5  150.00$  120.00$     30.00$    20.00%  CP 3 : -10% + CP 1 : -15$

PDFCreater 輸出檔案為空白

同事跑來問我說，PDFCreater 為什麼會把 Word 轉成空白的 PDF 檔案，Google 一下才發現，原來 PDFCreater 一直都存在檔案名稱有中文字型時，會轉出空白 PDF 的問題，不過自己測試，發現中文檔案名稱好像也不一定會變成空白 PDF，反正以後遇上就知道要如何解決這個問題。

參考資料
• Tool - PDFCreator 輸出 PDF 檔案內容為空白
• [免費] PDFCreator v1.6.2 簡繁體中文修正版
  

[SSMS] 隱藏 SQL Server Instance

在 Client 端安裝 SSMS 並利用搜尋網路伺服器，就可以找到其他 SQL Server Instance，要讓 SSMS 搜尋不到，可以在 SQL Server 的通訊協定內隱藏 Instance。

• SSMS 伺服器名稱內選擇瀏覽其他 => 網路伺服器 => 就可以搜尋到其它的 SQL Server Instance；這個搜尋主要是透過 UDP 1434 來進行，圖中可見我電腦中的兩個 Instance SQL2012 和 SQLExpress（2005），SQLExpress 為隱藏的對象 。

• 組態管理員 => 網路組態 => SQLExpress 的通訊協定 => 旗標 => 隱藏執行個體。

• 再次利用 SSMS 來搜尋，就找不到 SQLExpress Instance。

延伸閱讀
• [SQL] Logon Trigger

[Challenge] 計算各經理和其部屬薪資

Beyond Relational TSQL Challenge 52

In a Employee Parent-Child dimension, where each employee has his own identification number and a parent number to his manager. This challenge is intended to calculate company's wage spent below a top level manager.

• Sample Data

Employee_Name Number ManagerNumber Wage   
------------- ------ ------------- -------
Jacob         345    NULL          3000.00
Marco         873    NULL          3000.00
John          844    345           1880.00
James         139    844           2010.00
Ruth          111    873           1550.00
Margaret      622    345           1300.00
Mike          999    622           2010.00
Paul          611    139           1400.00

• Expected Results

Name   Total Wage  Max wage amount  Max wage employees
-----  ----------  ---------------  ------------------
Jacob  11600.00    2010.00          James,Mike
Marco  4550.00     1550.00          Ruth

• Rules
1. Note that the tree is not balanced .
2. Note that there is no flag to determine if an employee is a leaf employees.
3. 'Max wage employees' is a comma separated list of people (in alphabetical order) earning that maximum wage.
4. Employee Names will contain only letters a-z in upper, lower or mixed case.
5. The output should be ordered by name in a case insensitive alphabetical order.
6. When the top level manager has no employee, the output should be 0.00 for the max wage amount and an empty string for the max wage employees.