Commonly used sql2 and sqlserversql2 in SQLServer

Source: Internet
Author: User

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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.