Skip to content

Tag: teradata

Teradata merge multiple records into single record

I want to merge multiple record into single record Ex: Input pdt status start_dt end_dt a Inactive 2022/02/02 2022/02/04 a Inctive 2022/02/05 2022/02/10 a Active 2022/02/10 2022/02/12 b Active 2022/03/13 2022/03/17 Output pdt status start_dt end_dt a Active 2022/02/02 2022/02/12 b Active 2022/03/13 2022/03/17 Answer Normalize the date ranges first, then join back to pick up the most recent status.

SQL – Flag rows till 0 value of each group

I am calculating a running balance and want to flag all rows till 0 value to have ‘MATCHED’ flag else ‘NOT-MATCHED’ flag with respect to account ID. Here is what I have tried but didn’t got proper result: Answer We can use a sub-query to find the last acct_rank which is 0 and then use case to test each row.

Changing In to Exists in SQL – with DISTINCT

Having this one: Reading this one: Changing IN to EXISTS in SQL Tried to change it into “Exists”, but produced this and it did not work: The error is 3706: Syntax error: expected something between ‘=’ and ‘DISTINCT’ keyword. Answer You have already redundant code. Inside the IN subquery you are selecting a column from ORGHEADER, so the LEFT join

How to combine records start date and end date in SQL

I have a set of data example below: ID Role START_DATE END_DATE 1 A 2022-02-01 2022-02-03 1 A 2022-02-03 2022-02-10 1 A 2022-02-10 9000-12-31 2 B 2022-02-01 2022-02-03 2 A 2022-02-03 2022-02-04 2 B 2022-02-04 2022-02-11 I want to group them based on the ID, ROLE, START_DATE and END_DATE. So it looks like below: x ID Role START_DATE END_DATE 1

LEFT JOIN ON NULL Key Values Combined with GROUP BY

I’m using Teradata SQL and I wrote the following query (pay attention at the LEFT JOIN) Some entries for t1.key2 und t1.key3 (of the left sided table) are NULL. When that’s the case, the rows are not showing in the result, why? Is that Teradata specific, I would expect a LEFT JOIN to show rows with NULL values. If I

How to get data from sub-table?

i have “transaction” table and it has date_of_transaction, transaction_number, item_number columns. it has rows. also i have “item” sub-table. they linked with “item_number”. there are item_name and item_category columns for “item” table. i want to print rows with item_name and transaction_number. how can i make it? Answer

How to get a count of records by minute using a datetime column

I have a table with columns below: Customer Time_Start A 01/20/2020 01:25:00 A 01/22/2020 14:15:00 A 01/20/2020 03:23:00 A 01/21/2020 20:37:00 I am trying to get a table that outputs a table by minute (including zeros) for a given day. i.e. Customer Time_Start Count A 01/20/2020 00:01:00 5 A 01/20/2020 00:02:00 2 A 01/20/2020 00:03:00 0 A 01/20/2020 00:04:00 12