jingyang-li

 IF(OBJECT_ID('tempdb..#trans') IS NOT NULL) DROP TABLE #trans;
      create table #trans (acct_nbr varchar(128), tran_dt datetime, tran_amt decimal(38,2), tran_type1 varchar(128), tran_type2 varchar(128), tran_id varchar(128), tran_id_decline varchar(128))
        
  insert into #trans (acct_nbr, tran_dt, tran_amt, tran_type1, tran_type2, tran_id
  )
  values 
  ('111', '2022-05-08 11:08:47.257', '1.00', 'C', 'X', 'ID-01'),
  ('111', '2022-05-08 11:08:47.257', '1.00', 'A', '', 'ID-02'),
  ('111', '2022-05-09 11:10:47.257', '1.00', 'A', '', 'ID-03'),
  ('111', '2022-05-09 11:11:11.257', '1.00', 'P', 'O', 'ID-04'),

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,   

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

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

 

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

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

#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

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

 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

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)