Commonly used sql2 and sqlserversql2 in SQLServer
-- Create table [dbo]. [BI_Dim_Date] (-- YYMMDD [DateF] [nvarchar] (20) NULL, -- French Date Format [DateKey] [nvarchar] (10) NULL, [Dim_Year] [int] NULL, [Dim_Month] [int] NULL, [Dim_Day] [int] NULL, -- quarter [Qu] [int] NULL, [QuCN] [varchar] (20) NULL, [QuEN] [varchar] (20) NULL, -- month [MonthCN] [varchar] (20) NULL, [MonthEN] [varchar] (20) NULL, -- [Ten] [int] NULL, [TenCN] [varchar] (20) NULL, -- week [Dim_Week] [int] NULL, -- [WeekDayCN] [varchar] (20) NULL, [WeekDayEN] [varchar] (20) NULL, -- yy-mm-dd [Dim_Date1] [date] NULL, -- DD/MM/YY [Dim_Date2] [nvarchar] (10) NULL, --- whether holiday 1 holiday 0 normal [IsDayOff] [int] NULL, -- holiday description [Event_Name] [varchar] (20) NULL) --- The insertion sequence corresponds to DECLARE @ BeginDate DATE in the order of table creation; SELECT @ BeginDate = '2016 '; WHILE @ BeginDate <= '000000' begin insert into BI_Dim_Date SELECT dbo. changeIt (@ BeginDate) as DateF, -- CONVERT (varchar (10), @ BeginDate, 120) AS DateKey -- 12-30convert (varchar (10), @ BeginDate, 112) AS DateKey, -- YEAR (@ BeginDate) AS Dim_Year, MONTH (@ BeginDate) as Dim_Month, Day (@ BeginDate) AS Dim_Day, Datepart (QUARTER, @ BeginDate) AS Qu, case when Datepart (QUARTER, @ BeginDate) = 1 then' first QUARTER 'when Datepart (QUARTER, @ BeginDate) = 2 then' Second QUARTER 'when Datepart (QUARTER, @ BeginDate) = 3 then'quarter 3 'else' QUARTER 4 'end AS QuCN, case when Datepart (QUARTER, @ BeginDate) = 1 then 'q1' WHEN Datepart (QUARTER, @ BeginDate) = 2 then 'q2 'WHEN Datepart (QUARTER, @ BeginDate) = 3 then 'q3 'else' q4' end as QuEN, case when MONTH (@ BeginDate) = 1 then 'february 'when MONTH (@ BeginDate) = 2 then' February 'when MONTH (@ BeginDate) = 3 then' February 'when MONTH (@ BeginDate) = 4 then 'february 'when MONTH (@ BeginDate) = 5 then' February 'when MONTH (@ BeginDate) = 6 then' February 'when MONTH (@ BeginDate) = 7 then 'february 'when MONTH (@ BeginDate) = 8 then' February 'when MONTH (@ BeginDate) = 9 then' February 'when MONTH (@ BeginDate) = 10 then 'february 'when MONTH (@ BeginDate) = 11 then' February 'else' February 'end as MonthCN, case when MONTH (@ BeginDate) = 1 then 'Jan 'when MONTH (@ BeginDate) = 2 then 'feb' when MONTH (@ BeginDate) = 3 then 'mar' when MONTH (@ BeginDate) = 4 then 'apr' when MONTH (@ BeginDate) = 5 then 'may' when MONTH (@ BeginDate) = 6 then 'jun' when MONTH (@ BeginDate) = 7 then 'jul' when MONTH (@ BeginDate) = 8 then 'aug' when MONTH (@ BeginDate) = 9 then 'sept' when MONTH (@ BeginDate) = 10 then 'oct' when MONTH (@ BeginDate) = 11 then 'nov 'else' Dec 'end as MonthEN, CASE when DATEPART (DAY, @ BeginDate) <= 10 THEN 1 when datepart (DAY, @ BeginDate)> 20 THEN 3 ELSE 2 END as Ten, CASE when DATEPART (DAY, @ BeginDate) <= 10 then' 'when DATEPART (DAY, @ BeginDate)> 20 then' 'else' 中' 'end as TenCN, DATEPART (WEEK, @ BeginDate) -1 AS Dim_Week, DATENAME (WEEKDAY, @ BeginDate) as WeekDayCN, case when DATENAME (WEEKDAY, @ BeginDate) = 'monday' then 'mon 'when DATENAME (WEEKDAY, @ BeginDate) = 'tuesday 'then' Tue 'when DATENAME (WEEKDAY, @ BeginDate) = 'wedned' when DATENAME (WEEKDAY, @ BeginDate) = 'then' thu' when DATENAME (WEEKDAY, @ BeginDate) = 'Friday' then' fri' when DATENAME (WEEKDAY, @ BeginDate) = 'saturday' then' Sat 'else' Sun' end as WeekDayEN, CONVERT (varchar, @ BeginDate, 112) as Dim_Date1, CONVERT (varchar (100 ), cast (@ BeginDate as datetime), 101) as Dim_Date2, 0 as IsDayOff, ''as Event_Name SET @ BeginDate = DATEADD (DAY, 1, @ BeginDate); end; select * from BI_Dim_Date -- truncate table BI_Dim_Date -- drop table BI_Dim_Date -- select GETDATE () -- 100 12:48:47 -- Select CONVERT (varchar (20140101), '123', 101) -- 20140101 -- select convert (varchar (10), convert (varchar (10), '000000', 20140101), 120) -- 112 -- select convert (varchar (10 ), '123', 20140101) -- 120 -- Select CONVERT (varchar (20140101), GETDATE (), 100) 11/24/2014 -- select DATENAME (WEEKDAY, '123 ') as WeekDay -- set language N 'English '-- the day of the week in English format -- select DATENAME (WEEKDAY, '123 ') as WeekDay -- set language N 'simplified Chinese '-- select DATENAME (WEEKDAY, '000000') as WeekDay -- Select CONVERT (varchar (20131230), '000000', 100) -- 20140101 -- Select CONVERT (varchar (100), GETDATE (), 101) -- 11/24/2014 -- Select CONVERT (varchar (100), cast ('100' as datetime), 20140101) -- 01/01/2014 -- sp_rename 'dim _ Date. month ', 'dim _ month', 'column' -- sp_rename 'dim _ Date. day ', 'dim _ Day ', 'column' select * from BI_Dim_Date where DateKey> = '000000' update BI_Dim_Date set Dim_Week = 52 where DateKey between '000000' and '000000' update BI_Dim_Date set Dim_Week = 52 where DateKey' 20141229 'and' 20150104 'Update BI_Dim_Date set Dim_Week = 52 where DateKey between '000000' and '000000' update BI_Dim_Date set Dim_Week = 52 where DateKey = '000000' update BI_Dim_Date set Dim_Week = Dim_Week-1 where WeekDayEN = 'sun' update BI_Dim_Date set Dim_Week = Dim_Week + 1 where DateKey> = '2016' -- select SUBSTRING ('20160301 ', 3, 2) -- select SUBSTRING ('20140901', 20131230) -- select -- case -- when SUBSTRING ('20160901', 20131230) = 01 then 'Jan '-- when SUBSTRING ('20160301 ', 5, 2) = 02 then 'feb' --- when SUBSTRING ('100', 20131230) = 03 then 'mar '-- when SUBSTRING ('100', 20131230) = 04 then 'apr' -- when SUBSTRING ('20140901', 20131230) = 05 then 'may' -- when SUBSTRING ('20140901', 20131230) = 06 then 'jun' -- when SUBSTRING ('20140901', 20131230) = 07 then 'jul' -- when SUBSTRING ('20140901', 20131230) = 08 then 'aug' -- when SUBSTRING ('20140901', 20131230) = 09 then 'sept' -- when SUBSTRING ('20140901', 20131230) = 10 then 'oct' -- when SUBSTRING ('20140901', 20131230) = 11 then 'nov' -- else 'dec '-- end as MonthEN, ----- storage process of conversion date 30-Dec-13 -- create proc ChangeDate -- @ d1 nvarchar (10) -- as -- select. d + '-' + B. monthEN + '-' + c. y -- from (select SUBSTRING (@ d1, 7,2) as d) a, -- (select -- case -- when SUBSTRING (@ d1, 5, 2) = 01 then 'Jan '-- when SUBSTRING (@ d1, 5, 2) = 02 then 'feb' -- when SUBSTRING (@ d1, 5, 2) = 03 then 'mar '-- when SUBSTRING (@ d1, 5, 2) = 04 then 'apr' -- when SUBSTRING (@ d1, 5, 2) = 05 then 'may' -- when SUBSTRING (@ d1, 5, 2) = 06 then 'jun'--- when SUBSTRING (@ d1, 5, 2) = 07 then 'jul' -- when SUBSTRING (@ d1, 5, 2) = 08 then 'aug' -- when SUBSTRING (@ d1, 5, 2) = 09 then 'sept' -- when SUBSTRING (@ d1, 5, 2) = 10 then 'oct' -- when SUBSTRING (@ d1, 5, 2) = 11 then 'nov' -- else 'dec '-- end as MonthEN) B, -- (select SUBSTRING (@ d1, 3, 2) as y) c -- exec ChangeDate @ d1 = '000000' --- create function ChangeIt (@ dd nvarchar (10) returns nvarchar (20) AS begindeclare @ result nvarchar (20) SET @ result = (select (. d + '-' + B. monthEN + '-' + c. y) from (select SUBSTRING (@ dd, 9, 2) AS d) a, (select case when SUBSTRING (@ dd, 6, 2) = 01 then 'Jan 'when SUBSTRING (@ dd, 6, 2) = 02 then 'feb' when SUBSTRING (@ dd, 6, 2) = 03 then 'mar 'when SUBSTRING (@ dd, 6, 2) = 04 then 'apr' when SUBSTRING (@ dd, 6, 2) = 05 then 'may' when SUBSTRING (@ dd, 6, 2) = 06 then 'jun' when SUBSTRING (@ dd, 6, 2) = 07 then 'jul' when SUBSTRING (@ dd, 6, 2) = 08 then 'aug' when SUBSTRING (@ dd, 6, 2) = 09 then 'sept' when SUBSTRING (@ dd, 6, 2) = 10 then 'oct' when SUBSTRING (@ dd, 6, 2) = 11 then 'nov' else 'dec 'end AS MonthEN) B, (select SUBSTRING (@ dd, 3, 2) as y) c) RETURN (@ result) END -- drop function ChangeIt SELECT [dbo]. changeIt ('000000') SELECT dbo. changeIt ('000000') -- set language us_english -- select datename (dd, '2017-5-31 ') +'-'+ DATENAME (m, '2017-5-31 ') + '-' + DATENAME (yy, '1970-5-31 ') DECLARE @ BeginDate DATE; SELECT @ BeginDate = '20160901'; WHILE @ BeginDate <= '20160901' begin select dbo. changeIt (@ BeginDate) -- @ BeginDate SET @ BeginDate = DATEADD (DAY, 1, @ BeginDate); end update BI_Dim_Date set Dim_Week = Dim_Week-1 where WeekDayEN = 'sun' select * from BI_Dim_Date
Table connection update
ALTER TABLE bi_dim_date ALTER COLUMN Event_name [varchar](100) update bi_dim_dateSET Event_name= enamefrom bi_dim_date,TempDayOffWHERE DateKey=TempDayOff.Bdate
Update with case... when
update BI_Dim_Date set MonthEN=( case when Dim_Month= 1 then 'Jan' when Dim_Month= 2 then 'Feb' when Dim_Month= 3 then 'Mar' when Dim_Month= 4 then 'Apr' when Dim_Month= 5 then 'May' when Dim_Month= 6 then 'Jun' when Dim_Month= 7 then 'Jul' when Dim_Month= 8 then 'Aug' when Dim_Month= 9 then 'Sept' when Dim_Month= 10 then 'Oct' when Dim_Month= 11 then 'Nov' when Dim_Month= 12 then 'Dec' end update BI_Dim_Date set MonthEN=( case when Dim_Month= 1 then 'Jan' when Dim_Month= 2 then 'Feb' when Dim_Month= 3 then 'Mar' when Dim_Month= 4 then 'Apr' when Dim_Month= 5 then 'May' when Dim_Month= 6 then 'Jun' when Dim_Month= 7 then 'Jul' when Dim_Month= 8 then 'Aug' when Dim_Month= 9 then 'Sept' when Dim_Month= 10 then 'Oct' when Dim_Month= 11 then 'Nov' else 'Dec' end ) update BI_Dim_Date set MonthEN=( case Dim_Month when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sept' when 10 then 'Oct' when 11 then 'Nov' else 'Dec' end )
Max and min
select MIN(cc.DateKey) aa, MAX(cc.DateKey) bb from( select DateKey from BI_Dim_Date where Dim_Year=2014 and Dim_Month=11 ) cc
Query the first two
select * from (select *, row_number() over(partition by area_name order by area_name ) aa FROM db_bi) t where t.aa<=2
String truncation see post: http://bbs.csdn.net/topics/390946681
--- Create a table if OBJECT_ID ('test') is not nulldrop table test go create table test (id int, name varchar (10), [key] varchar (20 )) go -- insert test select 1, 'lisa ', 'Li, is, Sa' union allselect 2, 'sophia', 'AB, cd, ef' union allselect 3, 'lily', '12, 34,23 'go select * from test/** master .. spt_values is a digital auxiliary table, which contains 1, 2, 3, 4... charindex is to find the position of ',' in the key (starting from the number). The position of substring is to get the string starting from the number, and the ending position is obtained from charindex ', the 'position function is to retrieve the separated values of ',' in the key. **/select id,. name, SUBSTRING ([key], number, CHARINDEX (',', [key] + ',', number)-number) as [key] from test a, master .. spt_values where number> = 1 and number <= len ([key]) and type = 'p' and substring (',' + [key], number, 1) = ', 'select * from master .. spt_values where type = 'P'
Recursive query
---- 2008 recursion: recursion from the parent item to the subitem query with cte (ID, PID) whose parent ID is wID) AS (-- parent select * from DIGUI where PID = 'wid' union all -- lower-level select t in the recursive result set. ID, t. PID from DIGUI as tinner join cte as c on t. PID = c. id) select * from cte --- recursion from subitem to parent item query with cte (ID, PID) of all subids = kssID) AS (-- select * from DIGUI where ID = 'kssid 'union all -- select t of the parent item in the recursive result set. ID, t. PID from DIGUI as tinner join cte as c on t. ID = c. PID) select * from cte