Update the table with min and max timestamp

Database
Enthusiast

Update the table with min and max timestamp

What  all are possible ways to get the exact output mentioned below: start and endtime is timestamp fields ... In the final output starttime should take from status A and end time should take from status C .

Please help me

TAble A contains:

Name Status Start time and end time

A0001 A         08:00              08:10

A0001 C         09:00              09:10

A0002 C         10:00              10:10

Output should be:

Name Status Start time      end time

A0001 C         08:00              09:10

A0002 C         10:00              10:10

5 REPLIES
Enthusiast

Re: Update the table with min and max timestamp

Try this:

select name,st_time,end_tm from

(select name,max(status)over(partition by name order by start_time) st,min(start_time) over(partition by name order by start_time) st_time,max(end_time) over(partition by name order by end_time) end_tm from your_tbl) t

group by 1,2,3,4

 

You can even think of using qualify

select name,max(status)over(partition by name order by start_time) st,min(start_time) over(partition by name order by start_time) st_time,max(end_time) over(partition by name order by end_time) end_tm from your_tbl

qualify row_number() over(partition by name order by name,start_time)=1

Senior Apprentice

Re: Update the table with min and max timestamp

select Name, 
min(case when Status = 'A' then Starttime end) end,
max(case when Status = 'C' then endtime end) end
from tab
group by 1

This seems to match your data...

Re: Update the table with min and max timestamp

dnoeth,

I am new to teradata, and facing a small issue with partition. can you help me.

I implemented a Partition by CASE ON DW_ACTIVE column which tells us about Active rows/inactive rows in below table.

my users always applies a filter condition on DW_ACTIVE for tables and in joins. So, i applied a PPI with CASE and it was worked fine with small loads. All of suddenly i not finding PARTITION keyword in explain plan. I am realy wonder, why it is behaving.

Do you know, when Partition with CASE works , and which indes needs to apply on this situation.

Explain

SELECT *

  FROM  SANDBOX.personal_ddl_tcs

 WHERE DW_ACTIVE='Y'

  1) First, we lock a distinct DBA_SANDBOX."pseudo table" for read on a

     RowHash to prevent global deadlock for

     DBA_SANDBOX.SALES_ORDER_LINE_ITEM_CPNT.

  2) Next, we lock DBA_SANDBOX.SALES_ORDER_LINE_ITEM_CPNT for read.

  3) We do an all-AMPs RETRIEVE step from

     DBA_SANDBOX.SALES_ORDER_LINE_ITEM_CPNT by way of an all-rows scan

     with a condition of (

     "DBA_SANDBOX.SALES_ORDER_LINE_ITEM_CPNT.DW_ACTIVE = 'Y'") into

     Spool 1 (group_amps), which is built locally on the AMPs.  The

     size of Spool 1 is estimated with high confidence to be 638,804

     rows (107,957,876 bytes).  The estimated time for this step is

     0.16 seconds.

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.16 seconds.

trouble shoot:

SEL DW_ACTIVE, PARTITION, COUNT(*)

FROM  SANDBOX.personal_ddl_tcs

GROUP BY 1,2

                DW_ACTIVE       PARTITION          Count(*)

                N               3              3,447

                X               1              3

                Y              2              638,804

--DDL:

CREATE SET TABLE SANDBOX.personal_ddl_tcs ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

     )

PRIMARY INDEX ( SALES_LINE_ID )

PARTITION BY CASE_N(

DW_ACTIVE =  'X',

DW_ACTIVE =  'Y',

DW_ACTIVE =  'N',

DW_ACTIVE =  'D');

phi
Fan

Re: Update the table with min and max timestamp

If your example is correct, this should do it:

SELECT 
name
,MAX(status)
,MIN(start_time) AS start_time
,MAX(CASE WHEN Status = 'C' THEN end_time ELSE NULL END) AS end_time
FROM tab
GROUP BY 1
ORDER BY 1

because A0002 has no Status A record. Otherwise - if there is always an A record - the following should work (sounds more reasonable):

SELECT 
name
,MAX(status)
,MIN(CASE WHEN Status = 'A' THEN start_time ELSE NULL END) AS start_time
,MAX(CASE WHEN Status = 'C' THEN end_time ELSE NULL END) AS end_time
FROM phi_temp
GROUP BY 1
ORDER BY 1

phi
Fan

Re: Update the table with min and max timestamp

umanadh,

I suppose that the optimizer realizes that the data in the selected partition 'Y' is more than 99% of the whole table, so partition elemination would cost more than it would save.

Try selecting the other values, suppose that there it is different.