4 Week rolling average.. is this possible? 1

fmrock · Aug 8, 2008

Hey Everyone,

I have request, that i have no idea on how to do this in SQL and I am thinking this may have to be an application that does this.

I have the following query.

Code:

SELECT 
	CHARGES.CPCODE Site, 
	CHARGES.BILLSTATUS FC,
	Sum(CHARGES.PRAMOUNT) Payment_Amount,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
FROM 
	CHARGES CHARGES
WHERE
	(CHARGES.TYPE='P') 
	AND (CHARGES.SPLITFLAG IS  NULL)
	AND CHARGES.POSTDATE BETWEEN to_number(to_char(trunc(sysdate,'W'),'J')-(52*7)) AND to_number(to_char(trunc(sysdate,'W'),'J'))
GROUP BY
	CHARGES.CPCODE,
	CHARGES.BILLSTATUS,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')

This sums up the amount of payments we get per location per financial class.

Pretty simple right? Now we have the request to create a 4 week rolling average. Basiclly for each week, we want to average the last 4 weeks, except for the most current week, it should not do the average.

is this possible? Any ideas or suggestions.

SantaMufasa · Aug 8, 2008

FMRock,

If you post the CREATE TABLE CHARGES... statement and INSERT INTO CHARGES VALUES... statements that give us a data basis for creating code to do what you want, I'm sure we can (using Oracle Analytics) provide a solution. (You don't need to create any columns or data beyond those that appear in your sample code, above.)

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

fmrock · Aug 8, 2008

Here is a create statement.

This is for a 3rd party vendor and we only have query access, and no oracle system to test this on.

Ill work on the select statements.

Code:

create table CHARGES (
  CPCODE varchar2(10),
  BILLSTATUS varchar2(2),
  TYPE varchar2(1),
  PRAMOUNT number,
  POSTDATE varchar2(8),
  SPLITFLAG varchar2(1),
  CURRYEAR  number,
  CURRMONTH  number
)

fmrock · Aug 8, 2008

Here are some inserts i made up.

Code:

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-100,'2454687',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-175,'2454687',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-400,'2454687',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-99,'2454687',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-700,'2454283',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-200,'2454283',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-50,'2454283',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-1250,'2454283',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-1250,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-250,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-99,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-12,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-45,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-75,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-100,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-100,'2454289',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-100,'2454296',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-86.50,'2454296',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-78.50,'2454303',NULL,2008,08);

INSERT INTO CHARGES
	(CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH)
VALUES	('111111', 'S','P',-15.50,'2454310',NULL,2008,08);

fmrock · Aug 8, 2008

I also noticed post month is a string and updated my query to the following.

Code:

SELECT 
	CHARGES.CPCODE Site, 
	CHARGES.BILLSTATUS FC,
	Sum(CHARGES.PRAMOUNT) Payment_Amount,
	AVG(Sum(CHARGES.PRAMOUNT)) OVER
		(ORDER BY 
			CHARGES.CPCODE,
			CHARGES.BILLSTATUS,
			to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)),
			to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')
		 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) Rolling_Payment_AVG,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
FROM 
	CHARGES
WHERE
	(CHARGES.TYPE='P') 
	AND (CHARGES.SPLITFLAG IS  NULL)
	AND (MEDGROUP.CPACCTSTATUS='A')

	AND CHARGES.POSTDATE BETWEEN to_char(trunc(sysdate,'W'),'J')-(52*7) AND to_char(trunc(sysdate,'W'),'J')
GROUP BY
	CHARGES.CPCODE,
	CHARGES.BILLSTATUS,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')

This seems to work, however its not reseting after each CPCODE and BILLSTATUS

fmrock · Aug 8, 2008

Mistake in the last query

Code:

SELECT 
	CHARGES.CPCODE Site, 
	CHARGES.BILLSTATUS FC,
	Sum(CHARGES.PRAMOUNT) Payment_Amount,
	AVG(Sum(CHARGES.PRAMOUNT)) OVER
		(ORDER BY 
			CHARGES.CPCODE,
			CHARGES.BILLSTATUS,
			to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)),
			to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')
		 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) Rolling_Payment_AVG,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
FROM 
	CHARGES
WHERE
	(CHARGES.TYPE='P') 
	AND (CHARGES.SPLITFLAG IS  NULL)
	AND CHARGES.POSTDATE BETWEEN to_char(trunc(sysdate,'W'),'J')-(52*7) AND to_char(trunc(sysdate,'W'),'J')
GROUP BY
	CHARGES.CPCODE,
	CHARGES.BILLSTATUS,
	to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')

SantaMufasa · Aug 8, 2008

Thanks, FMRock, for posting the code...It saves soooo much time to have good data values to work from (rather then my bogusing up values that I make up).

When I run your code, above (and I comment out the redundant "to_char..." line in your ORDER BY clause, which throws a "ORA-00979: not a GROUP BY expression"), your code results in this output:

Code:

SITE       FC PAYMENT_AMOUNT ROLLING_PAYMENT_AVG WEEK
---------- -- -------------- ------------------- ------------------
111111     S            -774                -774 2008-08 2008-08-03

Given the data you provided, how do you want the output to differ from the above? (I.e., what do you want the output to look like, given your data rows?)

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

fmrock · Aug 8, 2008

That is pretty much what i want the data to look like,

Sorry, I forgot to take that one out, as i have some extra stuff in the query that i took out to simplify it for the post.

However, there are 70 sites, and each has 7 billstatus's.

I want the Rolling_Payment_AVG to reset for each site/billstatus.

I am using crystal reports to pivot this data in report which so the sites and bill status is the rows, and the weeks are the headers of each column.

Right now, it just does the avg over it all.

Hope i am explaing it correctly.

SantaMufasa · Aug 8, 2008

Yes, I understand, FMRock, but I'm hoping you can/will post enough extra INSERT statements to test/reflect the output you want. (I must leave presently to run an errand, but if you post enough more representative row INSERTs that will fully exercise the SQL (thus demonstrating proof-of-concept of the code and illustrate all output scenarios you want), I'll attempt to work on it when I finish my errands...Also remember, Olympics Opening Ceremonies tonight. <grin>

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

Dagon · Aug 8, 2008

Your data only appears to contain one site/billstatus, so perhaps it's not surprising you only get one row back.

You say the requirements are pretty simple, but I'm not sure they are. You say you want a 4 week rolling average but if I just do a straight query on your data:

Code:

select * from charges
WHERE
    (CHARGES.TYPE='P')
    AND (CHARGES.SPLITFLAG IS  NULL)
    AND CHARGES.POSTDATE BETWEEN to_char(trunc(sysdate,'W'),'J')-(52*7) AND to_char(trunc(sysdate,'W'),'J')

CPCODE     BI T   PRAMOUNT POSTDATE S   CURRYEAR  CURRMONTH
---------- -- - ---------- -------- - ---------- ----------
111111     S  P       -100 2454687          2008          8
111111     S  P       -175 2454687          2008          8
111111     S  P       -400 2454687          2008          8
111111     S  P        -99 2454687          2008          8

The problem is that all the dates, sites and billstatuses are identical, so how exactly can you be computing a moving average over the date ?

You are also doing an average of the sum of the values. What exactly are you trying to achieve with this ? A moving average will be computing the sum for you as it goes. Also, for a moving average to be meaningful, you would have to display all the rows so you can see what the average is at any point.

As a side issue, I intensely dislike mixing conventional "group by" with windowing functions. I'm not really sure what it means when you have a sum over a partitioning function but then apply a conventional average to it. In my view, if you're going to use windowing functions, you should use them for everything and dispense with the ordinary group by.

fmrock · Aug 9, 2008

I will make up some more data, for a better example.

The original query was just to sum up the amount of payments per week.... after the end users looked it over, they are now asking for a 4 week rolling average.

Also, I have never done anything like this and just playing with what i found on the net...

fmrock · Aug 11, 2008

Here is some more data.

Code:

INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454636',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-283.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-302.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-121.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-15.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-327.99,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',68.00,'2454659',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-121.00,'2454636',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-328.00,'2454636',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-40.00,'2454644',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-304.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-155.20,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-100.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-119.20,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-15.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-130.99,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-182.40,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-182.40,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-121.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-167.20,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-15.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-119.20,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-164.90,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-294.41,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','G','P',-194.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-81.87,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',0.00,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-203.34,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-247.64,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-247.98,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-218.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-475.02,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-92.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-113.82,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-61.62,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-219.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-16.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-80.43,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-160.23,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-222.04,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-80.43,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-148.59,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-148.59,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-203.28,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-27.37,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-80.43,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-71.41,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',0.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-151.29,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-71.41,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-126.30,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-71.41,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-161.64,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-174.90,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-148.60,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-85.52,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-174.90,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-80.43,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-219.00,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-339.00,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-126.30,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-111.55,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-148.59,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','G','P',-76.30,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-30.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-15.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-32.23,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454644',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454636',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-5.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',105.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-71.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454636',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-59.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-155.20,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-243.20,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-15.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-15.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-15.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-357.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-670.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-347.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-172.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-17.84,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-50.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',161.14,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-59.50,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454644',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-109.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-49.98,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-2.10,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454648',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-357.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-15.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-433.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-15.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-50.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-50.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-60.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-60.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-10.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-25.00,'2454659',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-670.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-257.00,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-272.00,'2454644',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-357.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-194.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-20.00,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-50.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',104.57,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',144.00,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-33.16,'2454649',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-1,271.13,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-3,300.66,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-6,522.91,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',988.70,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',3,300.66,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',6,522.91,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-988.70,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('111111','S','P',-906.72,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-60.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-65.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-15.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-218.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-30.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-60.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-40.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-5.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-5.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',20.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-75.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-18.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-50.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-15.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-90.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-15.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-30.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-50.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-37.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-50.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-219.00,'2454658',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-119.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-103.83,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-17.28,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-109.00,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-133.50,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-219.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-16.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-36.50,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-602.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-30.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-5.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-5.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-36.50,'2454636',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-50.00,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454638',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-402.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-875.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-50.00,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454642',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-219.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-219.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-16.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-30.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-36.50,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-15.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-54.75,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-52.00,'2454641',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-100.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-15.00,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-402.00,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-402.00,'2454631',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-402.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-219.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-219.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-169.00,'2454644',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-602.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-16.00,'2454637',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.00,'2454634',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-35.00,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-85.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-25.00,'2454635',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-602.00,'2454645',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-20.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-402.00,'2454650',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-202.00,'2454656',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-302.00,'2454651',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-200.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-265.50,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-73.60,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-10.88,'2454657',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-169.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-69.67,'2454659',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-340.00,'2454655',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-59.17,'2454630',NULL,2008,5);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',667.00,'2454643',NULL,2008,6);
INSERT INTO CHARGES (CPCODE,BILLSTATUS,TYPE,PRAMOUNT,POSTDATE,SPLITFLAG,CURRYEAR,CURRMONTH); VALUES ('222222','S','P',-667.00,'2454642',NULL,2008,6);

Dagon · Aug 11, 2008

Can you tell us:

a) which part of the data you want the query to operate on, if it's not the whole set

b) a mock-up of what the results of the query should show for that data set.

fmrock · Aug 11, 2008

I guess i am not sure what your asking for, but I just want to add the rolling average column to the report.

This would take the average of the current week, plus the last 3 weeks. This would just need to restart on each group/bill status.

SantaMufasa · Aug 11, 2008

FMRock,

So, given the additional data that you posted, and given the code that you posted (minus the extraneous expression, "to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0))," in your ORDER BY clause) here are the results:

Code:

SITE       FC PAYMENT_AMOUNT ROLLING_PAYMENT_AVG WEEK
---------- -- -------------- ------------------- ------------------
111111     G          -376.4              -376.4 2008-05 2008-06-08
111111     G            -764              -570.2 2008-06 2008-06-15
111111     G          -855.4          -665.26667 2008-06 2008-06-22
111111     G        -1503.09           -874.7225 2008-06 2008-06-29
111111     G           -1400          -1130.6225 2008-06 2008-07-06
111111     S        -7941.84          -2925.0825 2008-05 2008-06-08
111111     S         3083.36          -1940.3925 2008-06 2008-06-15
111111     S        -2012.84            -2067.83 2008-06 2008-06-22
111111     S          -326.3           -1799.405 2008-06 2008-06-29
111111     S         -526.45             54.4425 2008-06 2008-07-06
111111     S            -774           -909.8975 2008-08 2008-08-03
222222     G          -634.3           -565.2625 2008-05 2008-06-08
222222     G         -769.62           -676.0925 2008-06 2008-06-15
222222     G        -1410.22            -897.035 2008-06 2008-06-22
222222     G         -846.63           -915.1925 2008-06 2008-06-29
222222     G        -1954.05            -1245.13 2008-06 2008-07-06
222222     S         -631.17          -1210.5175 2008-05 2008-06-08
222222     S        -1131.86          -1140.9275 2008-06 2008-06-15
222222     S           -3108            -1706.27 2008-06 2008-06-22
222222     S         -1655.5          -1631.6325 2008-06 2008-06-29
222222     S        -4294.15          -2547.3775 2008-06 2008-07-06

Given your specifications, isn't this what you wanted?...a rolling 4-week average?

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

fmrock · Aug 11, 2008

Kind of, but if you look at the line.

Code:

222222     G          -634.3           -565.2625 2008-05 2008-06-08

The rolling average should start over and not take into the calculation from the lines before it in that group.

Dagon · Aug 12, 2008

You'll need to explain this better. If I run:

Code:

select cpcode, billstatus, pramount,  to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
from charges
WHERE
    (CHARGES.TYPE='P') 
    AND (CHARGES.SPLITFLAG IS  NULL)
    AND CHARGES.POSTDATE BETWEEN to_char(trunc(sysdate,'W'),'J')-(52*7) AND to_char(trunc(sysdate,'W'),'J')
	and cpcode = '222222'
	and billstatus = 'G'
ORDER BY to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD')

I get:

CPCODE     BILLSTATUS PRAMOUNT                               WEEK                                                   
222222     G          -219                                   2008-05 2008-06-08                                     
222222     G          -339                                   2008-05 2008-06-08                                     
222222     G          -76.3                                  2008-05 2008-06-08                                     
222222     G          -218                                   2008-06 2008-06-15                                     
222222     G          -92                                    2008-06 2008-06-15                                     
222222     G          -222.04                                2008-06 2008-06-15                                     
222222     G          -148.59                                2008-06 2008-06-15                                     
222222     G          -61.62                                 2008-06 2008-06-15                                     
222222     G          -27.37                                 2008-06 2008-06-15                                     
222222     G          -475.02                                2008-06 2008-06-22                                     
222222     G          -161.64                                2008-06 2008-06-22                                     
222222     G          -85.52                                 2008-06 2008-06-22                                     
222222     G          -174.9                                 2008-06 2008-06-22                                     
222222     G          -126.3                                 2008-06 2008-06-22                                     
222222     G          -219                                   2008-06 2008-06-22                                     
222222     G          -16                                    2008-06 2008-06-22                                     
222222     G          -71.41                                 2008-06 2008-06-22                                     
222222     G          -80.43                                 2008-06 2008-06-22                                     
222222     G          0                                      2008-06 2008-06-29                                     
222222     G          -80.43                                 2008-06 2008-06-29                                     
222222     G          -148.59                                2008-06 2008-06-29                                     
222222     G          -174.9                                 2008-06 2008-06-29                                     
222222     G          -148.6                                 2008-06 2008-06-29                                     
222222     G          -71.41                                 2008-06 2008-06-29                                     
222222     G          -151.29                                2008-06 2008-06-29                                     
222222     G          0                                      2008-06 2008-06-29                                     
222222     G          -71.41                                 2008-06 2008-06-29                                     
222222     G          -81.87                                 2008-06 2008-07-06                                     
222222     G          -113.82                                2008-06 2008-07-06                                     
222222     G          -111.55                                2008-06 2008-07-06                                     
222222     G          -126.3                                 2008-06 2008-07-06                                     
222222     G          -80.43                                 2008-06 2008-07-06                                     
222222     G          -203.34                                2008-06 2008-07-06                                     
222222     G          -80.43                                 2008-06 2008-07-06                                     
222222     G          -203.28                                2008-06 2008-07-06                                     
222222     G          -148.59                                2008-06 2008-07-06                                     
222222     G          -160.23                                2008-06 2008-07-06                                     
222222     G          -247.64                                2008-06 2008-07-06                                     
222222     G          -247.98                                2008-06 2008-07-06

Therefore, the first three values when the query is ordered by your peculiar "week" expression do add up to -634.3 and, since there are no rows prior to those, that would be the average at that point.

Or do you mean you want it reset to zero until there are enough values to provide a 4 week average ?

fmrock · Aug 12, 2008

It can average what we have, or set it to zero. The average should just start over for every CPCODE/BILLSTATUS Grouping.

PS... Thank you both for helping me with this.

Dagon · Aug 12, 2008

Try this:

Code:

select cpcode, billstatus, 
sum(pramount) over (partition by cpcode, billstatus, week) as weekly_sum, 
avg(pramount) over (partition by cpcode, billstatus
order by week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as rolling_avg
from
(select cpcode, billstatus, sum(pramount) pramount,  
to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || 
to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
from charges
WHERE
    (CHARGES.TYPE='P') 
    AND (CHARGES.SPLITFLAG IS  NULL)
    AND CHARGES.POSTDATE BETWEEN to_char(trunc(sysdate,'W'),'J')-(52*7) AND to_char(trunc(sysdate,'W'),'J')
group by cpcode, billstatus, 
to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || 
to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD'))

Dagon · Aug 12, 2008

That can be simplified to:

Code:

select cpcode, billstatus, 
pramount weekly_sum, 
avg(pramount) over (partition by cpcode, billstatus
order by week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as rolling_avg
from
(select cpcode, billstatus, sum(pramount) pramount,  
to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || 
to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD') Week
from charges
WHERE
    (CHARGES.TYPE='P') 
    AND (CHARGES.SPLITFLAG IS  NULL)
    AND CHARGES.POSTDATE BETWEEN to_char(trunc(sysdate,'W'),'J')-(52*7) AND to_char(trunc(sysdate,'W'),'J')
group by cpcode, billstatus, 
to_char(CHARGES.CURRYEAR) || '-' || to_char(LPAD(CHARGES.CURRMONTH,2,0)) || ' ' || 
to_char(next_day(trunc(to_date(CHARGES.POSTDATE,'j'))-7,'SUNDAY'),'YYYY-MM-DD'))

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

4 Week rolling average.. is this possible? 1

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

MIS

Programmer

Programmer

MIS

Programmer

Technical User

Programmer

MIS

Programmer

MIS

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor