jingyang-li

 A sample code from MSDN question

jingyang-li

CREATE TABLE [dbo].[productdetails](
[productid] [int] NULL,
[Productrstartdate] [date] NULL,
[Productenddate] [date] NULL,
[EMIInstallment] [int] NULL
)  
GO
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment]) 
VALUES (1, CAST(N'2020-10-02' AS Date), CAST(N'2024-10-02' AS Date), 5)
,(2, CAST(N'2020-02-10' AS Date), CAST(N'2021-02-10' AS Date), 2)

jingyang-li

CREATE TABLE #tmpTable (
    Friday varchar(50),
    Monday varchar(50),
    Saturday varchar(50),
    Sunday varchar(50),
    Thursday varchar(50),
    Tuesday varchar(50),
    Wednesday varchar(50),
    fk_incomes int,

jingyang-li

declare @s varchar(max) = 'Hi my name is M and I have two sister 111) M and 2111) K'---, 3333) W';
 
select string_agg([value],' ') newString 
from openjson('["'+replace(@s ,' ', '","') + '"]')
where [Value] not like '%[0-9])%'

jingyang-li

declare @s varchar(max) = 'Hi my name is M and I have two sister 1) M and 2) K'
 
;with mycte as (
select  Cast(N'<H><r>'+replace(@s ,' ', '</r><r>') + '</r></H>'  AS XML) vals
)
,mycte2 as (
 SELECT ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn 
 ,S.a.value('.', 'VARCHAR(100)') AS splitVal  
 FROM mycte d
 CROSS APPLY d.[vals].nodes('/H/r') S(a)

jingyang-li

 IF OBJECT_ID (N'NthDayOfWeekOfMonth') IS NOT NULL
           DROP FUNCTION NthDayOfWeekOfMonth
        GO
        
        CREATE FUNCTION NthDayOfWeekOfMonth (
        @TheYear CHAR(4), --the year as four characters (e.g. '2014')
        @TheMonth CHAR(3), --in english (Sorry to our EU collegues) e.g. Jun, Jul, Aug
        @TheDayOfWeek CHAR(3), -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun
        @Nth INT) --1 for the first date, 2 for the second occurence, 3 for the third
        RETURNS DATETIME

jingyang-li

Create Table test
( year int, month int,CustomerID int, AccountNum int, Amount int)
GO
Insert test 
values 
(2019,11,14,1,200),
(2019,11,20,1,500),
(2019,11,20,2,25),
(2019,12,20,1,670),

jingyang-li

https://stackoverflow.com/questions/23166423/how-to-create-view-for-all-tables-in-database

 
DECLARE @SQL nvarchar(MAX)
SET @SQL = N''

SELECT
    @SQL = @SQL + 
    N'IF EXISTS(SELECT 1 FROM sys.objects WHERE name = ''v_' + t.[TABLE_NAME] + ''' AND type = ''V'') BEGIN DROP VIEW [v_' + t.[TABLE_NAME] + '] END 
	EXEC(''CREATE VIEW [v_' + t.[TABLE_NAME] + N'] AS SELECT ' + 

jingyang-li

    // Configure WinSCP with winscp.ini file  export from GUI to generate this file from working machine
    //in c:\Program Files (x86)\WinSCP\  folder
    //copy this ini file to transfer site settings
    
                string logname = "D:\\data\\log\\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_log.xml";
                //// Run hidden WinSCP process
                Process winscp = new Process();
                winscp.StartInfo.FileName = "c:\\Program Files (x86)\\WinSCP\\winscp.com";

                winscp.StartInfo.Arguments = "/log=\"" + logname + "\"";

jingyang-li

CREATE TABLE #TEST
(OrderITEM NVARCHAR(MAX), CustomerId INT, OrderDate DATETIME)
GO
INSERT INTO #TEST(OrderITEM, CustomerId,OrderDate)
VALUES('A-100 A-105 C-100' ,1,Getdate()-30),
   ('A-111 A-102 D-201' ,2,Getdate()-5),
   ('D-400 G-10 F-220' ,3,Getdate()-20),
     ('H-801 M-451 D-201' ,1,Getdate()-3),
     ('F-701 A-102 D-201' ,4,Getdate()-6),