-
Re: where =< 2017-01-01 and to include nulls?
Sunny Talwar May 4, 2017 5:01 PM (in response to Robert Winkel)May be like this
LOAD *
FROM ...
Where If(Len(Trim(DateField)) = 0, MakeDate(2017, 1, 1), DateField) <= MakeDate(2017, 1, 1);
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 4, 2017 7:19 PM (in response to Sunny Talwar )-
Re: where =< 2017-01-01 and to include nulls?
Sunny Talwar May 4, 2017 7:24 PM (in response to Robert Winkel)Try this
SQL SELECT *,
NVL(DischargeDt, TO_DATE('01JAN2099', 'DDMONYYYY')) as DateField
FROM "Pat"."dbo"."Patients"
Where NVL(DischargeDt, TO_DATE('01JAN2017', 'DDMONYYYY')) <= TO_DATE('01JAN2017', 'DDMONYYYY');
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 4, 2017 7:38 PM (in response to Sunny Talwar )-
Re: where =< 2017-01-01 and to include nulls?
Sunny Talwar May 4, 2017 7:45 PM (in response to Robert Winkel)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');
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 4, 2017 8:16 PM (in response to Sunny Talwar )-
Re: where =< 2017-01-01 and to include nulls?
Stefan Wühl May 5, 2017 4:28 AM (in response to Robert Winkel)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.
-
Re: where =< 2017-01-01 and to include nulls?
Sunny Talwar May 5, 2017 6:11 AM (in response to Robert Winkel)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
-
-
-
-
-
-
-
Re: where =< 2017-01-01 and to include nulls?
Stefan Wühl May 4, 2017 5:30 PM (in response to Robert Winkel)There are two things to consider
a) you want to combine different conditions in a WHERE clause
You can use logical operators to achieve this:
The OR operator would be useful in your case
b) you want to check for NULL
There are two common approaches to check for NULL, IsNull(FIELD) checks for NULL and returns true if FIELD is NULL, Len(Trim(FIELD)) returns zero for NULL or if the FIELD value is empty or blank.
In total
LOAD
DateField,
...
FROM ....
WHERE DateField <= '2017-01-01' OR Len(Trim(DateField));
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 4, 2017 7:00 PM (in response to Stefan Wühl )-
Re: where =< 2017-01-01 and to include nulls?
Sunny Talwar May 4, 2017 7:17 PM (in response to Robert Winkel)Seems like you performing this in the SQL SELECT Statement instead of load statement... would you be able to share the SELECT Statement you have?
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 4, 2017 7:28 PM (in response to Sunny Talwar )[Patients]:
LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';
// LIB CONNECT TO 'pharmoresql (pharmore_dewing)';LOAD
[FacID] as PFacID,
[PatID] as PPatID,
[PatLName],
[PatFName],
[PatStatus],
[AdmDate],
// 01/01/0001 as Blankdate,
DischargeDt,
// isNull(DischargeDt, 00/00/0000),
[DefaultExempt340B];
SQL SELECT "FacID",
"PatID",
"PatLName",
"PatFName",
"PatStatus",
"AdmDate",
"DischargeDt",
"DefaultExempt340B"
FROM "Pat"."dbo"."Patients"Where If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1);
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 4, 2017 7:35 PM (in response to Sunny Talwar )
-
-
-
-
Re: where =< 2017-01-01 and to include nulls?
Robert Winkel May 5, 2017 10:18 AM (in response to Robert Winkel)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
-
test data.xlsx 2.1 MB
-
Re: where =< 2017-01-01 and to include nulls?
Stefan Wühl May 5, 2017 10:59 AM (in response to Robert Winkel)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
-
Re: where =< 2017-01-01 and to include nulls?
Sunny Talwar May 7, 2017 6:19 PM (in response to Robert Winkel)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....
-