All Forums Database
deva_048 8 posts Joined 04/12
15 Oct 2014
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
 

Raja_KT 1246 posts Joined 07/09
15 Oct 2014

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
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
16 Oct 2014
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...

Dieter

Umanadh 1 post Joined 10/14
16 Oct 2014

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');
 
 
 

umanadh

phi 2 posts Joined 10/14
17 Oct 2014

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 2 posts Joined 10/14
17 Oct 2014

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.

You must sign in to leave a comment.