Merging 2 rows into one single row

Database

Merging 2 rows into one single row

I have a situation where I need to Insert 2 rows(little info from second row) into single row.

Data looks like this:

Date              Service       CurrMonth   CURR_COUNTS

2016-03-01   Internet      March           10

2016-04-01   Internet      April              10

I need a result set in this way: have to show the latest month date and put the Curr month as prev month in single row with corresponding counts.

DATE            Service    Curr_Month    Prev_Month  CURR_M_COUNTS PREV_M _COUNTS

2016-04-01 Internet  April                 March                   10                         10

Have written following query:

WITH RECURSIVE rec_test AS

(select DATE,

Service, CURR_MONTH,

CURR_EOP_CNTS,

LAG(CURR_MONTH)  OVER(Partition by Service Order by DATE) as [Previous_Month],

LAG(CURR_COUNTS)  OVER(Partition by Service Order by DATE) as [Previous_Counts],

ROW_NUMBER() Over (Partition by Service Order by SNAPSHOT_DATE desc) as R1

 from PROFITABILITY_RPTS.AUD_PARS_NET_ADDS_LKP)

 select DATE, Service, CURR_MONTH, CURR_CNTS, Previous_Month, Previous_Counts

  from rec_test where R1=1

I get an error saying

"Data Type "CURR_MONTH" does not match a defined Type Name"

Please help.

Thanks

1 REPLY
Senior Apprentice

Re: Merging 2 rows into one single row

There's no LAG/LEAD in Teradata, you must rewrite it. And you don't need a CTE, there's QUALIFY:

select DATE,
Service, CURR_MONTH,
CURR_EOP_CNTS,
MIN(CURR_MONTH)
OVER(Partition by Service
Order by DATE
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as Previous_Month,
MIN(CURR_COUNTS)
OVER(Partition by Service
Order by DATE
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as Previous_Counts
from PROFITABILITY_RPTS.AUD_PARS_NET_ADDS_LKP
QUALIFY
ROW_NUMBER()
Over (Partition by Service
Order by SNAPSHOT_DATE desc) = 1