Create Contained Database from a BACPAC File

Posted: January 22, 2024 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

The target Instance needs to have this value to 1:

sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE;
GO

From the Azure SQL Database within SSMS:

>>Tasks>Export Data-tier Application>> to save exported database Bacpac file;

then Import to create a new database locally on a 2019 Instance from SSMS.

Expand Date Range

Posted: February 24, 2023 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

	CREATE table #TBL
	(DATE1 DATETIME
	,ID INT
	,Col1 Varchar(5)
	,Col2 Varchar(5)
	)
	Insert into #TBL values
	('12/1/22', 1001, 'abc', 'df')
	,('12/5/22', 1001, 'abc', 'def')
	,('12/5/22', 1002, 'dcb', 'ef')
	,('12/10/22', 1003, 'cdf', 'efg')
	,('12/10/22', 1001, 'abc', 'efg')
	declare @startdate date
	declare @enddate date

	Select @startdate = min(DATE1),@enddate = max(DATE1) from #TBL

	–**** create a Number table
	;WITH Num1 (n) AS (
	SELECT 1 as n
	UNION ALL SELECT n+1 as n
	FROM Num1 Where n <101),
	Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)

	,mycte as
	(
	select DATE1,ID,Col1,COl2, dateadd(day,n-1,@startdate) dt ,DATE2
	from Nums
	Cross apply (Select Distinct DATE1,ID,Col1,COl2 ,LEAD(DATE1) over (partition by ID,Col1 order by DATE1 ) DATE2 From #TBL ) a
	WHERE dateadd(day,n-1,@startdate)<=@enddate
	)
	,mycte2 as (
	Select DATE1, dt, c.ID,c.Col1,c.Col2 ,DATE2
	FROM mycte c
	WHERE c.dt<=@enddate –last date
	)
	select dt,ID,Col1,Col2

	from mycte2
	where dt>=DATE1 and (dt<DATE2 or DATE2 is null)
	Order by 2,1


	drop table #TBL

view raw Expand Date Range Query hosted with ❤ by GitHub

Select Rounding Datetime to the nearest 30 minutes

Posted: November 30, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

	create table #temp (ID varchar(50)
	,DateTime_ dateTime)

	insert into #temp (ID,DateTime_) values ('1001','2022-01-01 06:01:24.000')
	,('1002','2022-01-01 06:07:24.000')
	,('1003','2022-01-01 06:15:24.000')
	, ('1004','2022-01-01 06:20:24.000')
	,('1005','2022-01-01 06:39:24.000')
	, ('1006','2022-01-01 06:46:24.000')
	, ('1007','2022-01-01 16:59:24.000')

	select ID
	,datetimefromparts (Year(DateTime_),Month(DateTime_),day(DateTime_)
	,datepart(hour,DATEADD(hour, DATEDIFF(hour, 0,DateTime_)+(Case when floor(datepart(minute,DateTime_) /15.) =3 then 1 else 0 end), 0) )
	,Case when floor(datepart(minute,DateTime_) /15.) in(1,2) then 30 else 0 end
	,0
	,0
	) dtRounded30

	from #temp

	DROP Table #TEMP

view raw Select Rounding Datetime to the nearest 30 minutes.sql hosted with ❤ by GitHub

https://learn.microsoft.com/en-us/answers/questions/1108615/select-rounding-datetime-to-nearest-30-minutes.html

Import and Export Data with PowerBI Desktop

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

Get Data, thanks to Microsoft for providing so many ways to load data into PowerBI Desktop for further process. We have many ways to transfer data from one system to another. For many implementations, you can import and export data directly from these systems to meet various business requirements.

It is extremely easy to use Get Data from PowerBi Desktop from many sources(you can think about it). However, when you want these massaged data back to your original form, you may need to figure out a way to find the existing solutions developed by community developers.

A simple exporting data from Power BI operation is to copy and paste data.

Select table on the left navigation and right click inside table (any column) to show a list: choose Copy table and paste into a new excel worksheet or a new text file. The copy and past will have a size limit for how much data you can copy(less than 150000 rows or less if many columns need to be copied).

For large dataset, I tried to explore with free utilities:

One is to use Power BI Exporter(PowerBI free addon). To download all your data in a zip package (text files);

Another one is to use DAX Studio (Free tool). Export your data to SQL Server. You can do much much more with this tool if you want to explore DAX functions with this tool.

Why Query Store Not Using Forced Plan? What is “Morally Equivalent Execution Plan”?

Posted: August 23, 2022 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

https://littlekendra.com/2018/03/12/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/

Custom row number (dense_rank)

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

https://gist.github.com/jingyang-li/07a4bbfe21c6637bfbd8caf330cb8f10

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

Jingyang Li

SQL Server