SQL Query Script

This should do the trick for you...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #Interview_Sample (
        ClientID VARCHAR(10),
        GrantNo VARCHAR(10),
        SubmissionDate DATE,
        InterviewTypeCode VARCHAR(20)
        )

INSERT  INTO #Interview_Sample
VALUES
        ('ABC123','SM04111','2010-01-19','BASELINE'),
        ('ABC123','SM04111','2010-03-12','Three_Months'),
        ('MNC123','XX08000','2010-02-19','BASELINE'),
        ('MNC123','XX08000','2010-06-12','Three_Months'),
        ('ORC456','ABC9000','2012-10-20','BASELINE')


SELECT 
        bl.ClientID,
        bl.GrantNo,
        1 - COALESCE(COUNT(CASE WHEN DATEDIFF(mm, bl.SubmissionDate, fu.SubmissionDate) <= 3 THEN 1 END) OVER (PARTITION BY bl.ClientID), 0) AS FollowUpsDue,
        COUNT(fu.ClientID) OVER (PARTITION BY bl.ClientID) AS FollowUpsReceived 
FROM (
                SELECT 
                        isa.ClientID,
                        isa.GrantNo,
                        isa.SubmissionDate
                FROM 
                        #Interview_Sample isa
                WHERE 
                        isa.InterviewTypeCode = 'BASELINE'
                ) bl
        LEFT JOIN (
                SELECT 
                        isa.ClientID,
                        isa.SubmissionDate
                FROM 
                        #Interview_Sample isa
                WHERE 
                        isa.InterviewTypeCode <> 'BASELINE'       
                ) fu
                ON bl.ClientID = fu.ClientID

HTH,

Jason

---

Jason Long