SQL Server Common SQL2

-New Create TABLE [dbo]. [Bi_dim_date]    (--YYMMDD [DATEF] [nvarchar] (a) null,--French date format [DateKey] [nvarchar] () null, [dim_year] [int] NULL, [Dim_month] [INT]  NULL, [dim_day] [int] NULL,--quarter [Qu] [int] null, [QUCN] [varchar] (+) NULL, [Quen] [varchar] (20)  NULL,--month [MONTHCN] [varchar] () NULL, [Monthen] [varchar] (a) NULL,--1 early 2 mid 3 [Ten] [int]      NULL, [TENCN] [varchar] (a) null,--week [dim_week] [int] NULL,--Day of the week [WEEKDAYCN] [varchar] () NULL, [Weekdayen] [varchar] NULL,--YY-MM-DD [dim_date1] [date] null,--DD/MM/YY [dim_date2] [nvarchar] (TEN) NULL,---whether the section Holiday 1 Holiday 0 normal [isdayoff] [int] null,--Holiday description [event_name] [varchar] () NULL)---The order of insertion is in the order of the table to correspond to declare @Begi    Ndate DATE;  SELECT @BeginDate = ' 20131230 '; While @BeginDate <= ' 20171231 ' BEGIN INSERT into bi_dim_date SELECT dbo. Changeit (@BeginDate) as Datef,--convert (varchar), @BeginDate, as DateKey--2013-12-30convert-Isolated form (varchar), @BeginDate, 112)                                       As DateKey,--year (@BeginDate) as Dim_year, MONTH (@BeginDate) As Dim_month, Day (@BeginDate) as Dim_day, Dat Epart (QUARTER, @BeginDate) as Qu, case if Datepart (QUARTER, @BeginDate) =1 then ' first quarter ' when D                                                    Atepart (QUARTER, @BeginDate) =2 then ' second quarter ' when Datepart (QUARTER, @BeginDate) =3 then ' third quarter ' ELSE ' quarter ' END As QUCN, case is Datepart (QUARTER, @BeginDate) =1 then ' Q1 ' when Datepart (Quar                                                    TER, @BeginDate) =2 then ' Q2 ' when Datepart (QUARTER, @BeginDate) =3 then ' Q3 ' ELSE ' Q4 ' END As Quen, case if month (@BeginDate) = 1 Then ' January ' when month (@BeginDate) = 2 Then ' February ' when MONTH (@BeginDate) = 3 theN ' March ' when month (@BeginDate) = 4 Then ' April ' if month (@BeginDate) = 5 Then ' May ' if month (@BeginDate) = 6 Then ' June ' When month (@BeginDate) = 7 Then ' July ' if month (@BeginDate) = 8 Then ' August ' if month (@BeginDate) = 9 Then ' September ' when MO                                                NTH (@BeginDate) = Ten Then ' October ' when MONTH (@BeginDate) = one Then ' November ' Else ' December ' end As MONTHCN, case if month (@BeginDate) = 1 Then ' Jan ' when month (@BeginDate) = 2 Then ' Feb ' when MONT H (@BeginDate) = 3 Then ' Mar ' if month (@BeginDate) = 4 Then ' April ' when month (@BeginDate) = 5 Then ' if ' when month (@Be gindate) = 6 Then ' June ' when month (@BeginDate) = 7 Then ' Jul ' if month (@BeginDate) = 8 Then ' in ' When month (@BeginDa                                                TE) = 9 Then ' Sept ' when month (@BeginDate) = Ten Then ' Oct ' when month (@BeginDate) = All Then ' Nov ' Else ' Dec ' end As Monthen, case if DATEPART (day, @BeginDate) <=10 then 1 when Datepar T (day, @BeginDate) >20Then 3 ELSE 2 END as Ten, case if DATEPART (day, @Beg                                                indate) <=10 Then ' early ' when DATEPART [Day, @BeginDate] >20 then ' late ' ELSE ' mid ' END As TENCN, DATEPART (WEEK, @BeginDate)-1 as Dim_week, Datename (WEE KDAY, @BeginDate) as WEEKDAYCN, case when Datename (WEEKDAY, @BeginDate) = ' Monday ' and ' Mon ' when D Atename (WEEKDAY, @BeginDate) = ' Tuesday ' Then ' Tue ' when Datename (WEEKDAY, @BeginDate) = ' Wednesday ' and ' Wed ' when Datename (weekda Y, @BeginDate) = ' Thursday ' Then ' Thu ' when Datename (WEEKDAY, @BeginDate) = ' Friday ' and ' Fri ' when Datename (WEEKDAY, @BeginDate) = ' Saturday ' then ' Sat ' Else ' Sun ' end as Weekdayen, CONVERT (varchar, @Begi ndate,112) as dim_date1, CONVERT (varchar), cast (@BeginDate as DateTime), 101) as Dim_date2, 0 as Isdayoff, ' as Eve Nt_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 ()--2014-11- 12:48:47--select CONVERT (varchar), ' 20140101 ', 101)--20140101--select CONVERT (varchar), CONVERT (varchar (1 0), ' 20140101 ', (+),--20140101--select convert (varchar), ' 20140101 ',--20140101--select CONVERT (varchar ( (+), GETDATE (), 101) 11/24/2014--select datename (WEEKDAY, ' 20131230 ') as WEEKDAY--set language N ' 中文版 '--English format star  Period several--select datename (WEEKDAY, ' 20131230 ') as WEEKDAY--set language N ' Simplified Chinese '--select datename (WEEKDAY, ' 20131230 ') as WeekDay--select CONVERT (varchar (101), ' 20140101 ', 101)--20140101--select CONVERT (varchar (+), GETDATE (),)-- 11/24/2014--Select CONVERT (varchar), cast (' 20140101 ' as DateTime), 101)--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>= ' 20161229 ' update bi_dim_date set dim_week=52 where DateKey between ' 20131230 ' and ' 20140105 ' update Bi_dim_date set dim_week=52 where DateKey between ' 20141229 ' and ' 20150104 ' update bi_dim_date set dim_week=52 where Datek ey between ' 20151228 ' and ' 20160103 ' update bi_dim_date set dim_week=52 where datekey= ' 20170101 ' 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>= ' 20170101 '--select SUBSTRING (' 20131230 ', 3,2)--select SUBSTRING (' 20131230 ', 7,2)--select--case-When SUBSTRING (' 20131230 ', 5,2) = Then ' Jan '--when SUBSTRING (' 20131230 ', 5,2) = ' Feb '---when SUBSTRING (' 20131230 ', 5,2) = th En ' Mar '--when SUBSTRING (' 20131230 ', 5,2) = ' APR '--when SUBSTRING (' 20131230 ', ' 5,2 ') = ' may ' – when SUBS  TRING (' 20131230 ', 5,2) = Then ' June '--when SUBSTRING (' 20131230 ', 5,2) = "Then ' Jul ' – When SUBSTRING (' 20131230 ', 5,2) = --when SUBSTRING (' 20131230 ', 5, 2) = Sept '-When SUBSTRING (' 20131230 ', 5,2) = Ten Then ' Oct '--when SUBSTRING (' 20131230 ', 5,2) = one and then ' Nov '-  -Else ' Dec '--end as Monthen,-----conversion Date stored Procedure 30-dec-13--create proc changedate[email protected] nvarchar (ten)--as--select a.d+ '-' +b.monthen+ '-' +c.y--from (select SUBSTRING (@d1, 7,2) as D) a,--(select--case--when SUBSTRING (@d1, 5,2) = Then ' Jan ' – When SUBSTRING (@d1, 5,2) = "Feb"--wh    En SUBSTRING (@d1, 5,2) = "Mar"-When SUBSTRING (@d1, 5,2) = "APR"--when SUBSTRING (@d1, 5,2) = "may" --when SUBSTRING (@d1, 5,2) = Then ' June '---when SUBSTRING (@d1, 5,2) = "Then ' Jul ' – When SUBSTRING (@d1, 5,2) = th En '--when SUBSTRING (@d1, 5,2) = Sept '--when SUBSTRING (@d1, 5,2) = Ten Then ' Oct '--when SUBSTRING (@d1, 5,2 ) = one-then ' Nov '--else ' Dec '--end as Monthen) b,--(select SUBSTRING (@d1, 3,2) as Y) c--exec changedate @d1 = ' 20130220 '- --Custom functions Create function Changeit (@DD nvarchar (TEN)) returns nvarchar () as begindeclare @result nvarchar () SET @result = (select (a.d+ '-' +b.monthen+ '-' + C.Y) from (select SUBSTRING (@dd, 9,2) as D) A, (Select Case when SUBSTRING (@dd, 6,2) = While ' Jan ' when SUBSTRING (@dd, 6,2 ) = when SUBSTRING (@dd, 6,2) = "Mar" When SUBSTRING (@dd, 6,2) = "APR" When SUBSTRING (@dd, 6, 2) = when SUBSTRING (@dd, 6,2) = "June" When SUBSTRING (@dd, 6,2) = "Then ' Jul" when SUBSTRING (@dd, 6 , 2) = when SUBSTRING (@dd, 6,2) = Sept "When SUBSTRING (@dd, 6,2) = Ten Then ' Oct ' when SUBSTRING (@dd , 6,2) = one Then ' Nov ' Else ' Dec ' end as Monthen) b, (select SUBSTRING (@dd, 3,2) as Y) c) RETURN (@result) End--drop func tion Changeit SELECT [dbo]. Changeit (' 20131201 ') SELECT dbo. Changeit (' 20131201 ')--SET LANGUAGE us_english--select datename (dd, ' 2010-5-31 ') + '-' +datename (M, ' 2010-5-31 ') + '-' +  Datename (yy, ' 2010-5-31 ') DECLARE @BeginDate DATE;  SELECT @BeginDate = ' 20131230 '; WHILE @BeginDate <= ' 20171231 ' BEGIN SELECT dbo.  Changeit (@BeginDate) [email protected] 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

Even table updates

Alter TABLE Bi_dim_date ALTER COLUMN event_name [varchar] (+)  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 is dim_month= 1 then "Jan" when dim_month= 2 Then ' Feb ' when dim_month  = 3 Then ' Mar ' when dim_month= 4 Then ' April ' when dim_month= 5 then ' could ' when dim_month= 6 then ' June ' when Dim_month= 7 Then ' Jul ' when dim_month= 8 then ' ' when dim_month= 9 and ' Sept ' when dim_month= ten then ' O ' CT ' When dim_month= and ' Nov ' when dim_month= and ' Dec ' End update bi_dim_date set monthen= (case when D     Im_month= 1 Then ' Jan ' when dim_month= 2 and ' Feb ' when dim_month= 3 and ' Mar ' when dim_month= 4 Then ' APR ' When Dim_month= 5 Then ' could ' when dim_month= 6 then ' June ' when dim_month= 7 then ' Jul ' when dim_month= 8 t Hen ' dim_month= 9 Then ' Sept ' when dim_month= and ' Oct ' when dim_month= one then ' Nov ' else Dec     ' End ' update bi_dim_date set monthen= (case dim_month if 1 then ' Jan ' while 2 Then ' Feb ' when 3 Then ' Mar '   When 4 Then ' APR '  When 5 Then ' could ' when 6 then ' June ' when 7 then ' Jul ' when 8 then ' Sept ' when 9 then ' the ' when ' Hen ' Oct ' when one and then ' Nov ' Dec ' end

Maximum and minimum

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 bars of each zone

SELECT * FROM (SELECT *, Row_number () up (partition by Area_name order by  Area_name  ) AA from  db_bi) T  wh Ere t.aa<=2

Interception of strings See stickers: http://bbs.csdn.net/topics/390946681

---new table if object_id (' test ') is not nulldrop table test go CREATE TABLE test (     ID int,     name varchar),     [key] V Archar () Go-Insert Data inserts Test Select 1, ' Lisa ', ' Li,is,sa ' Union allselect 2, ' Sophia ', ' Ab,cd,ef ' union allselect 3, ' Lori ', ' 12,34,23 ' go select * from Test   /**   master. Spt_values is a digital auxiliary table, inside is 1,2,3,4...charindex is looking for key in ', ' the position (from number) bit to start looking for substring is starting from the number bit start to take the string, the cutoff position is derived from charindex ', ' the function to be implemented is to remove the key from the ', ' separated value **/      select    ID,     a.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  by parent to child query parent ID for WID with CTE (ID,PID) as (--Parent SELECT * from Digui where pid= ' WID ' union all--subordinate select in recursive result set T.id,t.pid from  Digui as Tinner join CTE as C in t.pid=c.id) SELECT * FROM CTE---child recursion to parent  query with CTE for all sub-id=kssid (ID  , PID) as (--Next level parent SELECT * from Digui where id= ' kssid ' union all--The parent of the recursive result set select T.id,t.pid from  Digui as Tinner join CTE As C on t.id=c.pid) SELECT * FROM CTE

SQL Server Common SQL2