Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need a where clause that in the load I filter out a date but still retain the null data.
I guess sql server doesn't have NVL... try with COALESCE
SQL SELECT *,
COALESCE(DischargeDt, CONVERT(DATETIME, '2099-01-01')) as DateField
FROM "Pat"."dbo"."Patients"
Where COALESCE(DischargeDt, CONVERT(DATETIME, '2017-01-01')) <= CONVERT(DATETIME, '2017-01-01');
COALESCE(DischargeDt, CONVERT(DATETIME, '2099-01-01')) as DateField
FROM "Pat"."dbo"."Patients"
Where COALESCE(DischargeDt, CONVERT(DATETIME, '2017-01-01')) <= CONVERT(DATETIME, '2017-01-01');
I still pick up all records but it shows a date of 1/1/2099 where they were null.
Robert, could you elaborate on the DischargeDt values that still pass the WHERE clause and that you want to exclude?
The WHERE clause will let all values pass where the WHERE condition returns TRUE.
Your current condition returns TRUE for all values before and including Jan 1st, 2017.
In a previous post, you said
"Or can a us a measure to exclude all dates of 2016-12-31 and keep the nulls?"
which is contradicting the current condition. Maybe you just need to adjust the date limit and / or comparison operator in your WHERE clause.
In your initial post you mentioned
"filter out a date but still retain the null data"
So, it seemed you do want to keep the null value in the dates. Then you mentioned this
"can I just auto populate the nulls with a date of 2099-01-01?"
I tried to provide you with a code which changes the null value to the date you asked for... now I am confused as to what exactly you are looking to get? Is this not what you wanted
I only want to pull in DischargeDt of 01/01/2017 and forward.
But what I am really trying to accomplish is a way to count number of patients we have that are active by month.
starting for the month of January 2017.
A trending report by month of patients that were active in each month.
Data in report
!
So you want to filter your dates more like
Where NVL(DischargeDt, TO_DATE('01JAN2017', 'DDMONYYYY')) >= TO_DATE('01JAN2017', 'DDMONYYYY');
For your other request (which is shifting / extending the topic of your thread quite a bit),have a look at
You want Jan 2017 and forward and you don't want to bring in nulls? May be this
DischargeDt as DateField
FROM "Pat"."dbo"."Patients"
Where COALESCE(DischargeDt, CONVERT(DATETIME, '2000-01-01')) >= CONVERT(DATETIME, '2017-01-01');
This will now bring everything 01/01/2017 and forward (just as you asked)
I only want to pull in DischargeDt of 01/01/2017 and forward.
and won't bring the nulls....