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


SQL 2012- Running Total with Increasing Percentage

  

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


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ebc004d0-070d-4612-9e72-5bd1c6d73334/sql-2012-running-total-with-increasing-percentage?forum=transactsql