If a record can't be found I'd like the query to return a 0 ...at the moment my query returns are blank when a record isn't located. Thought using CASE would do the trick but no dice. Need some help with this ...thank you.
SELECT CASE when DATEDIFF(DAY,p.StartOfCare, cna.VisitDate) = '' THEN 0 ELSE DATEDIFF(DAY,StartOfCare,cna.VisitDate) end AS Result
FROM Comp_Nurse_Assess cna INNER JOIN Patient p ON p.ID = cna.Patient_ID_Num
LEFT OUTER JOIN QANursingForms qa
ON cna.Patient_ID_Num = qa.PatientID
WHERE qa.Form_Title = 'Comp. Nursing Assessment' AND qa.QAComplete <> 1 AND p.Clientid = 5001 AND p.MRNum = '3336'
Could you show us some sample data and expected result? I'm not seeing how it could return blanks. What are the data types and do they allow nulls?
data types are datetime ...yes, I allow NULL. For this query there will always be a record in Patient p but not always in QANursingForms qa. In the case where a qa record doesn't exist the above query returns nothing. If a qa record does exist then, naturally, I get the date difference.
This doesn't compute. DATEDIFF returns an integer value but you are comparing it to an empty string. I'm not sure what value you are looking to compare against though or if you need some logic that would test for NULL. Can you elaborate?
I need to see sample data. You aren't using the qa table in the DATEDIFF.
My bad... you're correct. I meant Comp_Nurse_Assess cna.
Here is a quick video demonstrating what I'm after:
Sample data please. I can't watch the video and really wouldn't want to. Sample data plus expected result will illustrate the issue for us. Just sample data, not real data.
Here's how to normally post it, though I'd settle for much less at this point: http://www.sqlservercentral.com/articles/Best+Practices/61537/
That code can't return a blank.
Either it's not returning anything or it's returning NULL.
The problem is that you are not actually using a LEFT OUTER JOIN. By including the non-preserved table in the where clause you are essentially using an inner join.
You can try moving the filters on the QA table from the where clause to the join - no guarantee that will provide the results you are actually looking for though. It really depends on what you are expecting from the QA table since you don't utilize anything from that table except in the where clause.