SQL Query Script

Just need to alter the COUNT condition in "FollowUpsReceived"...

IF OBJECT_ID('tempdb..#Interview_Sample') IS NOT NULL DROP TABLE #Interview_Sample
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'),
        ('ORC456','ABC9000','2013-10-20','Three_Months')


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(CASE WHEN DATEDIFF(mm, bl.SubmissionDate, fu.SubmissionDate) <= 6 THEN 1 END) 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

I don't know the specific alterations you made to my last piece of code to make it work so I couldn't apply it here. Just do the same thing again and you should be good to go. :)

---

Jason Long