All Forums Database
saikandimalla 5 posts Joined 08/12
27 Aug 2012
Can some one provide the solution for the below scenario?

I have data in the  table as follows

 

Table data:

cust_id

src_id

branch

date

amt

3108

0

111

1-Jan-12

100

3108

0

111

11-Jan-12

50

3108

0

111

30-Jan-12

50

3109

0

222

30-Jan-12

50

3109

0

222

31-Jan-12

50

3108

0

111

2-Feb-12

100

3110

0

333

29-Feb-12

50

3109

0

222

1-Mar-12

50

 

output

 

 

 

 

 

cust_id

src_id

branch

date

MTD

YTD

3108

0

111

31-Jan-12

200

200

3109

0

222

31-Jan-12

100

100

3108

0

111

29-Feb-12

100

300

3109

0

222

29-Feb-12

0

100

3110

0

333

29-Feb-12

50

50

3108

0

111

31-Mar-12

0

300

3109

0

222

31-Mar-12

50

150

3110

0

333

31-Mar-12

0

50

 

Note:

Carryforward the entries which are present in present in previous month and not existed in current moth with mtd as 0 and ytd as previous month amount

 

 

saikandimalla 5 posts Joined 08/12
27 Aug 2012

Please provide a solution for this?

ulrich 816 posts Joined 09/09
27 Aug 2012

No, as long as you are not providing a DDL and insert statements to create your test data.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

saikandimalla 5 posts Joined 08/12
28 Aug 2012

Here i am providing the DML& Inser statements aswel

 

CREATE VOLATILE TABLE sales
(
cust_id     INTEGER NOT NULL,
src_id     INTEGER,
branch     INTEGER,
month_end_date    DATE FORMAT 'yyyy-mm-dd',
amt INTEGER)
on commit preserve rows;

INSERT INTO sales (3108,0,111,'2012-01-01',100);
INSERT INTO sales (3108,0,111,'2012-01-11',50);
INSERT INTO sales (3108,0,111,'2012-01-30',50);
INSERT INTO sales (3109,0,222,'2012-01-30',50);
INSERT INTO sales (3109,0,222,'2012-01-31',50);
INSERT INTO sales (3108,0,111,'2012-02-02',100);
INSERT INTO sales (3110,0,333,'2012-02-29',50);
INSERT INTO sales (3109,0,222,'2012-03-01',50);

select  *  from sales order by 4 asc

ulrich 816 posts Joined 09/09
29 Aug 2012

I think you are looking for something like this...

 

select base.cust_id,
       base.src_id,
       base.branch,
       base.month_end_date,
       coalesce(amt,0) as MTD,
       sum(coalesce(amt,0)) over (partition by base.cust_id, base.src_id, base.branch order by base.month_end_date rows between unbounded preceding and current row) as YTD
from
	(
	select b.cust_id,
	       b.src_id,
	       b.branch,
	       c.calendar_date as month_end_date
	from
		(
		select cust_id, 
		       src_id,
		       branch, 
		       min(add_months((month_end_date-extract(day from month_end_date)),1)) as min_month_end_date
		from sales
		group by 1,2,3
		) as b
		cross join
		(
		select calendar_date
		from sys_calendar.calendar
		where calendar_date = add_months((calendar_date-extract(day from calendar_date)),1)
		and year_of_Calendar = extract(year from current_date)
		and calendar_Date <= current_date
		) as c
	where c.calendar_date >= b.min_month_end_date 
	) base
	left outer join
	(select cust_id,
	       src_id,
	       branch,
	       add_months((month_end_date-extract(day from month_end_date)),1) as month_end_date, 
	       sum(amt) as amt
	from sales
	group by 1,2,3,4
	) as months_amt
on base.cust_id = months_amt.cust_id
   and base.src_id = months_amt.src_id
   and base.branch = months_amt.branch
   and base.month_end_date = months_amt.month_end_date

P.S. Always specify a PI

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
31 Aug 2012

Saikandimalla, I am not doing your job.

Did you try to understand what this SQL does?

If it does not meet your needs try to adopt it to your requirements.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

kunmehta 2 posts Joined 12/13
09 Dec 2013

Hi Ulrich, Recently I come across the similar scenario where I having a source table containing fields Cust_id, Amount , Date. At a single point of time Source table will contain the record for same month.  Now I need a output with the fields Cust_id, date, MTD, YTD. .!!

I am beginner but have to complete this in given deadline. Please help me providing a way to solve this.

M.Saeed Khurram 544 posts Joined 09/12
teradata_chela2 18 posts Joined 10/13
11 Dec 2013

Hi saikandimalla   
I am not sure if insert statements given by you are right  .
 
Below query will work for the results that you want .
 
SEL
cust_id,
src_id ,
branch,
month_end_date,
MAX(MTD) ,
MAX(YTD)
FROM
(
SEL
cust_id,
src_id ,
branch,
ADD_MONTHS( month_end_date -EXTRACT( DAY FROM month_end_date ) , 1 )month_end_date ,
SUM(amt) OVER ( PARTITION BY cust_id ,src_id, branch ,EXTRACT( MONTH FROM month_end_date ) ORDER BY month_end_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) MTD,
SUM(amt) OVER ( PARTITION BY cust_id ,src_id, branch ORDER BY month_end_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) YTD
FROM sales
)a
GROUP BY 1,2,3,4
 
Let me know if doesnt work .

Arint

You must sign in to leave a comment.