產生序號

DECLARE @SerialNumberStart INT
DECLARE @SerialNumberEnd INT
SELECT SerialNumberStart = 99900, SerialNumberEnd= 99999

DECLARE @tbNumberBase TABLE
(
Seq INT
)
INSERT INTO @tbNumberBase
SELECT digits FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)
Dig (digits)
INSERT INTO ProductInventory
(
ProductID,
SerialNumber,
Quantity
)
SELECT
@ProductID,
SerialNumber,
@Quantity
FROM
(SELECT
@SerialNumberStart
+ HundredThousands.Seq * 100000
+ TenThousands.Seq * 10000
+ Thousands.Seq * 1000
+ Hundreds.Seq * 100
+ Tens.Seq * 10
+ Units.Seq Num
FROM
@tbNumberBase HundredThousands
CROSS JOIN @tbNumberBase TenThousands
CROSS JOIN @tbNumberBase Thousands
CROSS JOIN @tbNumberBase Hundreds
CROSS JOIN @tbNumberBase Tens
CROSS JOIN @tbNumberBase Units)
TableAlias (SerialNumber)
WHERE SerialNumber <= @SerialNumberEnd
ORDER BY SerialNumber

Read More

CTE - Sample

Declare @start int, @end int
Select @start=1, @end=999
;With NumberSequence( Number ) as
(
Select @start as Number
union all
Select Number + 1 from NumberSequence where Number < @end
)
Select * From NumberSequence Option (MaxRecursion 1000)

Read More

CTE - Sample

--define limits
DECLARE @todate datetime, @fromdate datetime
SET @fromdate = '2013-01-01'
SET @todate = '2013-12-31'
;WITH DateSequence( [Date] ) AS
(
SELECT @fromdate AS [Date]
UNION ALL
SELECT DATEADD(day, 1, [Date]) FROM DateSequence WHERE DATE < @todate
)

--select result
SELECT
CONVERT(VARCHAR,[Date],112) AS ID,[Date] AS [Date],
DATEPART(DAY,[Date]) AS [Day],
CASE
WHEN DATEPART(DAY,[Date]) = 1 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'st'
WHEN DATEPART(DAY,[Date]) = 2 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'nd'
WHEN DATEPART(DAY,[Date]) = 3 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'th'
END AS [DaySuffix],
DATENAME(dw, [Date]) AS [DayOfWeek],DATEPART(DAYOFYEAR,[Date]) AS [DayOfYear],DATEPART(WEEK,[Date]) AS [WeekOfYear],
DATEPART(WEEK,[Date]) + 1 - DATEPART(WEEK,CAST(DATEPART(MONTH,[Date]) AS VARCHAR) + '/1/' + CAST(DATEPART(YEAR,[Date]) AS VARCHAR)) AS [WeekOfMonth],
DATEPART(MONTH,[Date]) AS [Month],DATENAME(MONTH,[Date]) AS [MonthName],DATEPART(QUARTER,[Date]) AS [Quarter],
CASE DATEPART(QUARTER,[Date])
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS [QuarterName],
DATEPART(YEAR,[Date]) AS [Year]
FROM DateSequence OPTION (MaxRecursion 10000)

Read More

PL/SQL

-- Finding a column name in all tables

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,USER_ALL_TABLES u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like '%TXTM%'
order by DATA_LENGTH desc;

Read More

vlookup & lookup

  

=IF(AND(A1>=0,A1<0.03125),115%,IF(AND(A1>=0.03125,A1<0.125),112%,IF(AND(A1>=0.125,A1<0.25),109%,IF(AND(A1>=0.25,A1<0.375),106%,IF(AND(A1>=0.375,A1<0.5),103%,IF(AND(A1>=0.5,A1<1),100%,IF(AND(A1>=1,A1<2),96%,IF(AND(A1>=2,A1<3),92%,IF(AND(A1>=3,A1<4),86%,IF(AND(A1>=4,A1<5),82%,IF(AND(A1>=5,A1<6),78%,IF(AND(A1>=6,A1<=7),73%,IF(AND(A1>=7,A1<8),69%,IF(AND(A1>=8,A1<9),65%,IF(AND(A1>=9,A1<10),60%,IF(AND(A1>=10,A1<11),56%,IF(AND(A1>=11,A1<12),51%,IF(AND(A1>=12,A1<13),47%,IF(AND(A1>=13,A1<14),42%,IF(AND(A1>=14,A1<15),38%,IF(AND(A1>=15,A1<16),33%,IF(AND(A1>=16,A1<17),29%,IF(AND(A1>=17,A1<18),24%,IF(AND(A1>=18,A1<19),20%,IF(AND(A1>=19,A1<20),16%,IF(AND(A1>=20,A1<21),11%,IF(AND(A1>=21,A1<22),7%,IF(AND(A1>=22,A1<23),2%,IF(AND(A1>=23),0%,"無值" )))))))))))))))))))))))))))))



=VLOOKUP(A2,$D$2:$E$29,2)



=IF(LOOKUP($G2,{0,60,70,80,90},$A$1:$E$1)=A$1,$F2,"")

 

Read More

URLs

VBA

- 書籤
http://go3d.pixnet.net/blog/post/34270685-%E3%80%90%E4%B8%8D%E7%94%A8%E8%B2%B7%E6%9B%B8%E3%80%81%E4%B8%8D%E7%94%A8%E8%A3%9C%E7%BF%92%E3%80%91excel-vba-%E5%9F%BA%E7%A4%8E%E6%95%99%E5%AD%B8%EF%BC%8F

http://web.ntit.edu.tw/~yclin/excel_new/excel06.htm

Read More

2nd generation NHI 系統更新異常

工作以來，頭一回遇到這麼不負責任、懶惰、耍大牌又不測試的使用者！就這麼上線了！

1. 薪資單及薪資冊的工資項欄位變長或消失。

2. SNHPT日期錯誤。

誰遇到2012.11的SAP勞保雇主保費比勞保局寄來的繳費金額還多的情況嗎？

Read More