CODEWITHSUNDEEP
sql-server
Antoine Pelletier
Antoine Pelletier

Reputation: 3316

SQL confusion while trying to retrieve 2 dates

I wanted to query a table and have dates on 2 separates rows, the first row would be the date closest to today, and the last row would contain max date.

I came up with this :

SELECT           
    ID_CAND, DATE_JOUR
FROM         
    Paie.dbo.PAI_CAND_JOUR_NON_DISPO nd
WHERE        
    (ID_CAND = 16246)  
    AND (DATE_JOUR = (SELECT MIN(DATE_JOUR) AS Expr1
                      FROM Paie.dbo.PAI_CAND_JOUR_NON_DISPO AS ds
                      WHERE (ds.ID_CAND = nd.ID_CAND) 
                        AND (DATE_JOUR > GETDATE())) 
         OR
         DATE_JOUR = (SELECT MAX(DATE_JOUR) AS Expr1
                      FROM Paie.dbo.PAI_CAND_JOUR_NON_DISPO AS ds
                      WHERE (nd.ID_CAND = ds.ID_CAND) 
                        AND (DATE_JOUR > GETDATE())))

And it returns only one row... but when i run the inner selects separately :

SELECT       
    MIN(DATE_JOUR) AS Expr1
FROM            
    Paie.dbo.PAI_CAND_JOUR_NON_DISPO AS ds
WHERE        
    (ID_CAND = 16246) AND (DATE_JOUR > GETDATE())

SELECT        
    MAX(DATE_JOUR) AS Expr1
FROM
    Paie.dbo.PAI_CAND_JOUR_NON_DISPO AS ds
WHERE        
    (ID_CAND = 16246) AND (DATE_JOUR > GETDATE())

It does return 2 different values... what am I doing wrong ?

Upvotes: 0

Views: 47

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

Perhaps this can help

Select MinDate = min(case when DATE_JOUR >= GETDATE() then DATE_JOUR else null end)
      ,MaxDate = max(DATE_JOUR)
 From  Paie.dbo.PAI_CAND_JOUR_NON_DISPO          
 Where ID_CAND = 16246

For Two separate rows

Select Expr1 = min(case when DATE_JOUR >= GETDATE() then DATE_JOUR else null end)
 From  Paie.dbo.PAI_CAND_JOUR_NON_DISPO          
 Where ID_CAND = 16246
Union  All
Select Expr1 = max(DATE_JOUR)
 From  Paie.dbo.PAI_CAND_JOUR_NON_DISPO          
 Where ID_CAND = 16246

Upvotes: 2

Related Questions