How to Fill Column Null Values from Previous Non-null Value or Next if No Previous Exists

create table test2 (name varchar(50), person_id int,value int)
insert into test2 values ('alice',    12 , null)  ,    
('christa', 13,  1),
('hally',14,   null),
('jack',  18,   2),
('raj', 20,   null),
('jane',22,    3),
('jodie',  25, null)

select * from test2


;with mycte as
(
SELECT name,person_id,value, row_number()over(order by person_id ) rn
FROM test2 A)

SELECT m.name,m.person_id, ISNULL(d.value,e.value) AS value
FROM   mycte m
       OUTER APPLY (SELECT TOP 1 *  FROM   mycte
                    WHERE  rn <= m.rn AND value IS NOT NULL
                    ORDER  BY rn DESC ) d
       OUTER APPLY (SELECT TOP 1 *  FROM   mycte
                    WHERE  Value IS NOT NULL
                    ORDER  BY rn) e
ORDER  BY m.person_id 


drop table test2

--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d7af7f8f-8acf-4a03-b3d1-297218aa5d60

Composable DML (Merge… Insert) with Foreign Key Issue and Solution

You can use Merge with INSERT (Composable DML) to log historic data. But you cannot insert into a table with a FOREIGN KEY defined. You will run into error like this:
” The target table ‘myTable’ of the INSERT statement cannot be on either side of a (primary key, foreign key)
relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.”

A workaround is to disable (or drop) these constraints before Insert and enable these constrains afterwards.

--mylog table has two foreign keys:[FK_myLog_a], [FK_myLog_s] 
--

--disable constraints
ALTER TABLE [dbo].[myLog] NOCHECK CONSTRAINT ALL



INSERT INTO [dbo].[myLog]
SELECT Action, ISNULL(Newaccountid ,Oldaccountid) as accountid, 
ISNULL(Newserviceid,Oldserviceid) as serviceid, changedDate

FROM
 
( 

MERGE asTable AS target
USING (
SELECT b.accountid,t1.Serviceid
FROM   mytable) AS source (Newid, Newsid)
ON (target.id = source.Newid )

WHEN NOT MATCHED BY TARGET  
--Insert  new
    THEN INSERT (accountid, Serviceid) VALUES (Newaccountid, NewServiceid)
        
WHEN NOT MATCHED BY SOURCE AND (target.id <>4)
--Delete old  
THEN  DELETE

OUTPUT $action, Inserted.id, Inserted.sid, Deleted.id,Deleted.sid, GETDATE()

)
 AS Changes (Action, Newid, Newsid,Oldid, Oldsid, changedDate) 
 WHERE Action = 'DELETE' Or Action = 'INSERT';






--Enable constraints after the operation
ALTER TABLE [dbo].[accountserviceLog] CHECK CONSTRAINT ALL

ALTER TABLE [dbo].[myLog] WITH CHECK CHECK CONSTRAINT [FK_myLog_a] 
ALTER TABLE [dbo].[myLog] WITH CHECK CHECK CONSTRAINT [FK_myLog_s] 


--http://msdn.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
--http://www.brentozar.com/blitz/foreign-key-trusted/

An Aggregation Query with T-SQL (Two Solutions: SQL Server 2012 only and SQL Server 2008/05)

if object_id('dbo.t','u') is not null
DROP TABLE [dbo].[t]
GO

CREATE TABLE [dbo].[t](
	[TransactType] [varchar](20) NULL,
	[TansactionCnt] [int] NULL,
	[TansactionSequence] [int] NULL
) ON [PRIMARY]

GO




INSERT [dbo].[t]   VALUES (N'Payment', 1, 600008)
, (N'New_Order', 1, 600010)
,(N'Payment', 1, 600012)
,(N'Payment', 1, 600014)
,(N'OrderStatus', 1, 600016)
,(N'Delivery', 1, 600018)
,(N'New_Order', 1, 600020)
,(N'New_Order', 1, 600022)
,(N'New_Order', 1, 600024)
, (N'Payment', 1, 600026)

;WITH mycte AS (
	SELECT TransactType,	TansactionCnt,	TansactionSequence,
 ROW_NUMBER() OVER(ORDER BY TansactionSequence) AS rnAll,
 ROW_NUMBER() OVER(PARTITION BY TransactType ORDER BY TansactionSequence) AS rnTransactType
FROM t 

), mycte1 AS (
   SELECT   TransactType, count(TransactType) Over(partition by TransactType, rnAll - rnTransactType) as TansactionCnt, TansactionSequence
FROM  mycte )


SELECT   TransactType, TansactionCnt, 
(STUFF((SELECT ',' + CAST(TansactionSequence AS varchar(10)) FROM  mycte1 WHERE m.TransactType=TransactType and m.TansactionCnt=TansactionCnt
 FOR XML PATH('')), 1, 1, '')) AS TansactionSequences
FROM  mycte1 AS m
GROUP BY TransactType, TansactionCnt
Order by TansactionSequences
--PS:
--I have learned this technique from Erland's sample at:
--http://social.msdn.microsoft.com/Forums/en/transactsql/thread/339af246-12e9-4c82-8d26-c3f23f126017

–SQL Server 2012 Solution

;with mycte0 as
(
select TransactType, TansactionSequence,
ROW_NUMBER() OVER(ORDER BY TansactionSequence) AS rn,
Case  When TransactType= lag(TransactType,1) Over(Order by TansactionSequence) Then 0 else 1 END resetflag 
FROM t --yourtablename
)

,mycte
as
(select TransactType, TansactionSequence, sum(resetflag) Over(order by rn) grn from mycte0)

,mycte1 as
(
select TransactType,  count(TansactionSequence) Over(partition by grn) TansactionCnt, TansactionSequence from mycte)


SELECT TransactType, TansactionCnt, 
(STUFF((SELECT  CONCAT(', ', CAST(TansactionSequence AS varchar(10))) FROM  mycte1 WHERE m.TransactType=TransactType and m.TansactionCnt=TansactionCnt
 FOR XML PATH('')), 1, 1, '')) AS TansactionSequences
FROM  mycte1 AS m
GROUP BY TransactType, TansactionCnt
Order by TansactionSequences

Finding Start and End Time From a Series Datetime Column (T-SQL)

I came across this question at MSDN T-SQL forum:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/339af246-12e9-4c82-8d26-c3f23f126017
Here is my solution:

IF OBJECT_ID('tempdb..#P') IS NOT NULL
	DROP TABLE tempdb..#P
GO	
CREATE TABLE #P
(
	PID	INT IDENTITY(1,1)
	, DT	DATETIME
	, BT	BIT
)
GO
INSERT #P
	SELECT '2012-01-02 23:18:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:23:07.000', 1 UNION ALL	
	SELECT '2012-01-02 23:28:29.000', 0 UNION ALL		
	SELECT '2012-01-02 23:29:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:30:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:31:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:32:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:33:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:34:07.000', 0 UNION ALL
	SELECT '2012-01-02 23:35:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:36:07.000', 0 UNION ALL				
	SELECT '2012-01-02 23:37:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:38:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:39:07.000', 1 UNION ALL		
	SELECT '2012-01-02 23:40:07.000', 0
GO
--SELECT * FROM #P
--GO
/*
--Expected results
StartTime					EndTime
2012-01-02 23:18:07.000		2012-01-02 23:28:29.000
2012-01-02 23:29:07.000		2012-01-02 23:34:07.000
2012-01-02 23:35:07.000		2012-01-02 23:36:07.000
2012-01-02 23:37:07.000		2012-01-02 23:40:07.000

*/	


;with mycte as
(
SELECT T.PID, BT,DT, row_number()over(order by DT desc) rn
FROM #P T)

,mycte2
AS(
SELECT m.pid,m.DT,m.BT,  d.DT AS DT2, row_number()over(PARTITION BY d.DT Order by m.DT ASC) rn2 FROM mycte m
OUTER APPLY (SELECT TOP 1 DT FROM mycte
WHERE rn<= m.rn AND BT =0
ORDER BY rn desc) d
)


SELECT DT AS StartTime,DT2 AS EndTime FROM mycte2
WHERE rn2=1
ORDER BY DT



-----***********First Try******************
--;with mycte as
--(
--SELECT T.PID, BT,DT,CASE WHEN T.BT=1 THEN NULL ELSE T.DT END AS DT2, row_number()over(order by DT desc) rn
--FROM #P T)

--,mycte2
--AS(
--SELECT m.pid,m.DT,m.BT,d.DT2, row_number()over(PARTITION BY d.dt2 order by m.DT ) rn2 FROM mycte m
--OUTER APPLY (SELECT TOP 1 * FROM mycte
--WHERE rn<= m.rn AND BT =0
--ORDER BY rn desc) d
--)

----SELECT * FROM mycte2

--SELECT   DT AS StartTime,DT2 AS EndTime FROM mycte2
--WHERE rn2=1
--ORDER BY DT