-
Re: where =< 2017-01-01 and to include nulls?
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?
I get an error
FROM "Pat"."dbo"."Patients"
Where If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1);
Or can I just auto populate the nulls with a date of 2099-01-01?
-
Re: where =< 2017-01-01 and to include nulls?
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?
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?
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.
-
Re: where =< 2017-01-01 and to include nulls?
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?
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?
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?
Or can I just auto populate the nulls with a date of 2099-01-01?
Or can a us a measure to exclude all dates of 2016-12-31 and keep the nulls?
-
Re: where =< 2017-01-01 and to include nulls?
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?
[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?
yes it was in the select but this is what happens in load
// 01/01/0001 as Blankdate,
// DischargeDt,
If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1),
-
-
-
-
Re: where =< 2017-01-01 and to include nulls?
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
!-
Re: where =< 2017-01-01 and to include nulls?
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?
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....
-
