Teradata SQl Query with Group by

General
Fan

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.

5 REPLIES
Junior Contributor

Re: Teradata SQl Query with Group by

Fan

Re: Teradata SQl Query with Group by

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#com...

Thanks a lot.

Enthusiast

Re: Teradata SQl Query with Group by

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

Fan

Re: Teradata SQl Query with Group by

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

Enthusiast

Re: Teradata SQl Query with Group by

Thank you it worked perfect! :)