All Forums General
sasguy 4 posts Joined 05/13
24 May 2013
Teradata SQl Query with Group by

I have data  as follows in a Table ( 3 columns ):

 Name    StartDt         EndDt
A     01/01/2009    12/31/2009
 A     01/01/2010    11/30/2010
 A     03/01/2011    10/31/2011
 A     04/01/2012    12/31/2013
 B     08/01/2013    12/12/2013
 A     01/01/2014    08/01/2014
 A     08/02/2014    05/14/2015

Now I want to create a Output using Terdata Sql query as follows:

  Name    Min_Startdt    Max_Startdt
     A       01/01/2009    11/30/2010
     A       03/01/2011    10/31/2011
     A       04/01/2012    12/31/2013
     A       01/01/2014    05/14/2015
     B       08/01/2013    12/12/2013

If u notice the First Row corresponding to Name=A in the Output  is combination of first 2 rows of Input for Name=A since the Startdt and Enddt  for these 2 rows are continous ie Enddt+1 of first row = Startdt of second row. Similarily the 4th row in output is a combination of 6th and 7th row in Input for Name=A. Remaining rows form the Input remain unchanged in the output.

Please let me how this can be achieved via a Teradata Query.

dnoeth 4628 posts Joined 11/04
25 May 2013

This topic should answer your question:
http://forums.teradata.com/forum/general/need-help-merging-consecutive-and-overlapping-date-spans
 
Dieter

Dieter

sasguy 4 posts Joined 05/13
25 May 2013

Actually Dieter your Previous Post in this Forum answers my Question Perfectly :
 
http://forums.teradata.com/forum/database/how-to-find-the-continious-records-based-on-key-column#comment-12490
 
Thanks a lot.

frozenshine 7 posts Joined 03/15
09 Mar 2016

Hi why I am getting group by error for this:

SEL bpft.ACCT_ID, 

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-06-01') AND ('2015-06-30') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentJune,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-07-01') AND ('2015-07-31') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentJuly,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-08-01') AND ('2015-08-31') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentAug,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-09-01') AND ('2015-09-30') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentSep,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-10-01') AND ('2015-10-31') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentOct,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-11-01') AND ('2015-11-30') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentNov,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-12-01') AND ('2015-12-31') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentDec,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2016-01-01') AND ('2016-01-31') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentJan,

CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2016-02-01') AND ('2016-02-29') THEN  SUM(bpft.TOTAL_PMT_AMT) END AS PaymentFeb

FROM 

payment bpft 

WHERE bpft.BILL_PMT_DT BETWEEN ('2015-06-01') AND ('2016-02-29')

GROUP BY 1

ank

sasguy 4 posts Joined 05/13
09 Mar 2016

Try   This :
SEL bpft.ACCT_ID, 
Sum( CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-06-01') AND ('2015-06-30') THEN  bpft.TOTAL_PMT_AMT  
                  ELSE  0
       END ) AS PaymentJune,
Sum( CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-07-01') AND ('2015-07-31') THEN  bpft.TOTAL_PMT_AMT 
                  ELSE 0
      END ) AS PaymentJuly,
Sum( CASE WHEN bpft.BILL_PMT_DT BETWEEN ('2015-08-01') AND ('2015-08-31') THEN  bpft.TOTAL_PMT_AMT
                  ELSE 0
         END  ) AS PaymentAug,
.....
FROM 
payment bpft 
WHERE bpft.BILL_PMT_DT BETWEEN ('2015-06-01') AND ('2016-02-29')
GROUP BY 1
 

frozenshine 7 posts Joined 03/15
09 Mar 2016

Thank you it worked perfect! :) 

ank

You must sign in to leave a comment.