Split One Column to Multiple Columns

  

--SQL Server 2016, 2017
 
create table source (CODE varchar(100))
Insert into Source values('6-1-A-B')

--Insert into  yourtable (CODE, CODE1,CODE2,CODE3,CODE4)
Select  s.CODE
, Max(Case when rn=1 then value end) CODE1
, Max(Case when rn=2 then value end) CODE2
, Max(Case when rn=3 then value end) CODE3
, Max(Case when rn=4 then value end) CODE4
from Source s
Cross apply (
SELECT ss.[value], ROW_NUMBER() OVER (PARTITION BY s.CODE ORDER BY s.CODE ) AS RN
FROM string_Split(CODE,'-') AS ss
) as d

Group by s.CODE

Drop table source

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/14dbc085-d942-48ba-878a-b83ea590a085/sql-string-split?forum=transactsql

Advertisements

First Wednesday after the Second Tuesday In a Month —Solution 2

  
 
   DECLARE  @StartDate  DATETIME = '2019-01-01'
DECLARE @EndDate DATETIME = DATEADD(DAY,900,@StartDate)
 
DECLARE @CustomOrderTable TABLE (DayOfWeek INT, OrderInTable INT)
INSERT @CustomOrderTable VALUES (7,7),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
 
 
  
   
--****  create a Number table
;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)
   
 --  
,DateRange as
(
select  dateadd(day,n-1,@startdate) dt from Nums  
where dateadd(day,n-1,@startdate)<=@EndDate)  
  
  
 
 
 
,mycte as (
SELECT  *,DayName = DATENAME(WEEKDAY,dt), DayOfWeek = DATEPART(DW,dt),Week = DATEPART(WEEK,dt),
 Sum(case when   DATENAME(WEEKDAY,dt)='Tuesday' then 1 else 0 end) Over(Partition by Year(dt), month(dt)   order by dt) grp
FROM    DateRange DR)
 
--INSERT INTO YourTable
 
 
 Select dt, DayName,  
 Year(dt) Year
 ,Case when  Day(DateAdd(Month, DateDiff(Month, 0, dt), 0)+6 
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+2)%7 --First Wednesday  IN the month
			 )=1 then 
			 DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+2)%7
			 Else
			  DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+7
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+2)%7
			  END  First_Wednesday_after_the_SecondTuesday_In_a_Month

    from mycte 
   
   

 

https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

https://jingyangli.wordpress.com/2019/01/03/first-wednesday-after-the-second-tuesday-in-a-month/


Search a Closest Date to Today from a Datetime Column

  
  --Thanks for Ronen for the test table mark up
CREATE TABLE TestTbl (
   ID INT primary key,
   Date DATETIME
)


INSERT INTO TestTbl (ID, Date)  
SELECT top 100000
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
	DATEADD(DAY, 0, ABS(CHECKSUM(NEWID()))%100000) d -- random values from 1900-01-01 to 2173-10-16
FROM sys.all_objects t1
CROSS JOIN sys.all_objects t2

 CREATE NONCLUSTERED INDEX idx_Date
ON [dbo].[TestTbl] ([Date])

Set statistics io on
Set statistics time on


---   Solution 3************************
print 'Jingyang Solution 3'
Select 
Case when abs(datediff(dd, getdate(), dt1))<abs(datediff(dd, getdate(), dt2)) 
then dt1 else dt2 end dt 
from (Select 1 as n ) src 
cross apply (
Select TOP 1 [Date] 
	from TestTbl
	WHERE [Date]  GETDATE()
	Order by [Date]) d2(dt2)

 /**************************************/

 -- Ronen Ariely 1:
print 'Ronen Ariely 1'
;With MyCTE1 as (
	Select TOP 1 [Date], RN = row_number() Over(Order by [Date])  
	from TestTbl
	WHERE [Date] < GETDATE()
	Order by [Date]
),
MyCTE2 as (
	Select TOP 1 [Date], RN = row_number() Over(Order by [Date] DESC) 
	from TestTbl
	WHERE [Date] < GETDATE()
	Order by [Date] DESC
),
MyCTE3 as(
	SELECT [Date],I = ABS(DATEDIFF(DAY, [Date], GETDATE())) FROM MyCTE1
	UNION ALL
	SELECT [Date],I = ABS(DATEDIFF(DAY, [Date], GETDATE())) FROM MyCTE2
)
SELECT TOP 1 [Date]
FROM MyCTE3
order by I
GO



---Olaf's
print 'Olaf''s'
 
 SELECT TOP 1 *
FROM TestTbl
ORDER BY ABS(DATEDIFF(day, GETDATE(), Date))


---Jingyang Solution 1
print 'Jingyang Solution 1'
Select ID, Date from (
Select *, row_number() Over(Order by abs(datediff(day,[Date],getdate() ))) rn 
from TestTbl
) t
WHERE rn=1

---Solution 2
print 'Jingyang Solution 2'
select distinct Date from (
Select  * ,  (min(abs(datediff(day,[Date],getdate() ))) Over() )  diff,  datediff(day,[Date],getdate() )  flg
from TestTbl
cross apply (values( abs(datediff(day,[Date],getdate() )) )  ) d(val)
) t
where diff=val  




---Solution 4
print 'Jingyang Solution 4'
;with mycte1 as (
 Select max([Date]) dt1
	from TestTbl
	WHERE [Date]  GETDATE()
	 ) 

Select 
Case when abs(datediff(dd, getdate(), dt1))<abs(datediff(dd, getdate(), dt2)) 
then dt1 else dt2 end dt 
from mycte1,mycte2


--Jingyang Solution 5
print 'Jingyang Solution 5'

 Select top 1 dt 
from 
 (Select max([Date]) dt 
	from TestTbl
	WHERE [Date]  GETDATE()
	) t
 Order by abs(datediff(dd, getdate(), dt))

 drop table TestTbl 
 
 
 

Set statistics io off
Set statistics time off
  
   

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33d32be6-43e2-454f-86ba-880fc39f394a/find-date-closest-to-getdate?forum=transactsql


Date Group and Sum Up Query

  

DECLARE @FromDate Date='2018-01-01'
DECLARE @ToDate Date='2019-01-11'

create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
insert into #temp (_Type,_Date,Qty,BalQty) values 
('IN',@FromDate,10,10) --modified date
,('OUT','2018-01-15',8,2)
,('OUT','2018-02-12',2,0),
('OUT','2018-03-13',2,-2)
,('OUT','2018-04-04',3,-5)
,('IN','2018-05-24',10,5)
,('IN','2018-05-28',6,11)
,('STKADJ','2018-05-28',11,0)
,('OUT','2018-08-29',6,-6)
,('IN','2018-09-15',10,4)


;with mycte as (
select * , Case when  BalQty<0 or (_Type='STKADJ' and  BalQty=0 )   then 1 else 0  end  chk

,row_number() Over(order by _Date)-row_number() Over(partition by Case when  BalQty<0 or (_Type='STKADJ' and  BalQty=0 ) then 1 else 0 end  order by _Date)   grp

from (
Select  _Type,_Date,Qty,BalQty   from #temp
Union all
Select '',@ToDate as _Date, 0 Qty,0 BalQty ) t


)

,mycte2 as (
Select *, row_number() Over(Partition by  chk,grp Order by  _Date) rn
,Case when row_number() Over(Partition by  chk,grp Order by  _Date)= 1 and chk=0 then datediff(day,min(_Date) Over(Partition by  chk,grp ) , max(_Date) Over(Partition by  chk,grp  )) +1 else 0 end diff from mycte
 )

 select  _Type,_Date,Qty,BalQty, Sum(diff) dtdiff from mycte2
 GROUP BY
	GROUPING SETS
	(
		  ((_Type),(_Date),(Qty),(BalQty) ),
		()  
	) 
 Order by ISNULL(_Date, Dateadd(day,1,@ToDate)) 

drop table #temp

/*
_Type	_Date	Qty	BalQty	dtdiff
IN	2018-01-01	10	10	43
OUT	2018-01-15	8	2	0
OUT	2018-02-12	2	0	0
OUT	2018-03-13	2	-2	0
OUT	2018-04-04	3	-5	0
IN	2018-05-24	10	5	5
IN	2018-05-28	6	11	0
STKADJ	2018-05-28	11	0	0
OUT	2018-08-29	6	-6	0
IN	2018-09-15	10	4	119
	2019-01-11	0	0	0
NULL	NULL	NULL	NULL	167
*/


 
  
   

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a47bec20-41a1-48c3-a03a-ee078649d44e/reorder-level-report-days-calculation-?forum=transactsql


String_Split and String_Agg (or Old way)

  
 
 CREATE TABLE [DNPA](
	[ComputerName] [varchar](20) NULL,
	[DistinguishedName] [varchar](256) NULL
);
GO

INSERT INTO [dbo].[DNPA] (ComputerName,DistinguishedName)
 
VALUES ( 'COMPUTER01', 'CN=COMPUTER01,OU=Computers,OU=Seattle,OU=PNW,DC=usa,DC=int' ) ,
       ( 'COMPUTER02', 'CN=COMPUTER02,OU=Computers,OU=Seattle,OU=PNW,DC=usa,DC=int' ) ,
       ( 'COMPUTER03', 'CN=COMPUTER03,OU=Computers,OU=Seattle,OU=PNW,DC=usa,DC=int' ) ,
       ( 'COMPUTER04', 'CN=COMPUTER04,CN=Computers,DC=spain,DC=int' ) ,
       ( 'COMPUTER05', 'CN=COMPUTER05,OU=Computers,OU=Terminal Support Services,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER06', 'CN=COMPUTER06,OU=Computers,OU=Planning Department,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER07', 'CN=COMPUTER07,OU=Computers,OU=Operations,DC=mexico,DC=int' ) ,
       ( 'COMPUTER08', 'CN=COMPUTER08,OU=Computers,OU=Information Services,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER09', 'CN=COMPUTER08,OU=Computers,OU=Security,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER10', 'CN=COMPUTER10,OU=Computers,OU=Gate,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER11', 'CN=COMPUTER11,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
       ( 'COMPUTER12', 'CN=COMPUTER12,OU=Computers,OU=Security,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER13', 'CN=COMPUTER13,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
       ( 'COMPUTER14', 'CN=COMPUTER14,OU=Computers,OU=Blocked-USB,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER15', 'CN=COMPUTER15,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
       ( 'COMPUTER16', 'CN=COMPUTER16,OU=Computers,OU=Training,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER17', 'CN=COMPUTER17,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
       ( 'COMPUTER18', 'CN=COMPUTER18,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
       ( 'COMPUTER19', 'CN=COMPUTER19,OU=Computers,OU=Planning Department,OU=Barcelona,DC=spain,DC=int' ) ,
       ( 'COMPUTER20', 'CN=COMPUTER20,OU=Computers,OU=Mexico City,DC=mexico,DC=int' ) ,
       ( 'COMPUTER21', 'CN=COMPUTER21,OU=Computers,OU=Planning Department,OU=Barcelona,DC=spain,DC=int' );
;
GO
;with mycte as (
SELECT s.ComputerName, s.DistinguishedName, [x].[value], 
 ROW_NUMBER() OVER (PARTITION BY s.ComputerName ORDER BY  x.rn DESC ) rn2 
FROM [DNPA] AS s
CROSS APPLY (
SELECT ss.[value], 
     ROW_NUMBER() OVER (PARTITION BY s.ComputerName ORDER BY s.ComputerName DESC) AS rn
FROM STRING_SPLIT(s.DistinguishedName, ',') AS [ss]
      ) AS x
	  )
---*****************SQL Server 2017	 using STRING_AGG  function 
/* 
 Select  ComputerName, 
 Stuff(STRING_AGG(stuff([value] ,1,3,'')
 , '\'  )  WITHIN GROUP ( ORDER BY rn2) ,charindex('\',STRING_AGG(stuff([value] ,1,3,'')
 , '\'  )  WITHIN GROUP ( ORDER BY rn2)),1,'.') newString
FROM  mycte
Group by ComputerName

*/


SELECT   m2.ComputerName
, STUFF((
        SELECT Case when rn2=2 then '.'  else '\' end + CAST(stuff(m.[value] ,1,3,'') AS NVARCHAR(MAX)) 
        FROM mycte m
        WHERE m.ComputerName = m2.ComputerName
        ORDER BY m.rn2 ASC
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS newString
FROM  mycte AS m2
GROUP BY ComputerName

 


 Drop TABLE [DNPA]
   

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ccc281f1-63b2-45a8-a990-66724ac65d67/how-to-use-cte-inline-with-select-clause?forum=transactsql


First Wednesday after the Second Tuesday In a Month

  
 
  DECLARE  @StartDate  DATETIME = '2019-01-01'
DECLARE @EndDate DATETIME = DATEADD(DAY,900,@StartDate)

DECLARE @CustomOrderTable TABLE (DayOfWeek INT, OrderInTable INT)
INSERT @CustomOrderTable VALUES (7,7),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6)


 
  
--****  create a Number table
;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)
  
 --  
,DateRange as
(
select  dateadd(day,n-1,@startdate) dt from Nums  
where dateadd(day,n-1,@startdate)<=@EndDate)  
 
 



,mycte as (
SELECT  *,DayName = DATENAME(WEEKDAY,dt), DayOfWeek = DATEPART(DW,dt),Week = DATEPART(WEEK,dt),
 Sum(case when   DATENAME(WEEKDAY,dt)='Tuesday' then 1 else 0 end) Over(Partition by Year(dt), month(dt)   order by dt) grp
FROM    DateRange DR)

--INSERT INTO YourTable


 Select dt,	DayName,  DR.DayOfWeek, Week,
 Year(dt) Year 
 ,Max(case when grp=2 and DayName='Wednesday' then dt else null end) Over(Partition by Year(dt), month(dt) )   First_Wednesday_after_the_SecondTuesday_In_a_Month
	from mycte DR INNER JOIN @CustomOrderTable CO ON CO.DayOfWeek=DR.DayOfWeek
 
ORDER BY dt
   

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d0bbb46c-d632-45a2-b825-4e3b18cb887d/first-wednesday-after-the-second-tuesday?forum=transactsql


A JSON Sample with TSQL

  
 
  --Use JSON function

Create table test(Id int ,col varchar(4000))
Insert into test values(1,'

')
 ;with mycte as (
 select col,  
  concat('[', Stuff(replace(replace(replace(replace(col,':','":"'),',','","'),'>','"}'),'<button ',',{"')  ,1,1,''),']') jsonInfo
 
 from test)

 ,mycte2 as (
 select *   from mycte
 CROSS APPLY OPENJSON (jsonInfo)
 with (text varchar(50),value  varchar(50))

 )

 select text as 'button.text',value as 'button.value'  
 from mycte2
 For JSON PATH

 
 drop table test
   

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9bd29ce3-6b20-44e7-8755-248abbd16665/sql-query-help?forum=transactsql