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

Advertisements

Check a Stored Procedure Last Runtime

SELECT DB_NAME(database_id)
,OBJECT_NAME(object_id)
,cached_time
,last_execution_time
,execution_count
FROM sys.dm_exec_procedure_stats

WHERE object_name( object_id) =’my Stored Procedure Name’


Consecutive Dates with Exclusion

  
 
  



 CREATE TABLE [dbo].[PaidTimeOff](
[StaffCode] [varchar](50) NULL,
[VacationDate] [datetime] NULL
) ON [PRIMARY]
GO


INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-03-21 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-05-07 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-05-08 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',
'2018-05-09 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-06-29 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-02 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-03 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-05 00:00:00')
INSERT INTO PaidTimeOFF (StaffCode, VacationDate) VALUES ('L.Johnson',  '2018-07-06 00:00:00')
  
  

declare @startdate date
declare @enddate date  
 
Select @startdate = min(Vacationdate),@enddate = max(Vacationdate) from [PaidTimeOff] 
 
--****  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)
 
 --  
,myStaffCodeDate as
(
select  StaffCode,dateadd(day,n-1,@startdate) dt from Nums 
Cross join (Select Distinct StaffCode From  [PaidTimeOff] ) a
where dateadd(day,n-1,@startdate)<=@enddate)       
 
,mycte as (
 select m.StaffCode, m.dt,
 Case when datepart(weekday,m.dt) in (1,7) 
 or (datepart(month ,m.dt)=7 and datepart(day,m.dt)=4) 
 then m.dt else p.VacationDate  end newDt
 from myStaffCodeDate m Left join [PaidTimeOff] p
 on m.StaffCode=p.StaffCode and m.dt=p.VacationDate)

 ,mycte2 as (select StaffCode,newdt, dt, DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY  newDt),  newDt) grp
 from mycte

 )

 Select StaffCode, min(newdt) firstDay, max(newdt) lastDay,
  count(*) TotalDaysOff 
  from mycte2
  where  datepart(weekday, dt) Not in (1,7) 
 and  not  (datepart(month , dt)=7 and datepart(day, dt)=4) 
 and newdt is not null
 group by StaffCode, grp

 drop table [PaidTimeOff]



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0976edae-dc2d-486d-baa3-945fad0ea577/consecutive-dates?forum=transactsql


Assign Custom Sequence with T-SQL

Add number 20 between different group and continue the sequence.

Create TABLE test(SNo int identity(1,1),StudentID INT,RollNumber int
, Course CHAR(1))

INSERT INTO test (StudentID,Course)
VALUES (123,’A’),(124,’A’),(125,’A’)
,(126,’B’)
,(127,’B’),(128,’B’)
,(129,’C’)
,(130,’C’),(131,’C’)
,(132,’D’)
,(133,’D’),(134,’D’)

;with mycte as (
Select SNo,Course,
dense_rank() OVER(ORDER BY Course) dnk,
ROW_NUMBER() OVER(Partition by Course ORDER BY SNO) rn
,10*Ceiling(count(*)Over(Partition by Course)/10.) cnt
from test
)
,mycte2 as (
select * ,lag(cnt) Over(order by sno) cntlag
from mycte)

select SNo, Course
, Case when dnk=1 then rn else
sum(case when rn =1 then cntlag else 0 end)Over(Order by SNo)+rn +20*(dnk-1) -1 end RollNumber

from mycte2

–Or this

;with mycte as (
Select SNo,Course,
dense_rank() OVER(ORDER BY Course) dnk,
ROW_NUMBER() OVER(Partition by Course ORDER BY SNO) rn
,10*Ceiling(count(*)Over(Partition by Course)/10.) cnt
from test)

select SNo,Course,
case when dnk=1 then rn else
rn+20*(dnk-1)+Sum(Case when rn=1 then ISNULL(cnt2,0) else 0 end) Over(Order by sno) -1 end cnt22
from mycte m
outer apply( select top 1 cnt cnt2 from mycte where dnk<m.dnk order by dnk desc) d

order by sno

drop table test

https://forums.asp.net/t/2146815.aspx?SQL+Server+Query+for+Partitioning+Data


STRING_AGG,STRING_SPLIT to Order List Within a Column (SQL Server 2017)

  
 
  ---SQL Server 2017
Create TABLE test
	( id int,unOrderCol VARCHAR(200) 
	);

INSERT	Test(id,unOrderCol) 
VALUES (1,'4,8,5,7'),(2,'9,3,7'),(3,null)

 SELECT	id
 , STRING_AGG(value, ',') WITHIN GROUP ( ORDER BY CAST(value AS INT)) orderCol
 FROM	test  
Cross apply STRING_SPLIT(unOrderCol, ',')
Group by id  
 
drop table test


 

Get Six Digits Coupon Code (unique)

Create table test (id int, pinCode varchar(6))

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n between 1 and 100),
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 *, row_number() Over(partition by Pincode Order by n) rn
 from (Select Left(Convert(varchar(36),NEWID()),6) as pincode ) t
cross apply (select n from nums WHERE n<10000) d(n)

)


Select * 
into myRandomCodeTable
from mycte 
where rn=1
 
 Select * from   myRandomCodeTable


--drop table   myRandomCodeTable
drop table test 
 
 
 
 

Get Six Digits Coupon Code (4 letters and 2 numbers)

 
 --create  table myRandomTable (id int identity(1,1),
--c1 char(1),
--c2 char(1),
--c3 char(1),
--c4 char(1),
--c5 char(1),
--c6 char(1)
--,RandomCode char(6))
--go

--===== 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 between 1 and 100),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,singleMix as (
select  cast(n-1 as char(1) ) c from nums
where (n between 1 and 10 ) 
union all
select   Char(ascii('A')+n-1) c from nums
where (n between 1 and 26 )  
)

,singleMix5 as (Select top 1 c from singleMix ORDER BY NEWID())
,singleMix6 as (Select top 1 c from singleMix ORDER BY NEWID())

,singleMix1 as (Select top 1 c from singleMix ORDER BY NEWID())
 ,singleMix2 as (Select top 1 c from singleMix ORDER BY NEWID())
 ,singleMix3 as (Select top 1 c from singleMix ORDER BY NEWID())
 ,singleMix4 as (Select top 1 c from singleMix ORDER BY NEWID())
 
 ,mix2 as (
 Select 
 m1.c c1
 ,m2.c c2
 ,m3.c c3
 ,m4.c c4
 ,m5.c c5
 ,m6.c c6
  from singleMix1 m1,singleMix2 m2,singleMix3 m3,singleMix4 m4
 ,singleMix5 m5,singleMix6 m6)
 
  Insert into myRandomTable (  RandomCode )
 Select   concat(c1,c2,c3,c4,c5,c6) RandomCode from mix2
  WHERE   isnumeric(c1)
 +isnumeric(c2)
 +isnumeric(c3)
 +isnumeric(c4)
 +isnumeric(c5)
 +isnumeric(c6)=2

 go 50000



 select count(*) from myRandomTable  


 --drop table myRandomTable


  ;with mycte as (
 select *, row_number() Over(partition by RandomCode Order by RandomCode) rn  
 from myRandomTable
 )

 delete from mycte where rn>1

 select top 15000 randomCode    from myRandomTable