Options to Delete with Two Tables


--option 1
delete from jobs 
where exists  (select 1 from calibrations c where id =c.JobId)
--option 2
delete from jobs 
from calibrations c inner join jobs j on j.id =c.JobId

--option 3

delete from jobs 
where id in  (select Jobid from calibrations )

--This is not a good option if you use the id column from your delete table, all rows will be deleted
delete from jobs 
where id in  (select Id from calibrations )

Advertisements

Calculate Thanksgiving Date with T-SQL

In the US, Thanksgiving is always celebrated on the fourth Thursday of November.
For any given date, we can find out Thanksgiving day in that year with T-SQL.

 


declare @anydate datetime='2014-01-30'
declare @dt datetime='20151101'
Select  @dt=dateadd(month,10,dateadd(year, datediff(year,0,@anydate),0)) ---November 1st


select  DateAdd(day, (7+5 -DatePart(weekday, @dt))%7, @dt) firstThuIntheMonth
select  DATEADD(WEEK, 3, DATEADD(d, Case when DATEPART(dw, @dt)>5 Then 7 Else 0 ENd + (5 - DATEPART(dw, @dt)), @dt)) Thanksgivingday
Select  DATEADD(WEEK, 3, DateAdd(day, (7+5 -DatePart(weekday, @dt))%7, @dt) ) Thanksgivingday

/*
Year	U.S. Thanksgiving	
2012	Thursday, November 22	
2013	Thursday, November 28	
2014	Thursday, November 27	
2015	Thursday, November 26	
2016	Thursday, November 24	
2017	Thursday, November 23	
2018	Thursday, November 22	
2019	Thursday, November 28	
*/


 

Get Most Recent Backup Chain in Full, Differential and Log Backups

 
 
 use [myDB];

declare @dt datetime=getdate()
declare @dbname sysname= (select DB_NAME())
declare @server_name sysname ='DEVSQL'
 
;WIth mycte as (SELECT
s.database_name,
s.backup_start_date,
s.first_lsn, s.last_lsn,
s.database_backup_lsn,
s.checkpoint_lsn,
 s.[type],-- D--FULL,I--Diff,L--LOG
 m.physical_device_name, 
s.server_name,s.recovery_model
,s.backup_finish_date
,row_number() Over(Partition by s.[type] Order by s.backup_finish_date DESC ) rn
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @dbname
AND server_name=@server_name
AND s.backup_finish_date >=(select top 1 s.backup_finish_date from msdb.dbo.backupset s 
Where s.[type]='D' and s.server_name= @server_name AND s.database_name = @dbname 
AND s.backup_finish_date<= @dt
Order by s.backup_finish_date DESC)
AND s.backup_finish_date<= @dt
)
 
Select * from mycte
WHERE (rn=1 and [type]<>'L') Or (last_lsn >= (select last_lsn+1 from mycte WHERE rn=1 and [type]='I') )
ORDER BY backup_finish_date desc,backup_start_date desc

 
 

 

Get max min per group in sequence — A solution for the question

You can find the original question from MSDN and I tried to come up with a solution but with requirement changes, here is the final version. I hope this is.

 



if object_id('test','U') is not null drop table test 
Create table test (Car int, X Char(1), [Time] int)

INSERT  INTO test
VALUES  ( 11, 'A', 5 ),
        ( 11, 'B', 6 ),
        ( 11, 'B', 7 ),
        ( 11, 'B', 8 ),		 
        (  11,'C', 9 ),
        (  11,'C', 10 ),
        (  11,'C', 11 ),
		(  11,'A', 12 ),
		(  11,'B', 13 ) ;


	with mycte as (	 	
	Select  Car, X,  [Time] time1, lead( [Time],1) Over( Order by  [Time]) leadTime 
	,row_number()Over(order by [Time]) - ROW_NUMBER() OVER (PARTITION BY X ORDER BY [Time]) delta from test )

 ,mycte1 as (
 Select Car, X,  time1,leadTime, delta
 , ROW_NUMBER() OVER (PARTITION BY delta ORDER BY [Time1] DESC) rn
 , count(*) OVER (PARTITION BY X, delta ) cnt   
 from mycte)





 Select Car, X, min(time1) minTime, max(leadTime)  maxTime,  0 as moving 
 from mycte1
 WHERE rn&gt;1
Group by  Car, X, delta 
 UNION ALL
Select  Car, X,  min(time1) minTime, max(CASE WHEN cnt&gt; 1 Then leadTime Else time1 END )  maxTime , 1 as Moving 
from mycte1
 WHERE rn=1
GROUP BY Car, X, delta
Order by minTime


/*
Car	X	minTime	maxTime	moving
11	A	5	5	1
11	B	6	8	0
11	B	8	9	1
11	C	9	11	0
11	C	11	12	1
11	A	12	12	1
11	B	13	13	1
*/



 

Arrange Column Order In a Select Statement (Dynamic query)

 
--create table test (examID int, col1 int, col4 int, col3 int, col2 int)
--Insert into test values (1, 1,2,3,4),(1,4,3,2,1),(1,9,9,9,9)


Declare @ExamID int=1

DECLARE @colsSorted AS NVARCHAR(2000),
   @sql  AS NVARCHAR(4000)

   declare @mytable varchar(50)='test', @myschema  varchar(50) ='dbo'

select @colsSorted 
  = STUFF((select ', '+quotename(column_name,'[')
           from information_schema.columns 
           where table_name = @mytable and TABLE_SCHEMA=@myschema
		   AND COLUMN_NAME<>'ExamID'
			Order by NewID()
           FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')


print  @colsSorted

Set @sql=N'SELECT '+ @colsSorted  +  ' FROM ' + @myschema+ '.'+ @mytable + ' WHERE ExamID='+ CAST(@ExamID as varchar(4))

exec sp_executesql @sql;


---    drop table test


 

Number Table and Split STring

 
--===== Create number table on-the-fly
;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)
 

,mycte as (
select strCol , Row_NUmber() Over(Order By n) rn

, CAST(substring(strCol, n, charindex('/', strCol + '/', n) - n) as MONEY)  splitVal

from test
cross apply Nums
Where n <= len(strCol) AND substring('/' + strCol, n, 1) = '/' )
Select * from mycte 

WHERE rn=4


Drop table test 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/08de9a11-ed07-4e8a-834b-1d96a15900c8/substring-use-to-extract-portion-of-field?forum=transactsql


Island Question Variation

 

if object_id('test','U') is not null drop table test 
create table test (MBR_ID int,  DS varchar(10))
Insert into test values
(1,'2011-07-15'), (1,'2011-07-21'),(1,'2011-07-29'),(1,'2011-08-04'),(1,'2011-08-11'),(1,'2011-08-25'),(1,'2011-09-01'),(1,'2011-09-21')
,(1,'2012-01-11'),(1,'2012-03-15'),(1,'2012-06-04'),(1,'2012-10-11'),(1,'2013-02-14'),(1,'2013-06-18'),(1,'2013-07-08'),(1,'2013-07-15')
,(1,'2013-07-17'),(1,'2013-08-07'),(1,'2013-08-14'),(1,'2013-08-28'),(1,'2013-09-11'),(1,'2013-09-25'),(1,'2013-11-20')
,(1,'2013-12-04'),(1,'2013-12-17'),(1,'2014-01-14'),(1,'2014-01-29'),(1,'2014-02-25'),(1,'2014-02-26'),(1,'2014-03-12')
,(1,'2014-03-19'),(1,'2014-03-22'),(1,'2014-03-24')
 

;with mytest as (
--move date to the end of the month
select MBR_ID, eomonth(ds) as ds from test)
,mycte as (
SELECT MBR_ID, ds,  lag(ds, 1) Over(partition by MBR_ID  ORDER BY ds) dslag
 ,Case when datediff(month,lag(ds, 1) Over(partition by MBR_ID ORDER BY ds), ds) <=2 
 Then 0 Else datediff(month,ds,lag(ds, 1) Over(partition by MBR_ID ORDER BY ds) ) End diff
 
FROM mytest)

---get group grn
,mycte1 as (
Select MBR_ID, ds, SUM(diff) Over(Order by ds) grn  from mycte )

--Get YYYY-MM
,mycte2 as (
select MBR_ID, Convert(varchar(7),min(ds),126) START_DT, Convert(varchar(7),max(ds),126)  END_DT 
from mycte1 
Group by MBR_ID,grn)

--Sort
Select MBR_ID, START_DT,END_DT from mycte2 
Order by Cast(START_DT+'-01' as date)
 
 /*
 MBR_ID	START_DT	END_DT
1	2011-07	2011-09
1	2012-01	2012-03
1	2012-06	2012-06
1	2012-10	2012-10
1	2013-02	2013-02
1	2013-06	2014-03

 */


 

https://social.msdn.microsoft.com/Forums/en-US/1a8ff1d3-fdbb-4cb8-b596-249cded7b874/sql-query-help?forum=transactsql