One of Date Island Solutions

Posted: May 27, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

Question is from here:

https://docs.microsoft.com/en-us/answers/questions/860297/find-first-remaining-date-range-record-within-main.html

I have come up with one solution:

https://gist.github.com/jingyang-li/2efff23413bec8bc201492a742e0a875

Improved String_Split Function in SQL Server 2022

Posted: May 24, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

select @@version
/* Microsoft SQL Server 2022 (CTP2.0) – 16.0.600.9 (X64) May 20 2022 13:29:42
Copyright (C) 2022 Microsoft Corporation Enterprise Evaluation Edition (64-bit)
on Windows 10 Pro 10.0 (Build 19044: )
*/
declare @s varchar(100)=’1,2,4,2,5,3,6,7′

select * from string_split(@s,’,’,1)
/*
value ordinal
1 1
2 2
4 3
2 4
5 5
3 6
6 7
7 8

SQL Server 2022 Public Preview(CPT2.0)

Posted: May 24, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

Bob Ward announced from Linkedin post: the long wait SQL Server Public Preview is available for download today(May 24, 2022).

Here is the link I used to get my local copy:

https://info.microsoft.com/ww-landing-sql-server-2022.html

Will play with the new features in next couple of days and dive into it.

Split Values into Columns –XML Solution

Posted: May 12, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

	–Yitzhak Khabinsky's solution
	–https://docs.microsoft.com/en-us/answers/questions/845428/need-sql-help-to-split-comma-separated-value-into.html
	create table #test
	(col VARCHAR(2000))
	insert into #test
	values('2292,0192,20211018,0778988158494,0000600,150,000000000000001')
	,('2292,14642,20210923,630996500903,0000500,061,1')
	,('2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917')


	–DECLARE @separator CHAR(1) = ',';

	SELECT c.value('(/root/r[1]/text())[1]', 'INT') AS col1
	, c.value('(/root/r[2]/text())[1]', 'VARCHAR(10)') AS col2
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col3
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col4
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col5
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col6
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col7
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col8
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col9
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col10
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col11
	, c.value('(/root/r[3]/text())[1]', 'DATE') AS col2
	, c.value('(/root/r[13]/text())[1]', 'VARCHAR(20)') AS col13
	FROM #test AS t
	CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +
	REPLACE(col, ',', ']]></r><r><![CDATA[') +
	']]></r></root>' AS XML)) AS t1(c);

	Drop table #test

view raw Split_CommaSeparated_Column_XML_Split.sql hosted with ❤ by GitHub

Quarterly Data Summary with T-SQL

Posted: May 12, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

	create table datatable (TrendType varchar(12), date date, sales int)
	insert into datatable values ('Monthly','5/1/2021',1234),
	('Monthly','3/1/2021',1234),
	('Monthly','2/1/2022',1234),
	('Monthly','5/1/2022',1234),
	('Monthly','8/1/2021',1234),
	('Monthly','8/1/2022',1234),
	('Monthly','12/1/2021',1234),
	('Monthly','11/1/2022',1234)

	select 'Quarterly' TrendType, DATEADD(Q, DATEDIFF(Q, 0, [date]) , 0) as [date],
	Sum(sales) Sales from datatable
	group by DATEADD(Q, DATEDIFF(Q, 0, [date]) , 0)
	order by DATEADD(Q, DATEDIFF(Q, 0, [date]) , 0)


	drop table datatable

view raw Quarterly_Data_TSQL.sql hosted with ❤ by GitHub

Retrieve JSON File Values

Posted: May 10, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

A question from Q&A:

https://docs.microsoft.com/en-us/answers/questions/823494/json-file-to-sql-table.html

Here are a few ways to get the result:

https://gist.github.com/jingyang-li/644d75ffa21234044d9442e01c027b31

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, WorkAddress VARCHAR(MAX));
INSERT INTO @t VALUES
(’89 Oliver”s Yard’ + CHAR(13) +
’21 City Rd’+ CHAR(13) +
‘London’+ CHAR(13) +
‘EC1Y 1HP’);

;with mycte as (
SELECT id, ‘[“‘+ REPLACE(WorkAddress, CHAR(13), ‘”,”‘) + ‘”] ‘ jsCol
FROM @t
)

select distinct id
,JSON_VALUE(jsCol, ‘$[0]’) AS Address1
,JSON_VALUE(jsCol, ‘$[1]’) AS Street
,JSON_VALUE(jsCol, ‘$[2]’) AS City
,JSON_VALUE(jsCol, ‘$[3]’) AS Postcode
FROM mycte
cross apply openjson(jsCol ) as j

–Select id
–,max(Case when [key]=0 then value else null end) LVL_1
–,max(Case when [key]=1 then value else null end) LVL_2
–,max(Case when [key]=2 then value else null end) LVL_3
–,max(Case when [key]=3 then value else null end) LVL_4

–FROM mycte
–cross apply openjson(jsCol ) d

–group by id

A T-SQL Solution for a Question on Q&A

Posted: May 10, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

I came across a question “Need Help with SQL UPDATE where the Target and Source have a many to many relationship” on Microsoft Q&A. It is a complicate question and it took some time time to figure out the right solution after a few attempts. I posted this link here to record how I solved the problem.

By the way, I am a volunteer moderator on the site so I can see all revisions from all replies on the question.

https://gist.github.com/jingyang-li/2c5ea23a934c59641717c87f5831adff

Check Kaprekar number UDF with T-SQL

Posted: May 8, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

A simplified version with T-SQL to find Kaprekar number under 1000.

https://gist.github.com/jingyang-li/f6a472cfb724e7a14035d7321ea5c8cc

FizzBuzz with T-SQL

Posted: May 2, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

There many ways to solve this problem and there are multiple ways to provide decent solutions in SQL Server as well. For the number in question we try to solve, any solution should work just fine. In SQL Server (database in general), we are expecting a SET based solution for obvious reasons. I think this thinking is important and that can help to prepare query solutions in a right direction for query performance optimization in applications.

I have compiled a few ways to solve FizzBuzz quiz in the following for my own reference:

FizzBuzz_Number_XML.sql



	DECLARE @myFizzBuzz varchar(max)

	;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 @myFizzBuzz =
	(
	STUFF(
	(SELECT char(9)+char(10)+
	CASE WHEN n % 15 = 0 THEN 'FizzBuzz'
	WHEN n % 5 = 0 THEN 'Buzz'
	WHEN n % 3 = 0 THEN 'Fizz'
	ELSE CAST(n AS VARCHAR(8)) END
	FROM Nums Where n<1000
	FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '')
	);
	PRINT @myFizzBuzz;

view raw FizzBuzz_Number_XML.sql hosted with ❤ by GitHub

FizzBuzz_ThreeLoop.sql

	–While_Loop
	DECLARE @counter INT
	DECLARE @output VARCHAR(max)=''
	SET @counter = 1
	WHILE @counter < 101
	BEGIN
	SELECT @output+=
	CASE WHEN @counter % 15 = 0 THEN 'FizzBuzz'
	WHEN @counter % 5 = 0 THEN 'Buzz'
	WHEN @counter % 3 = 0 THEN 'Fizz'
	ELSE CAST(@counter AS VARCHAR(8))
	END + char(9)+char(10)
	SET @counter = @counter + 1
	END

	Print @output





	—- DO_WHILE
	DECLARE @counter INT
	DECLARE @output VARCHAR(max)=''
	SET @counter = 1
	BEGIN

	Starting:

	SELECT @output+=
	CASE WHEN @counter % 15 = 0 THEN 'FizzBuzz'
	WHEN @counter % 5 = 0 THEN 'Buzz'
	WHEN @counter % 3 = 0 THEN 'Fizz'
	ELSE CAST(@counter AS VARCHAR(8))
	END + char(9)+char(10)

	SET @counter += 1

	IF @counter <=100
	GOTO Starting ;

	Print @output

	END


	—- REPEAT_UNTIL
	DECLARE @counter INT
	DECLARE @output VARCHAR(max)=''
	SET @counter = 1
	BEGIN
	Starting:

	SELECT @output+=
	CASE WHEN @counter % 15 = 0 THEN 'FizzBuzz'
	WHEN @counter % 5 = 0 THEN 'Buzz'
	WHEN @counter % 3 = 0 THEN 'Fizz'
	ELSE CAST(@counter AS VARCHAR(8))
	END + char(9)+char(10)
	SET @counter = @counter + 1

	IF NOT(@counter >100)
	GOTO Starting ;

	END

	Print @output

view raw FizzBuzz_ThreeLoops.sql hosted with ❤ by GitHub

FizzBuzz_JSON_Solutions.sql

	–Use Number values from master.dbo.spt_values
	–JSON 1
	;with mycte as (
	SELECT
	[KEY] theKey,
	Value TheValue
	from
	OpenJson (
	(
	SELECT
	CASE
	WHEN Number % 15 = 0 THEN 'FizzBuzz'
	WHEN Number % 5 = 0 THEN 'Buzz'
	WHEN Number % 3 = 0 THEN 'Fizz'
	ELSE CAST(Number AS VARCHAR(8))
	END myFizzBuzz
	FROM
	(
	SELECT
	Number
	FROM
	master.dbo.spt_values
	where
	type = 'P'
	and Number >= 1
	and Number <= 1000 –maxnumber 0-2047
	) FizzBuzz For JSON AUTO
	)
	)
	WHERE
	type = 5
	)
	select
	unpvt.Value
	from
	mycte AS src
	CROSS APPLY OpenJson(src.TheValue) AS unpvt
	order by theKey



	–JSON 2
	;with mycte as (
	SELECT (
	SELECT Vals = JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(Col, 'json'), '","') + '"]')
	FOR JSON PATH
	) jsCol
	FROM (SELECT
	CASE WHEN Number % 15 = 0 THEN 'FizzBuzz'
	WHEN Number % 5 = 0 THEN 'Buzz'
	WHEN Number % 3 = 0 THEN 'Fizz'
	ELSE CAST(Number AS VARCHAR(8)) END Col

	FROM master.dbo.spt_values
	where type='P' and Number >=1 and Number <=1000 –maxnumber 0-2047
	) FizzBuzz
	)
	–select jsCol from mycte

	select unpvt.value
	–unpvt.value,unpvt.[key]
	FROM mycte AS src
	CROSS APPLY OpenJson(jsCol) j
	Outer APPLY OpenJson(j.value) d
	CROSS APPLY OpenJson(d.value) AS unpvt
	Order by j.[Key]

view raw FizzBuzz_JSON_Solutions.sql hosted with ❤ by GitHub

Jingyang Li

SQL Server