SQL adding IF statement

Hi-
I'm trying to write a sql statement  and I'm having trouble adding an if statement -
I think it needs to be a case statement but I'm not sure how to do this....


This is the part - I need help with:
if isnull(dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1) then MAX(dbo.cstd_company_status.creation_date) else  ((MAX(dbo.cstd_company_status.creation_date)
                      <= dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1)

SELECT     TOP (100) PERCENT dbo.oncd_company.company_id, dbo.oncd_company.company_name_1, dbo.onca_company_status.translation AS Status3, 
                      MAX(dbo.cstd_company_status.creation_date) AS StatusDate3, dbo.onca_company_status.status_sequence, 
                      dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1
FROM         dbo.CompanyStatus_Date10_ReEngage RIGHT OUTER JOIN
                      dbo.oncd_company ON dbo.CompanyStatus_Date10_ReEngage.company_id = dbo.oncd_company.company_id LEFT OUTER JOIN
                      dbo.onca_company_status RIGHT OUTER JOIN
                      dbo.cstd_company_status ON dbo.onca_company_status.company_status_code = dbo.cstd_company_status.company_status_code ON 
                      dbo.oncd_company.company_id = dbo.cstd_company_status.company_id
GROUP BY dbo.oncd_company.company_id, dbo.oncd_company.company_name_1, dbo.onca_company_status.translation, 
                      dbo.onca_company_status.status_sequence, dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1
HAVING      (dbo.onca_company_status.status_sequence = N'03') AND if isnull(dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1) then MAX(dbo.cstd_company_status.creation_date) else  ((MAX(dbo.cstd_company_status.creation_date)
                      <= dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1)
ORDER BY dbo.oncd_company.company_name_1

Open in new window

LVL 2
stacydrAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
Use case
SELECT     TOP (100) PERCENT dbo.oncd_company.company_id, dbo.oncd_company.company_name_1, dbo.onca_company_status.translation AS Status3, 
                      MAX(dbo.cstd_company_status.creation_date) AS StatusDate3, dbo.onca_company_status.status_sequence, 
                      dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1
FROM         dbo.CompanyStatus_Date10_ReEngage RIGHT OUTER JOIN
                      dbo.oncd_company ON dbo.CompanyStatus_Date10_ReEngage.company_id = dbo.oncd_company.company_id LEFT OUTER JOIN
                      dbo.onca_company_status RIGHT OUTER JOIN
                      dbo.cstd_company_status ON dbo.onca_company_status.company_status_code = dbo.cstd_company_status.company_status_code ON 
                      dbo.oncd_company.company_id = dbo.cstd_company_status.company_id
GROUP BY dbo.oncd_company.company_id, dbo.oncd_company.company_name_1, dbo.onca_company_status.translation, 
                      dbo.onca_company_status.status_sequence, dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1
HAVING (dbo.onca_company_status.status_sequence = N'03') AND 
       case 
         when dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1 IS null then 
            MAX(dbo.cstd_company_status.creation_date) 
         else  
            MAX(dbo.cstd_company_status.creation_date)
        end  <= dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1
ORDER BY dbo.oncd_company.company_name_1

Open in new window

0
 
logideepakCommented:
case when dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1 is null then MAX(dbo.cstd_company_status.creation_date) else (MAX(dbo.cstd_company_status.creation_date)
 end as testcolumn

The complete query could be as follows:

SELECT     TOP (100) PERCENT dbo.oncd_company.company_id, dbo.oncd_company.company_name_1, dbo.onca_company_status.translation AS Status3,
                      MAX(dbo.cstd_company_status.creation_date) AS StatusDate3, dbo.onca_company_status.status_sequence,
                      dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1,
case when dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1 is null then MAX(dbo.cstd_company_status.creation_date) else (MAX(dbo.cstd_company_status.creation_date)
 end as testcolumn
FROM         dbo.CompanyStatus_Date10_ReEngage RIGHT OUTER JOIN
                      dbo.oncd_company ON dbo.CompanyStatus_Date10_ReEngage.company_id = dbo.oncd_company.company_id LEFT OUTER JOIN
                      dbo.onca_company_status RIGHT OUTER JOIN
                      dbo.cstd_company_status ON dbo.onca_company_status.company_status_code = dbo.cstd_company_status.company_status_code ON
                      dbo.oncd_company.company_id = dbo.cstd_company_status.company_id
GROUP BY dbo.oncd_company.company_id, dbo.oncd_company.company_name_1, dbo.onca_company_status.translation,
                      dbo.onca_company_status.status_sequence, dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1
HAVING      (dbo.onca_company_status.status_sequence = N'03') AND if isnull(dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1) then MAX(dbo.cstd_company_status.creation_date) else  ((MAX(dbo.cstd_company_status.creation_date)
                      <= dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1)
ORDER BY dbo.oncd_company.company_name_1
0
 
Nathan RileyFounderCommented:
Need to fill in the ?'s, but here you go.

 
case when isnull(dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1) = ?then MAX(dbo.cstd_company_status.creation_date) 
else  when ((MAX(dbo.cstd_company_status.creation_date)
                      <= dbo.CompanyStatus_Date10_ReEngage.ReEngageDate1) then ?
end

Open in new window

0
 
stacydrAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.