Can some one provide the solution for the below scenario?

Database

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

8 REPLIES

Re: Can some one provide the solution for the below scenario?

Please provide a solution for this?

N/A

Re: Can some one provide the solution for the below scenario?

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

Re: Can some one provide the solution for the below scenario?

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

N/A

Re: Can some one provide the solution for the below scenario?

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

N/A

Re: Can some one provide the solution for the below scenario?


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.

Re: Can some one provide the solution for the below scenario?

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.

Re: Can some one provide the solution for the below scenario?

Re: Can some one provide the solution for the below scenario?

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 .