DELETE FROM …. FROM or DELETE FROM CTE (T-SQL Sample Code)

Posted: May 29, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  
create table atable (id int,col1 int)
insert into atable values(1,1),(2,10),(3,30)
create table btable (id int,col1 int)
insert into btable values (3,30)



select * from atable 

--Option 1
DELETE FROM  a
FROM atable a
WHERE EXISTS (Select 1 From btable b WHERE a.id=b.id)

--Option 2
--;with mycte as (
--Select *
--from atable a
-- WHERE EXISTS (Select 1 From btable b WHERE a.id=b.id)
--)
--delete from mycte

select * from atable 



drop table atable, btable

https://msdn.microsoft.com/en-us/library/ms189835.aspx?f=255&MSPPError=-2147217396

The beginning and end of a week (T-SQL)

Posted: May 28, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment


--SELECT @@DATEFIRST --  7 (default, U.S. English) First day of the week is Sunday

--Set to meet your setting
SET DATEFIRST 1 --First day of the week is Monday

declare @startdate date='2014-07-01'
declare @enddate date='2015-07-31'

--Use some functions avalaible to SQL Server 2012

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n&lt;101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,dates as (
Select  dateadd(day,n-1,@startdate) dt from Nums
WHERE dateadd(day,n-1,@startdate)&lt;=@enddate
)

,yourdtset as (
SELECT dt, FIRST_VALUE(dt)Over(Partition by DATEPART(year,dt), DATEPART(week,dt)  Order by dt)  WeekFrom
,FIRST_VALUE(dt)Over(Partition by DATEPART(year,dt), DATEPART(week,dt) Order by dt DESC)  WeekEnd
,ROW_NUMBER () OVER (Partition by DATEPART(year,dt), DATEPART(week,dt)  Order by dt ) rn
FROM dates
)

  Select &#039;Week No&#039; + Cast(ROW_NUMBER () OVER (ORDER BY dt) as varchar(5)) +&#039;:&#039; as [WeeekNum]
  ,Format(WeekFrom,&#039;d-MMMM-yyyy&#039;)  WeekFrom
  ,Format(WeekEnd,&#039;d-MMMM-yyyy&#039;)  WeekEnd
   from yourdtset
   WHERE rn=1

--set to my local
SET DATEFIRST 7</pre>
--Week starts at Sunday
declare @dt Date = GETDATE()
select DATEADD(DAY, -datepart(weekday, DATEADD(DAY,@@datefirst - 1,@dt)) ,@dt) as week_start_monday
GO

--Week starts at Monday
declare @dt Date = GETDATE()
select DATEADD(DAY, -datepart(weekday, DATEADD(DAY,@@datefirst - 1,@dt)) + 1 ,@dt) as week_start_monday
GO
<pre>

https://social.msdn.microsoft.com/Forums/en-US/1fd8ba69-b638-447a-a457-c630ec136be2/how-to-generate-week-ranges-like-this-picture-in-sql?forum=transactsql

Extract Date Part (or beginning of the day) for Datetime Data Type with T-SQL

Posted: May 27, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  
---SQL SEver 2012, 2014



SELECT 
--Use GETDATE()
dateadd(day,day(getdate()), EOMONTH(getdate(),-1)) 
, Cast(getdate() as Date)
, Convert(Date,getdate())
,datefromparts(Year(getdate()),month(getdate()),day(getdate()))
,dateadd(day,datediff(day,0,getdate()),0)
,Dateadd(day,datepart(dayofyear,getdate())-1,dateadd(year,datediff(year,0,getdate()),0))
,DATETIMEFROMPARTS(Year(getdate()),month(getdate()),day(getdate()),0,0,0,0)
,SMALLDATETIMEFROMPARTS(Year(getdate()),month(getdate()),day(getdate()),0,0)
,DATETIME2FROMPARTS(Year(getdate()),month(getdate()),day(getdate()),0,0,0,0,0)


--Use current_timestamp
,dateadd(day,day(current_timestamp), eomonth(current_timestamp,-1)) 
, Cast(current_timestamp as Date)
, Convert(Date,current_timestamp)
,datefromparts(Year(current_timestamp),month(current_timestamp),day(current_timestamp))
,dateadd(day,datediff(day,0,current_timestamp),0)
,Dateadd(day,datepart(dayofyear,current_timestamp)-1,dateadd(year,datediff(year,0,current_timestamp),0))
,SMALLDATETIMEFROMPARTS(Year(current_timestamp),month(current_timestamp),day(current_timestamp),0,0)
,DATETIMEFROMPARTS(Year(current_timestamp),month(current_timestamp),day(current_timestamp),0,0,0,0)
,DATETIME2FROMPARTS(Year(current_timestamp),month(current_timestamp),day(current_timestamp),0,0,0,0,0)

UDF Sample Code

Posted: May 21, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  

Alter FUNCTION [dbo].[udfCanonical2] (@path VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
 
	
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)  
 
SELECT  @path= (select C + '' from (select N, substring(@path, N, 1) C from Nums 
			
			where N<=datalength(@path)) t
            where PatIndex('%[a-zA-Z0-9 ]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)') 
       
RETURN  Replace(@path, ' ', '-')

END

Min,Max values along with other column value

Posted: May 21, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

  

CREATE TABLE MRMReadings
    ([SiteIncomeID] [int] IDENTITY(1,1) NOT NULL, [SiteID] nchar(5), [EDateTime] datetime NOT NULL, Income [nvarchar](50) NOT NULL)
;
    
INSERT INTO MRMReadings
    ([SiteID],  [EDateTime], [Income])
VALUES
    ('L0020',  '2015-05-19 05:00:26', '85.98'),
    ('L0020',   '2015-05-20 05:00:28', '145.98'),
    ('L0101',   '2015-05-19 22:07:43', '1,936.08'),
    ('L0101',   '2015-05-20 22:14:00', '1,438.89'),
    ('L0102',   '2015-05-20 21:16:26', '143.65'),
    ('L0102',   '2015-05-19 21:48:06', '243.50')
;



Select  [SiteID]
,Cast(Substring(Min(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(12) )  ),1,8) as datetime) ReadStartDate                  
,Cast(Substring(Max(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(8))),1,8) as datetime) ReadEndDate  
,Cast(Substring(Min(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(16))),9,16) as nvarchar(50)) ReadStartIncome     
,Cast(Substring(Max(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(16))),9,16) as nvarchar(50)) ReadEndIncome    
FROM   MRMReadings
Group by [SiteID]
 
   /*

   SiteID	ReadStartDate	ReadEndDate	ReadStartIncome	ReadEndIncome
L0020	2015-05-19 05:00:26.000	2015-05-20 05:00:28.000	85.98	145.98
L0101	2015-05-19 22:07:43.000	2015-05-20 22:14:00.000	1,936.08	1,438.89
L0102	2015-05-19 21:48:06.000	2015-05-20 21:16:26.000	243.50	143.65

*/
  



drop table MRMReadings

https://social.msdn.microsoft.com/Forums/en-US/ddb88538-4af8-4c16-88f2-d5522289dc7e/merge-2-rows-into-one-line-output?forum=transactsql

Combine INT to Datetime for last_occurrence_date and last_occurrence_time

Posted: May 18, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

 
 
--You can find a better solution from Scott Coleman


SELECT CAST(CAST(NULLIF(last_occurrence_date, 0) AS CHAR(8)) + ' ' +
	STUFF(STUFF(RIGHT(CAST(last_occurrence_time + 1000000 AS CHAR(7)), 6),
		5, 0, ':'), 3, 0, ':') AS DATETIME) AS last_date_and_time_as_datetime
FROM msdb.dbo.sysalerts

--better
;WITH    cteAlertTimes
          AS ( SELECT   last_occurrence_date / 10000 - 1900 AS yr ,
                        last_occurrence_date / 100 % 100 - 1 AS mo ,
                        last_occurrence_date % 100 - 1 AS dy ,
                        last_occurrence_time / 10000 AS hr ,
                        last_occurrence_time / 100 % 100 AS mi ,
                        last_occurrence_time % 100 AS sec
               FROM     msdb.dbo.sysalerts
               WHERE    last_occurrence_date > 0
             )
    SELECT  DATEADD(second, sec,
                    DATEADD(minute, mi,
                            DATEADD(hour, hr,
                                    DATEADD(month, mo, DATEADD(YEAR, yr, 0))
                                    + dy)))
    FROM    cteAlertTimes
	--http://www.sqlservercentral.com/Forums/Topic839394-146-1.aspx

Convert @YYYYQ to the end of Quarter with TSQL

Posted: May 14, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

 declare @YYYYQ char(5)='20152' 
 
 
SELECT Cast( Left(@YYYYQ ,4)+ Case when (Right(@YYYYQ,1)-1)  % 3 =0 
Then Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'31' 
Else Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'30' END   as Date)

, Left(@YYYYQ ,4)+ Case when (Right(@YYYYQ,1)-1)  % 3 =0 
Then Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'31' 
Else Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'30' END

--Or
Select   EOMOnth( Left(@YYYYQ,4)+  Right('0'+Cast(Cast(Right(@YYYYQ,1) as int) *3 as varchar(2)),2)+'01' ) 




---Another sample

 Create table test (id int, YYYYQ varchar(14)
 )
 
 Insert into test values(1,'1Q2014 Archive'),
 (2,'2Q2014 Archive'),
 (3,'3Q2014 Archive'),
 (4,'4Q2014 Archive'),
 (5,'1Q2015 Archive') 

 
 
SELECT 

Cast( Substring(YYYYQ ,3,4)+ Right('0'+Cast((Left(YYYYQ,1)-1)*3+1 as varchar(2)),2) +'01' as Date)  FirstdayofTheQuarter,


Cast( Substring(YYYYQ ,3,4)+ Case when (Left(YYYYQ,1)-1)  % 3 =0 
Then Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'31' 
Else Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'30' END  as Date)   LastdayofTheQuarter,

Dateadd(Day,1,
Substring(YYYYQ ,3,4)+ Case when (Left(YYYYQ,1)-1)  % 3 =0 
Then Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'31' 
Else Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'30' END) LastdayofNextQuarter

From test


---Or


Select
Substring(YYYYQ ,3,4)+  Right('0'+Cast(((Cast(Left(YYYYQ,1) as int)-1) *3 +1)as varchar(2)),2)+'01' FirstdayofTheQuarter
,
EOMOnth( Substring(YYYYQ ,3,4)+  Right('0'+Cast(Cast(Left(YYYYQ,1) as int) *3 as varchar(2)),2)+'01' ) LastdayofTheQuarter
,
Dateadd(day,1,EOMOnth( Substring(YYYYQ ,3,4)+  Right('0'+Cast(Cast(Left(YYYYQ,1) as int) *3 as varchar(2)),2)+'01' )) 
FirstdayofNextQuarter


From test



Drop table test

bcp a text file with double quotes to a table — another sample

Posted: May 14, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

 ---Generate xml format file
DECLARE @cmd VARCHAR(4000) 
set @cmd = 'BCP  [testcs].[dbo].[Table_1] format nul   -f "C:\temp\myformatfile.xml" -x -c -t\^",\^" -r \^"\n  -T -S'+ @@servername
exec master..xp_cmdshell @cmd
  
 --Modify the format file myFormatFile.xml  
--By adding one row in the Record section by copy Row ID=1 and change the ID to 0


----bcp in with all data
exec master..xp_cmdshell  'bcp [testcs].[dbo].[Table_1] IN "C:\temp\mytest.txt" -f "C:\temp\myformatfile.xml" -T  -S'+ @@servername;

--Another sample table and file
create table bcpTest
(
CASH1 varchar(10),
CASH2 varchar(10),
CASH3 varchar(10),
CASH4 varchar(10)
)
--file:
"-28,750.00"^"-28,750.00"^"-1,000,000"^"5.75"

 -- Generate fmt format file 
 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f E:\DATA\myFormatFiletest.fmt -t\"\^\" -r\"\n -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd
  
 --Modify the format file myFormatFiletest.fmt 
--By adding one row   by copy Row 1 and modify it and change the first column to 1 through 5
--change column count from 4 to 5

12.0
5
1       SQLCHAR             0       10      "\""    0     ""          SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       10      "\"^\""    1     CASH1          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       10      "\"^\""    2     CASH2          SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       10      "\"^\""    3     CASH3          SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       10      "\"\r\n"   4     CASH4          SQL_Latin1_General_CP1_CI_AS

 

--Import code

--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt'
WITH
(
FORMATFILE ='E:\DATA\myFormatFiletest.fmt' 
 
)
select * from bcpTest



--XML format file

 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f "E:\DATA\myformatfile.xml" -x -t\^"^^\^" -r \^"\n   -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd

--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt' WITH
(FORMATFILE ='E:\DATA\myformatfile.xml' )
select * from bcpTest



---Modified XML file  (add one row and modify it.  (check the -t flag  -t\^"^^\^"  ). The ^ has special meaning so double up to escape it as delimiter.

Optional Parameters in Stored Procedure: how to make it perform well

Posted: May 13, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

Kimberly Tripp: Building High Performance Stored Procedures

Building High Performance Stored Procedures

Powershell With UPDATE

Posted: May 12, 2015 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment


if (-not(Get-Module -name 'SQLPS')) {
  if (Get-Module -ListAvailable | Where-Object {$_.Name -eq 'SQLPS' }) {
    Push-Location # The SQLPS module load changes location to the Provider, so save the current location
   Import-Module -Name 'SQLPS' -DisableNameChecking  
   }
  }

$val1="abc"
$val2="xyz"
 
$svrname = "MC\MSSQL2014"

Invoke-Sqlcmd -Query "update testcs.dbo.mytest Set col2='$val2' WHERE col1 like '$val1'+'%' " -ServerInstance $svrname

Jingyang Li

SQL Server