Hi
One of the possible solutions could be
--create some test data
USE tempdb
GO
CREATE TABLE test1 (sessionId int, MRN int, ChartStartTime date ,DeliveryTime date)
GO
INSERT INTO dbo.test1(sessionId,MRN,ChartStartTime,DeliveryTime)
SELECT 36957,123456,'7/20/2015',NULL
UNION ALL
SELECT 37695,123456,'8/29/2015',NULL
UNION ALL
SELECT 37824,123456,'9/5/2015','9/5/2015'
UNION ALL
SELECT 37916,123456,'9/10/2015',NULL
UNION ALL
SELECT 37917,123455,'7/20/2015',NULL
UNION ALL
SELECT 37918,123455,'8/29/2015',NULL
UNION ALL
SELECT 37919,123455,'9/5/2015',NULL
UNION ALL
SELECT 37920,123455,'9/10/2015',NULL
--
;WITH GetDeliveryTime AS
(
SELECT MAX(t1.DeliveryTime) DelTime
,t1.MRN
FROM test1 t1
GROUP BY t1.MRN
)
SELECT t.sessionId
,t.MRN
,t.ChartStartTime
,t.DeliveryTime
,SUM(CASE
WHEN t.ChartStartTime >= dt.DelTime THEN 1
ELSE 0
END) OVER (PARTITION by t.MRN ORDER BY (SELECT NULL)) AS [No_of_visits]
--,COUNT(*) OVER (PARTITION BY t.MRN ORDER BY (SELECT NULL)) AS [Total_No_of_visits]
FROM dbo.test1 t
LEFT OUTER JOIN GetDeliveryTime dt
ON dt.MRN = t.MRN
D.Mincic
😀
MCTS Sql Server 2008, Database Development