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.
Tag: teradata
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
Merging Respective Start and End Dates, Setting Flag Depending on Start/End Date – SQL
Problem: I have a table of transactions (see below) with either (open/start) or (close/end) transaction in the format of date. The task is to merge these transactions with their corresponding dates, however, there can be also cases when the transaction is opened/started, but not closed/ended, in which case only Start Date must be shown, and the Flag ‘Y’ assigned. The
How to take some value from value in one column in Teradata SQL?
I have table in Teradata SQL like below: The first value is always: “O|” then there is 4 numbers and so on… I need to create new column only with these 4 numbers code, so as a result I need something like below: How can I do that in Teradata SQL ? Answer Use STRTOK
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
How to generate dynamic table having begin month date and end month date Teradata or SAS SQL
I’d like to generate a dynamic Table with the start date of a month as a column and the end date of the month as another column. Ideally, I’d like to provide two years, f.e. 2016 and 2021. The output I’d like to have when providing these two years is the following: Kindly note that I require the output for