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

 

 ;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
 

 

 

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

 

 

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


Ranking Functions Sample Query

  

 
  
 
CREATE 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


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/007eb38b-a934-4345-91ba-e12f7c46a250/find-cycle-from-first-failure-to-success-record?forum=transactsql


Dynamic datepart with DATEADD Function

  

 

CREATE TABLE [dbo].[ProcessMaster](
[ProcessID] [int] IDENTITY(1,1) NOT NULL,
[Frequency] [varchar](20) NULL,
[FrequencyQty] [int] NULL,
[LastStarted] [datetime] NULL)

insert into [ProcessMaster] (Frequency,FrequencyQty, LastStarted) 
values( 'dd', 1, '2017-06-15 08:16:20.587')
,('mi' ,5,'2010-06-22 11:12:00.537')
 
declare  @Frequency nvarchar(2), @FrequencyQty int, 
--more variables
@NextStart datetime , @ProcessID int=2
   
Select  @Frequency = Frequency, @FrequencyQty = FrequencyQty from ProcessMaster where ProcessID =  @ProcessID 

declare @sql nvarchar(2000) =N'Select  @NextStart=(Select dateadd('+@Frequency+',@FrequencyQty,LastStarted) from [dbo].[ProcessMaster] where ProcessID = @ProcessID )'
 

EXECUTE sp_executesql @sql, N'@NextStart datetime output,@Frequency char(2),@FrequencyQty int, @ProcessID int' ,@NextStart =@NextStart output , @Frequency =@Frequency , @FrequencyQty=@FrequencyQty , @ProcessID=@ProcessID
 
 
Select @NextStart 


drop table [ProcessMaster]


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/48a9fd46-3577-4b0b-9925-b9e67992b9a3/trying-to-dynamically-add-to-dates?forum=transactsql


Missing Object Error from Profiler to Insert Newly Create Table

When you insert rows immediately after the table creation, you may find there is an exception report for missing object if you dig into Profiler to view it. It is working fine from front end but you still see this error report. This behavior is caused by “deferred name resolution” and you will see this behavior with stored procedure as well.

Here is the code that generates this exception:

  

create table #temp (id int) 
insert into #temp (id) values (1)
 

 

You can avoid this error report by using different batch for your creation and insert.

  

create table #temp (id int) 

Go

insert into #temp (id) values (1)

Go

 

 

Or use global temp table

 

  

&nbsp;

create table ##temp (id int)

insert into ##temp (id) values (1)

&nbsp;

 

Or do nothing and keep coding.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/773b0265-1547-4d8a-9e43-b6a5ab8f33bd/getting-invalid-object-name-by-creating-a-temp-table-and-inserting-rows-right-away?forum=transactsql