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

  
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)

 
--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<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)<=@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 'Week No' + Cast(ROW_NUMBER () OVER (ORDER BY dt) as varchar(5)) +':' as [WeeekNum]
  ,Format(WeekFrom,'d-MMMM-yyyy')  WeekFrom  
  ,Format(WeekEnd,'d-MMMM-yyyy')  WeekEnd
   from yourdtset
   WHERE rn=1


--set to my local
SET DATEFIRST 7 

 

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

  
---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

  

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

  

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

 
 
--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

 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