Transfer Table Schema From one to dbo
Posted: July 31, 2017 Filed under: Uncategorized Leave a comment
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
Original Script from:
https://www.sqlservercentral.com/Forums/654301/change-old-schema-name-into-new-schema-name
Split records, separate the overlapped dates
Posted: July 31, 2017 Filed under: Uncategorized Leave a commentDECLARE @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
Pivot With Case For Two Groups
Posted: July 25, 2017 Filed under: Uncategorized Leave a comment
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
*/
Date Range with Category
Posted: July 25, 2017 Filed under: Uncategorized Leave a comment
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
*/
Island Group, Regroup Sample Query
Posted: July 20, 2017 Filed under: Uncategorized 1 Comment
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
Delete Rows with Conditions
Posted: July 19, 2017 Filed under: Uncategorized Leave a comment
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);
;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 > getdate() and maxEnddate is null and cntDistinct>1
select * from #trp
drop table #trp
Recursive with or without CTE
Posted: July 13, 2017 Filed under: Uncategorized Leave a comment
--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 > 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
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
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
)
,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);
SQL 2012- Running Total with Increasing Percentage
Posted: July 7, 2017 Filed under: Uncategorized Leave a comment
Create Table RevenueTbl(CustomerID int ,Revenue Decimal(10,2),Years Int )
Insert into RevenueTbl
Select 93,100,2010 union all
Select 93,0,2011 union all
Select 93,0,2012 union all
Select 93,0,2013 union all
Select 93,0,2014 union all
Select 93,0,2015 union all
Select 93,0,2016
SELECT CustomerID
,POWER(10.000,SUM(Log(ISNULL(NULLIF(Revenue,0),1.1), 10)) OVER (PARTITION BY CustomerID ORDER BY Years ROWS unbounded preceding)) Revenue
,Years
FROM RevenueTbl
order by Years
SELECT CustomerID
, ROUND(BaseRevenue * POWER(1 + 10. / 100, Years - BaseYear), 2) AS Revenue
, Years
FROM (
SELECT CustomerID, Revenue, Years
, FIRST_VALUE(Revenue) OVER (PARTITION BY CustomerID ORDER BY Years) AS BaseRevenue
, FIRST_VALUE(Years) OVER (PARTITION BY CustomerID ORDER BY Years) AS BaseYear
FROM RevenueTbl
) AS main
drop Table RevenueTbl
Ranking Functions Sample Query
Posted: July 6, 2017 Filed under: Uncategorized Leave a commentCREATE TABLE mytable( ID INTEGER NOT NULL ,Status VARCHAR(20) NOT NULL ,JobID INTEGER NOT NULL ,Created_Date VARCHAR(14) NOT NULL ,Expected_Value VARCHAR(14) ,col varchar(50)); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (1,'PROCESSED WITH ERROR',3220,'4/25/2017 7:42',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (2,'COMPLETED',3220,'4/27/2017 7:35','4/25/2017 7:42','from ID = 1'); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (3,'COMPLETED',3220,'4/28/2017 2:15',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (4,'PROCESSED WITH ERROR',3220,'4/28/2017 3:46',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (5,'PROCESSED WITH ERROR',3220,'4/28/2017 3:54',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (6,'COMPLETED',3220,'5/16/2017 2:12','4/28/2017 3:46','from ID= 4'); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (7,'COMPLETED',3220,'5/16/2017 6:29',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (8,'COMPLETED',3221,'4/25/2017 7:42',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (9,'PROCESSED WITH ERROR',3221,'4/27/2017 7:35',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (10,'COMPLETED',3221,'4/28/2017 2:15','4/27/2017 7:35','from ID= 9'); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (11,'COMPLETED',3221,'4/28/2017 3:46',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (12,'PROCESSED WITH ERROR',3221,'4/28/2017 3:54',NULL,NULL); INSERT INTO mytable(ID,Status,JobID,Created_Date,Expected_Value,col) VALUES (13,'COMPLETED',3221,'5/16/2017 2:12','4/28/2017 3:54','from ID =12'); ;with mycte as (select ID,Status,JobID,Created_Date ,Expected_Value ,row_number() Over (Order by ID )- row_number() Over (Partition by Status,JOBID Order by ID ) grp ,row_number() Over (Partition by JOBID Order by ID ) seq from mytable) ,mycte2 as ( select ID,Status,JobID,Created_Date ,Expected_Value,seq,grp ,row_number() Over (Partition by Status,JOBID, grp Order by ID ) rn ,case when row_number() Over (Partition by Status,JOBID, grp Order by ID )=1 and Status='PROCESSED WITH ERROR' then Created_Date else null end dt from mycte ) select ID,Status,JobID,Created_Date, /*Expected_Value ,*/ case when rn=1 and Status='COMPLETED' then d.dt else null end as yourExpectedValue from mycte2 m Cross apply (select top 1 dt from mycte2 m2 WHERE m2.seq<=m.seq and m2.JobId=m.JobId and dt is not null Order by seq DESC) d(dt) order by ID drop table mytable
Jingyang Li 