SQL Server Common SQL2

Source: Internet
Author: User

-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

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.