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


 
Advertisements

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
 
 
 
 

Custom Format Date–a Sample

  
 
  Select Cast(dateadd(day,n,DtMonday) as date) dt, DATENAME(WEEKDAY,dateadd(day,n,DtMonday)) WEEK_DAY 
,Format(dateadd(day,n,DtMonday),'MM/dd/yyyy (dddd)','en-US') theformatyouwant
from (
Select dateadd(week,datediff(week,0,getdate()),0) DtMonday ) t
cross apply(VALUES	(0),(1),(2),(3),(4),(5),(6)) d(n)
/*
dt	WEEK_DAY	theformatyouwant
2018-08-06	Monday	08/06/2018 (Monday)
2018-08-07	Tuesday	08/07/2018 (Tuesday)
2018-08-08	Wednesday	08/08/2018 (Wednesday)
2018-08-09	Thursday	08/09/2018 (Thursday)
2018-08-10	Friday	08/10/2018 (Friday)
2018-08-11	Saturday	08/11/2018 (Saturday)
2018-08-12	Sunday	08/12/2018 (Sunday)

*/


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33297de1-64bb-4f23-ad28-4a35de1eac63/getting-the-date-of-7-days-based-on-getdate?forum=transactsql