• 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