Hoping someone can help me out with this problem. I have the following sample dataset: MEM_ID CLM_ID ADM_DT DCHG_DT 1 111 01-01-2020 02-01-2020 1 112 03-01-2020 04-01-2020 1 113 04-01-2020 05-01-2020 1 114 06-01-2020 07-01-2020 2 211 01-01-2020 02-01-2020 2 212 05-01-2020 08-01-2020 3 311 02-01-2020 03-01-2020 3 312 03-01-2020 05-01-2020 3 313 05-01-2020 06-01-2020 3 314 07-01-2020 08-01-2020 I
Tag: netezza
How to surround a column with double quotes in Netezza
I want to surround the selected column entries with double quotes whenever there is a special character say ‘ | ‘ in the column value . In the above query in the true condition I want to surround email by double quotes whenever email contains | . For ex if email is a|bc@gmail.com , I want it to be “a|bc@gmail.com”.
Netezza: Generate FULL Statistics using SQL / force
Is there a way to force netezza to generate full statistics on a table using SQL. I know it is possible to do via command line but that require nzsql client to be installed on the machine that initiates this command. According to the details mentioned here – https://www.ibm.com/support/pages/understanding-generate-statistics-statement it may be possible to force NZ to assume that the
SYS_CALENDAR function in SQL Netezza
please how can I run a query that generate a calendar in SQL Netezza like the below query that works with Teradata Answer
Filter data based on group SQL Netezza
Please I have the below table, I need to filter the data based on the DATE column, In the Sus_ID level if the DATE column has NULL get this row or if the hasn’t NULL record will get the row for the newest DATE the desired result should be the below I tried the below code but it didn’t work
Parse out word between two characters SQL Netezza
I am having trouble finding the correct syntax to parse out a word between two characters in Netezza. PATIENT_NAME SMITH,JOHN L BROWN,JANE R JONES,MARY LYNN I need the first name which is always after the comma and before the first space. How would I do this in Netezza? Answer I think Netezza supports regexp_extract(). That would be: Or regexp_replace():
Count grouped dates in SQL
I currently have data looking like: where grp is grouped consecutive dates. I want to count each of this grp, so with above data, I would like to get result of 5 (2 consecutive date groups for id 1, 3 consecutive date groups for id 2). Anyone have idea how to tackle this? Answer Do you just want count(distinct)? I
Calculating the number of days between dates including magnitude in Netezza
I have two date fields in my Netezza table and I wish to find the number of days between them. The original table I pull these two fields from has them recorded as DATE. I wish to subtract SECOND_DATE from FIRST_DATE and wish to include the magnitude i.e. negative days in this example There are -158 days between SECOND_DATE and
How to count consecutive dates using Netezza
I need to count consecutive days in order to define my cohorts. I have a table that looks like: pat_id admin_date —————————- 1 3/10/2019 1 3/11/…
How to get Date only from the datetime value?
I have an netezza SQL database field that looks like the examples below: Is there a way to select the first set of date to be date only like below: Answer Try to use built-in function DATE() in the following: