CODEWITHSUNDEEP
sqlsql-server
Mr John
Mr John

Reputation: 241

datediff with conditions in select statment

Sql Server 2008 Datediff with conditions in the select statement.

I have the below query and need help understanding why I'm getting these results and best solution for my problem. 

 SELECT DISTINCT l.program, l.id
     ,(DATEDIFF(DAY, B.FIRST_DT , B.FIRST_second_DT))days_between1
     ,(DATEDIFF(DAY, C.FIRST_DT , C.FIRST_second_DT))days_between2      
FROM  l
LEFT JOIN l B ON L.id = B.id AND L.SERVICE_DATE = B.SERVICE_DATE
              AND B.FIRST_DT IS NOT NULL AND B.FIRST_DT IS NOT NULL
              AND l.IS ='0' AND L.FIRST_V_DT IS NOT NULL
LEFT JOIN l C ON L.id = C.id AND L.SERVICE_DATE = C.SERVICE_DATE
              AND C.FIRST_DT IS NOT NULL
              AND C.FIRST_A_DT IS NOT NULL AND L.IS ='1'
              AND L.FIRST_DT IS NOT NULL
WHERE 1=1

I'm getting two lines

Program    id    daysbetween1      daysbetween2
blue        1        null              3
blue        1         4               null

How do I get one line, like below and why am I getting two lines? 

Program    id    daysbetween1      daysbetween2
blue        1        4                3

Upvotes: 1

Views: 240

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

You can try this way:

SELECT l.program, l.id
     ,MAX((DATEDIFF(DAY, B.FIRST_DT , B.FIRST_second_DT))) days_between1
     ,MAX((DATEDIFF(DAY, C.FIRST_DT , C.FIRST_second_DT))) days_between2      
FROM  l
LEFT JOIN l B ON L.id = B.id AND L.SERVICE_DATE = B.SERVICE_DATE
              AND B.FIRST_DT IS NOT NULL AND B.FIRST_DT IS NOT NULL
              AND l.IS ='0' AND L.FIRST_V_DT IS NOT NULL
LEFT JOIN l C ON L.id = C.id AND L.SERVICE_DATE = C.SERVICE_DATE
              AND C.FIRST_DT IS NOT NULL
              AND C.FIRST_A_DT IS NOT NULL AND L.IS ='1'
              AND L.FIRST_DT IS NOT NULL
GROUP BY l.program, l.id

That where condition is unnecessary unless you are getting this SQL from some sort of dynamic concatenation of the filters.

Upvotes: 2

Related Questions