Select dateadd(month, 6, Csat.InterviewDate)as DueDate,csat.Interviewdate,csat.interviewtypecode,csat.clientid

 from rpt.csatclientdcilegacy as CSAT Inner Join
 rpt.VwAssessment AS ast ON CSAT.AssessmentId = ast.Id INNER JOIN
                         rpt.VwSiteAward AS sa ON ast.SiteAwardId = sa.SiteAwardId INNER JOIN
                         rpt.vwAwardDetail As AD on sa.siteawardid=AD.siteawardid
where  CSAT.Interviewtypecode in('Baseline','Six_Months') and Csat.InterviewDate between ad.awardStartDate  and CURRENT_TIMESTAMP and ad.awardenddate>=getdate() and csat.clientID='2192984281'

Query Results

What I need is the below results , any help appreciate

FollowUp Due is calculated for BaseLine only Formula is result2 Interviewdate <=Result1 DueDate then 1  esle 0

• Edited by NaveenCR 6 hours 11 minutes ago

Sorry that explanation doesnt make much sense. As per this you will have FollowUpDue as 1 for all BASELINE records

Reson being you're calculation DueDate as dateadd(month, 6, Csat.InterviewDate) which will always be > csat.Interviewdate so the below condition in bold will always be true so it will be 1 always

FollowUp Due is calculated for BaseLine only Formula is result2 Interviewdate <=Result1 DueDate then 1  esle 0

Check this out for seeing it in action

Select case when csat.interviewtypecode = 'BASELINE' AND csat.Interviewdate < = dateadd(month, 6, Csat.InterviewDate) THEN 1 ELSE 0 END AS FollowUpDue,
dateadd(month, 6, Csat.InterviewDate)as DueDate,csat.Interviewdate,csat.interviewtypecode,csat.clientid
 from rpt.csatclientdcilegacy as CSAT Inner Join
 rpt.VwAssessment AS ast ON CSAT.AssessmentId = ast.Id INNER JOIN
                         rpt.VwSiteAward AS sa ON ast.SiteAwardId = sa.SiteAwardId INNER JOIN
                         rpt.vwAwardDetail As AD on sa.siteawardid=AD.siteawardid
where  CSAT.Interviewtypecode in('Baseline','Six_Months') and Csat.InterviewDate between ad.awardStartDate  and CURRENT_TIMESTAMP and ad.awardenddate>=getdate() and csat.clientID='2192984281'

Sorry, I did not explain properly. It was my mistake.

Each client will have 2 records in the table, one is baseline and one is SixMonths with 2 different interview dates.

Here is the Sample Data from the table

 If the interview date difference between baseline and six_month is more than 180 days then Followup is 1 else 0

Here is the results what I want is

• Edited by NaveenCR 5 hours 15 minutes ago

Are you looking for the below?

create Table Test_Sample (DueDate date, InterviewDate date, InterviewTypeCode varchar(20), ClientId int)

Insert into Test_Sample Values
('2010-07-19','2010-01-19','BASELINE', 219),
('2011-01-12','2010-07-12','SIX_MONTHS', 219)

Select Case when DateDiff(day,InterviewDate,LEAD(InterviewDate)Over(partition by ClientID Order by InterviewTypeCode)) >=180 then 1 else 0 End 
, * From Test_Sample

Drop table Test_Sample

• Marked as answer by NaveenCR 4 hours 50 minutes ago

this?

;With CTE
AS
(
Select dateadd(month, 6, Csat.InterviewDate)as DueDate,csat.Interviewdate,csat.interviewtypecode,csat.clientid
 from rpt.csatclientdcilegacy as CSAT Inner Join
 rpt.VwAssessment AS ast ON CSAT.AssessmentId = ast.Id INNER JOIN
                         rpt.VwSiteAward AS sa ON ast.SiteAwardId = sa.SiteAwardId INNER JOIN
                         rpt.vwAwardDetail As AD on sa.siteawardid=AD.siteawardid
where  CSAT.Interviewtypecode in('Baseline','Six_Months') and Csat.InterviewDate between ad.awardStartDate  
),
CTE1
AS
(
SELECT clientid,
CASE WHEN  DATEDIFF(dd,MAX(CASE WHEN interviewtypecode = 'BASELINE' THEN Interviewdate END),
MAX(CASE WHEN interviewtypecode = 'SIX_MONTHS' THEN Interviewdate END)) > 180 THEN 1 ELSE 0 END AS FollowUpDue
FROM CTE
GROUP BY clientid
)
SELECT FollowuUpDue,
DueDate,
Interviewdate,
interviewtypecode,
c.clientid
FROM CTE c
INNER JOIN CTE1 c1
ON c1.clientid = c.clientid

Lathees/Visakh,

Thank you so much for your help.

I am running those queries on 2.5 Million records, it is taking almost 14 minutes. Is there anyway to run faster.

Lathees/Visakh,

Thank you so much for your help.

I am running those queries on 2.5 Million records, it is taking almost 14 minutes. Is there anyway to run faster.

Do you have an index on ClientID?

As long as you are doing it for complete table with huge number of records, it is expected only. But you can check out the index on Client ID to improve the performance.

• Marked as answer by NaveenCR 4 hours 27 minutes ago

Lathees/Visakh,

Thank you so much for your help.

I am running those queries on 2.5 Million records, it is taking almost 14 minutes. Is there anyway to run faster.

Check the execution plan and see the possibility of adding missing indexes.

If it still doesnt help see if you can make this into an indexed view or extend the table itself to add this a new bit field so that you dont want to calculate it on the fly everytime. Once you've a field its just one time update and thereafter you can use it in queries

Added missing Index and ran query , this time it took 5 minutes.

Once again thank you so much.

Hi, 

Maybe you can try below select statement. You're output will be a 1 in the "FolowUpDue" column when the difference between two lines will be more then six months. 

CREATE TABLE #TEMP 
(
INTERVIEWDATE date, 
INTERVIEW_TYPE_CD varchar(20), 
CLIENTID int
);
--DROP TABLE #TEMP

INSERT INTO #TEMP VALUES
('2010-01-19','BASELINE', 21929842),
('2010-07-12','SIX_MONTHS', 21929842),
('2010-01-19','BASELINE', 22029842),
('2010-08-12','>SIX_MONTHS', 22029842);
--TRUNCATE TABLE #TEMP

SELECT 
CASE WHEN DATEDIFF(DAY, FIRST_VALUE(INTERVIEWDATE) OVER(PARTITION BY CLIENTID ORDER BY INTERVIEWDATE), LAST_VALUE(INTERVIEWDATE) OVER(PARTITION BY CLIENTID ORDER BY INTERVIEWDATE)) > 180 THEN 1 ELSE 0 END AS FollowUpDue, INTERVIEWDATE, 
INTERVIEW_TYPE_CD, 
CLIENTID
FROM #TEMP

Regards,

Reshma

 

Guys, Was trying to understand the query. Could you please let me know why we are using 180. may be silly question but wanted to know to understand the question fully