troubleshooting Question

help with datediff query

Avatar of gianitoo
gianitoo asked on
Microsoft SQL Server 2005
8 Comments1 Solution171 ViewsLast Modified:
in my db  i have a legalcontract.triggerdate  and  legalcontract.triggerdateend  
most of the contracts do not have  legalcontract.triggerdateend  

how can i make this so if  legalcontract.triggerdateend is null then it will use query below but if there is a date then the where statement will use  
legalcontract.triggerdateend  instead of  legalcontract.triggerdate
do i make sense?

SELECT     ExpRev.Revenue AS revenue, ExpRev.expense, Legalclients.Clientname, legalcontract.enddate,legalcontract.triggerdate,legalcontract.summary, legalcontract.Contractname, legalcontract.Summary,
FROM         Legalclients LEFT OUTER JOIN
                      legalcontract ON Legalclients.ID = legalcontract.ClientID LEFT OUTER JOIN
                          (SELECT     ContractID, CASE PaymentType WHEN 'income' THEN Amount ELSE 0 END AS Revenue,
                                                   CASE PaymentType WHEN 'expense' THEN Amount ELSE 0 END AS expense
                            FROM          LegalPayments) AS ExpRev ON legalcontract.ID = ExpRev.ContractID LEFT OUTER JOIN
                      Legalcontracttype ON legalcontract.ContractType = Legalcontracttype.ID
WHERE (legalcontract.triggerdate >= CONVERT (varchar(10), GETDATE(), 121)) AND (legalcontract.triggerdate <= DATEADD(day, 90, CONVERT (varchar(10), GETDATE(), 121)) and      (legalcontract.expired = 1))
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros