I have data in the table as follows
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
Here i am providing the DML& Inser statements aswel
CREATE VOLATILE TABLE sales
cust_id INTEGER NOT NULL,
month_end_date DATE FORMAT 'yyyy-mm-dd',
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
I think you are looking for something like this...
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
c.calendar_date as month_end_date
min(add_months((month_end_date-extract(day from month_end_date)),1)) as min_month_end_date
group by 1,2,3
) as b
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
left outer join
add_months((month_end_date-extract(day from month_end_date)),1) as month_end_date,
sum(amt) as amt
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
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.
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.
You can find YTD and MTD caiolculation examples here:
I am not sure if insert statements given by you are right .
Below query will work for the results that you want .
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
GROUP BY 1,2,3,4
Let me know if doesnt work .