Transfer Table Schema From one to dbo

 

  

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

set @oldschema = 'IIS APPPOOL\User1'
set @newschema = 'dbo'

while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
begin
select @table = name from sys.tables 
where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)

set @sql = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].' + @table

exec(@sql)
end
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc3ec5b6-282c-4b6f-8161-ebb754bffc49/change-schema-sql-2016?forum=transactsql

Original Script from:
https://www.sqlservercentral.com/Forums/654301/change-old-schema-name-into-new-schema-name


Split records, separate the overlapped dates

  
 DECLARE @TBL_TEMP TABLE(MBR_NBR VARCHAR(100),PLAN_ID VARCHAR(100),MBR_ELIG_EFFDTE DATE,MBR_ELIG_TRMDTE DATE)

INSERT @TBL_TEMP

SELECT '101162464','MCR000','2016-01-01','2017-12-31' UNION
SELECT '101162464','MCRMCD08','2016-08-01','2017-04-30' UNION

SELECT '14601938','MCRADV02','2016-01-01','2016-04-30' UNION
SELECT '14601938','MCRMCD02','2016-05-01','2017-04-30' UNION
SELECT '14601938','MCR000','2017-05-01','2017-12-31' 

 

;with mycte as (
SELECT MBR_NBR,
Case when n=1 then lead(PLAN_ID) Over(Partition by MBR_NBR  Order by dt ) else PLAN_ID end PLAN_ID,
dateadd(day,n,dt) MBR_ELIG_EFFDTE, n,n2,
dateadd(day,-lead(n2) Over(Partition  by MBR_NBR Order by dt ),lead(dt) Over(Partition by MBR_NBR Order by dt ))  MBR_ELIG_TRMDTE

 FROM @TBL_TEMP
cross apply (values(0,1,MBR_ELIG_EFFDTE),(1,0,MBR_ELIG_TRMDTE)  ) d(n,n2,dt)
)

Select  MBR_NBR,PLAN_ID, MBR_ELIG_EFFDTE,MBR_ELIG_TRMDTE 
--,Row_number() Over(Partition by MBR_NBR,MBR_ELIG_EFFDTE Order by MBR_ELIG_TRMDTE desc)  rn
--,n,n2
 from mycte 
 WHERE MBR_ELIG_TRMDTE is not null  and MBR_ELIG_EFFDTE < MBR_ELIG_TRMDTE
order by   MBR_NBR, MBR_ELIG_EFFDTE



-- expected resultset should like below
DECLARE @TBL_TEMP2 TABLE(MBR_NBR VARCHAR(100),PLAN_ID VARCHAR(100),MBR_ELIG_EFFDTE DATE,MBR_ELIG_TRMDTE DATE)

INSERT @TBL_TEMP2

SELECT '101162464','MCR000','2016-01-01','2016-07-31' UNION
SELECT '101162464','MCRMCD08','2016-08-01','2017-04-30' UNION
SELECT '101162464','MCR000','2017-05-01','2017-12-31' UNION

SELECT '14601938','MCRADV02','2016-01-01','2016-04-30' UNION
SELECT '14601938','MCRMCD02','2016-05-01','2017-04-30' UNION
SELECT '14601938','MCR000','2017-05-01','2017-12-31' 


SELECT * FROM @TBL_TEMP2 ORDER BY 1,3


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4897356c-a293-414e-9af6-672d7ab7e078/split-records-separate-the-overlapped-dates?forum=transactsql


Pivot With Case For Two Groups

  

CREATE TABLE mytable(
   YEAR INTEGER  NOT NULL  
  ,ID   INTEGER  NOT NULL
  ,NBR  INTEGER  NOT NULL
  ,PT   INTEGER  NOT NULL
  ,AMT  INTEGER  NOT NULL
);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,1,26,2000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,2,27,1000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,3,25,3000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,11,55,5000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,13,56,1000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,11,58,5000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,12,57,8000);
 

 Declare @sql as NVarchar(max)=null  
declare @ColumnHeaders NVARCHAR(MAX) 

;with mycte
as(
select *, row_number() Over(Partition by ID Order by PT) rn from mytable)

Select @ColumnHeaders = STUFF( (SELECT  ',' + 'MAX(CASE WHEN rn=' + Cast(rn as varchar(5))+ ' THEN PT else 0 end ) as ' + quotename('PT'+ Cast(rn as varchar(2)),'[')  + char(10)+char(13)
  FROM mycte 
  Group by rn
  Order by rn FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') +
 ','+STUFF( (SELECT    ',' +'MAX(CASE WHEN rn=' + Cast(rn as varchar(5))+ ' THEN AMT else 0 end ) as ' + quotename('AMT'+ Cast(rn as varchar(2)),'[')  + char(10)+char(13)
 FROM mycte    Group by rn Order by rn  FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') 
  +' ,SUM(AMT) as ''Total''';
 --print @ColumnHeaders
 
Set @sql  =' ;with mycte
as(
select *, row_number() Over(Partition by ID Order by PT) rn from mytable)
Select [Year],ID,'+   @ColumnHeaders + ' from mycte Group by [Year],ID ';
     
--print @sql
EXEC(@SQL)

 
 

drop table mytable

/*
Year	ID	PT1	PT2	PT3	PT4	AMT1	AMT2	AMT3	AMT4	Total
2016	63	25	26	27	0	3000	2000	1000	0	6000
2015	66	55	56	57	58	5000	1000	8000	5000	19000
*/

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e41a7ac5-36e3-4772-b042-e45264c8e44d/sql-help?forum=transactsql


Date Range with Category

  

DECLARE @Activities TABLE
(
  Department varchar(100),
  Activity varchar(100), 
  StartDateCampus1 Date,
  StartDateCampus2 Date

)
declare @dt datetime='2017-07-25'

INSERT INTO @Activities values
('Physics','Enrolment',@dt - 180,@dt - 120),
('Physics','StartofClasses',@dt - 90,@dt - 60),
('Physics','LastClassDate',@dt - 15,@dt- 1)

 
declare @startdt1 datetime
declare @startdt2 datetime  

--get start dates for  both campus
;with mycteDt as (
Select  StartDateCampus1  dt from @Activities
UNION ALL
Select  StartDateCampus2 from @Activities)
 
Select @startdt1 = DATEADD(month, DATEDIFF(month,0,min(dt)),0) 
,@startdt2 = DATEADD(month, DATEDIFF(month,0,max(dt)),0) 
from mycteDt 

--****  create a Number table
;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)
  
 --Departments and Dates  
,myDateDepartments as
(
select Department, n as rn, dateadd(month,n-1,@startdt1) dt 
from Nums ,(Select Distinct Department From  @Activities) a
   
)

,mycte2 as (
Select m.Department, rn, a.Activity Activity1, b.Activity Activity2
,dt, a.StartDateCampus1 , b.StartDateCampus2 
from myDateDepartments m
LEFT JOIN    @Activities a 
ON DATEADD(month, DATEDIFF(month,0,a.StartDateCampus1),0)=m.dt  and m.Department=a.Department
LEFT JOIN    @Activities b 
ON DATEADD(month, DATEDIFF(month,0,b.StartDateCampus2),0)=m.dt and m.Department=b.Department
 
 WHERE m.dt<=@startdt2 --last date
 )

 Select Department, Stuff(Convert(varchar(11), m.dt,100),4,4,'-') as [Month],d1.Activity1 as [Activity(Campus 1)], d2.Activity2 as [Activity(Campus 2)]
 from mycte2 m
Outer apply (select top 1 Activity1 from mycte2 m2 
WHERE m2.rn<=m.rn and Activity1 is not null  
Order by rn DESC) d1(Activity1) 
Outer apply (select top 1 Activity2 from mycte2 m2 
WHERE m2.rn<=m.rn and Activity2 is not null  
Order by rn DESC) d2(Activity2) 
/*
Department	Month	Activity(Campus 1)	Activity(Campus 2)
Physics	Jan-2017	Enrolment	NULL
Physics	Feb-2017	Enrolment	NULL
Physics	Mar-2017	Enrolment	Enrolment
Physics	Apr-2017	StartofClasses	Enrolment
Physics	May-2017	StartofClasses	StartofClasses
Physics	Jun-2017	StartofClasses	StartofClasses
Physics	Jul-2017	LastClassDate	LastClassDate

*/
 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/422f62fd-f775-4838-9cda-f5cb2ff792b5/need-to-split-data-as-per-dates?forum=transactsql


Island Group, Regroup Sample Query

  

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
)
;
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','COMP');
INSERT INTO #temp
VALUES('64074558792','20160811','20160813','Re-Activattion','N-CO');
 
--INSERT INTO #temp
--VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
--INSERT INTO #temp
--VALUES('64074558792','20160911','20160911','Re-Activattion','COMP');
--INSERT INTO #temp
--VALUES('64074558792','20160912','20160913','Re-Activattion','N-CO');
--;

;WITH Src AS (
SELECT Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
 ROW_NUMBER() OVER(PARTITION BY Identifier,SN_Status ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) AS rn,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) -
 ROW_NUMBER() OVER(PARTITION BY Identifier,CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END ORDER BY CreatedDate, CompletedDate) AS grp
 FROM #temp
 
)
,Grouped as (
select * , dense_rank()Over(Partition by Identifier,SN_Status  Order by grp) grp2
from Src)

Select Identifier,    
Case when SUM(CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END) >0 then 
CAST( DATEDIFF(day,min(CreatedDate) ,max(CreatedDate)  )  AS VARCHAR(25))
ELSE 'NOT COMPLETED' END AS RE_ACT_COMPLETION_TIME 

,Sum(CASE WHEN SN_Status = 'N-CO' THEN 1 ELSE 0 END) as [RE-AN NCO #]

,Sum(CASE WHEN SN_Status = 'PARTIALLY' THEN 1 ELSE 0 END) as [RE-AN PART#]
 
from Grouped
Group by Identifier, grp2 
order by min(CreatedDate)


drop table #temp


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50edadfb-60d8-407c-b72e-d024e83fbf8e/rownumber-over-partition-assistance?forum=transactsql


Delete Rows with Conditions

 

  

create table #trp (SQID varchar(10), SPID varchar(10), Group1 varchar(10), startdate datetime, enddate datetime, cost integer)
insert into #trp values ('2310','A1','120A','2017-01-01','2017-10-01',10)
insert into #trp values ('2310','B1','120A','2016-01-01',NULL,10)
insert into #trp values ('2310','D1','120A','2017-08-01',NULL,30)  /*This should not delete*/ 
insert into #trp values ('2310','A1','121A','2017-08-01',NULL,30)
insert into #trp values ('2310','B1','121A','2017-08-01',NULL,20)
insert into #trp values ('2310','D1','121A','2017-08-01',NULL,40);

&nbsp;

 ;with mycte as ( 
   SELECT *, ROW_NUMBER() OVER ( partition by SQID,group1 ORDER BY startdate) rn
        FROM    #trp
		) 
,mycte2 as ( 
select  SQID, SPID, Group1,startdate,enddate,cost
 , max(enddate) Over(PARTITION BY SQID,Group1) maxEnddate 
 , SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY SQID) cntDistinct 
 FROM  mycte)
 
 delete from mycte2
 --select * from mycte2
 where startdate &gt; getdate() and  maxEnddate is null and cntDistinct&gt;1

&nbsp;

 select * from #trp

&nbsp;

drop table #trp
 

&nbsp;

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/49432f77-79f6-438b-8636-c1795fece82c/need-help-to-delete-the-records?forum=transactsql


Recursive with or without CTE

 

  
--Loop solution

 If Object_Id('tempdb..#test') Is not Null
Begin
       Drop table #test
End
create table #test (ID int, ID_1 int, ID_2 int, ID_3 int)

insert into #test values
(1,1,NULL,1),
(2,1,2,1),
(3,3,2,1),
(4,3,3,NULL),
(5,NULL,3,NULL),
(6,NULL,NULL,6),
(7,7,NULL,6),
(8,7,8,NULL),
(9,NULL,8,NULL)

GO
--create two table variables
declare @result table(ID int, parent_ID int, fullpath VARCHAR(900), lvl int)
declare @source table(ID int, parent_ID int )

--unpivot to normalize data
Insert into @Source (ID,parent_ID)

Select ID, Min(ids) parent_ID from (
 select ID, ID_1 ids from #test
 UNION 
 select ID,  ID_2 from #test
 UNION 
 select ID,  ID_3 from #test) t
Group by ID
 
--root 
insert into @result(ID, parent_ID, fullpath,lvl)
 Select ID, Min(parent_ID) parent_ID , CONVERT(VARCHAR(900), Cast(ID as varchar(10))+ '*')  AS FullPath, 0 as lvl
 from @Source
 Group by ID
 Having( ID=Min(parent_ID) )

 ---while loop
   WHILE (@@ROWCOUNT &gt; 0) 
   BEGIN

  insert into @result(ID, parent_ID, fullpath,lvl)

 Select s.ID, s.parent_ID , CONVERT(VARCHAR(1000),  isnull(r.FullPath,'') + Cast(r.parent_ID as varchar(10))+ '*' )  ,lvl+1 lvl    from
 @result r join @Source s on r.id =s.parent_ID  
 WHERE s.ID Not in (Select ID from @result)
 except
 select ID, parent_ID, fullpath,lvl from @result
 
 
END

&nbsp;

 
 
 Select ID
 , Stuff(FullPath,charindex('*',FullPath),Len(fullpath),'') minID from @result
 order by id
 
 

 --https://stackoverflow.com/questions/21990957/how-to-convert-recursive-cte-to-be-usable-in-sql-server-2000
 

&nbsp;

 

Recursive CTE

 

  

If Object_Id('tempdb..#t') Is not Null
Begin
       Drop table #t
End
create table #t (ID int, ID_1 int, ID_2 int, ID_3 int)

insert into #t values
(1,1,NULL,1),
(2,1,2,1),
(3,3,2,1),
(4,3,3,NULL),
(5,NULL,3,NULL),
(6,NULL,NULL,6),
(7,7,NULL,6),
(8,7,8,NULL),
(9,NULL,8,NULL)

GO

;with mycte as (
 select ID, ID_1 ids from #t
 UNION 
 select ID,  ID_2 from #t
 UNION 
 select ID,  ID_3 from #t
 
 
 )
 ,mycte1 as (
 Select ID, Min(ids) parent_ID from mycte
 Group by ID
 
 )

&nbsp;

 
 ,mycte2 as (

 Select ID,  parent_ID,  CONVERT(VARCHAR(900), Cast(ID as varchar(10))+ '*')  AS FullPath ,0 lvl
    from mycte1
 WHERE ID=parent_ID
 UNION ALL

 Select src.ID, src.parent_ID , CONVERT(VARCHAR(900),  FullPath+ Cast(t.parent_ID as varchar(10))+ '*' )  ,lvl+1 lvl    from
  mycte1 src
    join mycte2 t on t.id =src.parent_ID  
 WHERE t.ID Not in (Select ID from mycte1 WHERE src.ID=t.parent_ID)
 
 
 )
 
 Select ID, Stuff(FullPath,charindex('*',FullPath),Len(fullpath),'')  minID 
 from mycte2
 order by id
 
--OPTION (MAXRECURSION 0);
 

&nbsp;

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0010ff3e-9bf3-4638-9273-00b3e4848b28/sql-server-matching-on-columns-of-same-table?forum=transactsql