[SQL] 次序函數應用-判斷是否為連續日期

此論壇問題是要計算員工班別從哪一天開始並計算連續幾天是同一個班別。

T-SQL 解法

DECLARE @Temp TABLE (EmpNO char(10) , [WorkDate] datetime ,WorkType  char(4))
INSERT INTO @Temp VALUES('001','2013/01/10','早班')
INSERT INTO @Temp VALUES('001','2013/01/11','早班')
INSERT INTO @Temp VALUES('001','2013/01/12','早班')
INSERT INTO @Temp VALUES('001','2013/01/13','早班')
INSERT INTO @Temp VALUES('001','2013/01/14','休息')
INSERT INTO @Temp VALUES('001','2013/01/15','休息')
INSERT INTO @Temp VALUES('001','2013/01/16','休息')
INSERT INTO @Temp VALUES('001','2013/01/17','晚班')
INSERT INTO @Temp VALUES('001','2013/01/18','早班')
INSERT INTO @Temp VALUES('001','2013/01/21','早班')
INSERT INTO @Temp VALUES('001','2013/01/22','早班')
INSERT INTO @Temp VALUES('001','2013/01/23','早班')
INSERT INTO @Temp VALUES('001','2013/01/24','早班')

SELECT 
    T.EmpNO , 
    T.WorkType , 
    MIN(WorkDate) AS StartDate , -- 找出該班別起始日期
    COUNT(*) AS [Counts]         -- 該班別連續天數
FROM
    (
        SELECT 
            EmpNO , 
            WorkType , 
            WorkDate ,
            -- 重點邏輯
            DATEADD(DD , -1 * ROW_NUMBER() OVER (PARTITION BY EmpNO , WorkType ORDER BY WorkDate) ,    WorkDate) AS Grp
        FROM @Temp
    ) AS T
GROUP BY T.EmpNO , T.WorkType , T.Grp
ORDER BY EmpNO , StartDate

邏輯分析

 
以 001 早班為例說明

SELECT 
    EmpNO , 
    WorkType , 
    WorkDate ,
    ROW_NUMBER() OVER (PARTITION BY EmpNO , WorkType ORDER BY WorkDate) AS ROWNO ,
    DATEADD(DD , -1 * ROW_NUMBER() OVER (PARTITION BY EmpNO , WorkType ORDER BY WorkDate) , WorkDate) AS Grp
FROM @Temp
WHERE EmpNO = '001' 
        AND WorkType = '早班'

判斷連續日期為此問題重點，利用 ROW_NUMBER() 產生 ROWNO 欄位後，把此 ROWNO 資料去減掉 WorkDate，從上圖的 Grp 欄位可以看出，假如該班別為連續日期，Grp 欄位內的日期資料會是相同日期，依此來判斷是否為連續日期。

• 延伸閱讀
• [SQL] 次序函數應用-重覆資料不顯示
• [SQL] 次序函數應用-資料分組
• [Challenge] 從 Log 中計算 Server 停機時間
• 參考資料
• 論壇問題出處
• 論壇問題應用 - 一樣的TSQL 思考邏輯
• Gaps and Islands problem (SQL) 連續範圍

[Challenge] 從 Log 中計算 Server 停機時間

Beyond Relational TSQL Challenge 37

This is a challenge to identify the downtime of servers from the log data generated by a monitoring application.

There is a monitoring application that keeps checking the status of several servers at 5 minute intervals. When the monitoring application finds that a particular server is down, it adds a record into the log table.

• Sample Data

ServerName DownTime
---------- -------------------
Server1       2010-01-03 10:00
Server1       2010-01-03 12:00
Server1       2010-01-03 12:05
Server1       2010-01-03 12:10
Server1       2010-01-03 14:05
Server1       2010-01-03 17:35
Server1       2010-01-03 17:45
Server1       2010-01-03 17:50
Server1       2010-01-03 17:55
Server2       2010-01-03 09:55
Server2       2010-01-03 10:00

Note that the monitoring application inserts a record only when the server is down. The polling takes place every 5 minutes. In the above example, "Server1" was down at 10:00. There is no entry for 10:05 which indicates that when the monitoring application checked at 10:05, the server was up. So the first down time is for 00:05 minutes at 10:00 am.

• Expected Results

Server  DownTime             DownFor
------- -------------------- -------
Server1     2010-01-03 10:00 00:05
Server1     2010-01-03 12:00 00:15
Server1     2010-01-03 14:05 00:05
Server1     2010-01-03 17:35 00:05
Server1     2010-01-03 17:45 00:15
Server2     2010-01-03 09:55 00:10

• Rules
1. Assume that the current status of the server - after the last reading - is 'up'.
2. It is possible to have the server down for more than 99 hours and in such a case, the actual number of hours should be displayed.
3. No order should be assumed in the input data.
4. Output should be sorted by Server Name and Down Time columns.
5. There will be no duplicates in the input data.

[Challenge] 從數字字串中把數字和字母分開

Beyond Relational TSQL Beginners Challenge 10

This challenge refers to sanitizing text data. Your task is to read an alphanumeric string and create two output columns one containing all the digits found in the input string and the other with all the letters of the English Alphabet.

• Sample Data

Id AlphanumericData
-- ---------------------------------------------------------------------
1  I would like to invite my readers to participate in a TSQL Challenge.
2  You will receive 12 records
3  1234567

• Expected Results

Id AlphanumericData            Numbers    Alphabets
-- --------------------------- ---------  -----------------------------
1  I would like to invite my   No Number  Iwouldliketoinvitemyreaders
   readers to participate in   Found      toparticipateinaTSQLChallenge   
   a TSQL Challenge.         
2  You will receive 12 records 12         Youwillreceiverecords
3  1234567                     1234567    No Alphabets Found

• Rules
1. The program should run in SQL SERVER 2005+.
2. Column names should respect the desired output shown.
3. Output must be sorted in Ascending order of Id.
4. Ignore those records where there is no data.
5. If the alphanumeric data does not contain any numeric record, then the Number field should display "No Number Found".
6. If the alphanumeric data does not contain any alphabetic record, then the Alphabets field should display "No Alphabets Found".

[SQL] 利用 T-SQL 監測硬碟空間

這篇文章介紹兩種監測硬碟空間的方法

利用 xp_fixeddrives 搭配 Automation

1. xp_fixeddrives：找出本機硬碟還有多少可用空間，但無法抓取網路磁碟，是一個 undocumented extended stored procedure。
2. Automation：利用 FileSystemObject 來抓取本機硬碟總空間。

要使用 Automation 必須先開啟伺服器選項 Ole Automation Procedures

• 在 Facet 內啟用（啟動一）

• 利用 T-SQL 語法啟動（啟動二）

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ole Automation Procedures', 1
reconfigure
GO