jingyang-li

Declare @rept  table(Id  int Identity(1,1), ReptDesc varchar(max))
Insert into @rept (ReptDesc)
Values(
'Category: Projects; Accounting and Operations; Sales 
Description: this is a transaction report'),
('Category: Technical, Accounting
Description:'),
('Category: Accounting'),
('Category: Accounting, Sales'),
(Null),

jingyang-li

create table WeekSales(dt datetime, Amount decimal(8,2))
insert into WeekSales values(getdate()-15, 100),(getdate()+0, 100)
,(getdate()-14, 100),(getdate()-23, 100),(getdate()-3, 100),(getdate()-3, 100)
,(getdate()-23, 100),(getdate()+41, 100),(getdate()+11, 100),(getdate()+1, 100)
,(getdate()-52, 100),(getdate()+10, 100),(getdate()+20, 100),(getdate()+0, 100)
,(getdate()-61, 100),(getdate()+53, 100),(getdate()+33, 100),(getdate()+3, 100)
,(getdate()+64, 100),(getdate()+43, 100),(getdate()+46, 100),(getdate()+6, 100)
,(getdate()+53, 100),(getdate()+33, 100),(getdate()+13, 100),(getdate()+12, 100)
,(getdate()+42, 100),(getdate()+23, 100),(getdate()+23, 100),(getdate()+24, 100)

jingyang-li

 Declare @table1
  table(TableId int, SId int, Data varchar(200), DataOwner int)
 insert into @table1 values
  (1, 10, 'aaaa', 1001)
 ,(2, 10, 'aaaa', 1000)
 ,(3, 20, 'bbbb', 1000)
 ,(4, 10, 'ccc', 2000)

;with mycte as (

jingyang-li

declare @rundt datetime=getdate()
If month(@rundt) not  in (1,4,7,10)  
THROW 51000,'Job cannot be executed on this month',1;

jingyang-li

#Import-Module dbatools
#Import-Module Az

#Create a new Container or use an existing one
$ContainerName= "devbackup"

#Create a new Resource Group or use an existing one
$ResourceGroupName  = "rgdba"

#Create a new Storage Account or use an existing one

jingyang-li

Drop table if exists DateTimeTable
Drop table if exists DateTableTime
Create table DateTimeTable (id int identity(1,1) primary key, mydatetime datetime)
Create table DateTableTime (id int identity(1,1) primary key, mydate date, mytime time)

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 

jingyang-li

 

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)

jingyang-li

--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'

jingyang-li

--Use Number values from master.dbo.spt_values
--JSON 1
;with mycte as (
    SELECT
        [KEY] theKey,
        Value TheValue
    from
        OpenJson (
            (
                SELECT

jingyang-li

create or alter FUNCTION [dbo].[kaprekar](@n int)
 RETURNS bit
 AS
 BEGIN
 DECLARE @result bit;
 Begin
 If exists ( 
 select 1
 from 
 (select @n number,   square_value,