asked on

Oracle NVL splitting output on query

after much help on a previous thread which resolved most of my problem, i am trying to finalize a query.. the problem is the math is not totally right..

I am posting links to 2 images

This image is the result I am getting now (which is wrong)
http://img713.imageshack.us/img713/8172/28361428.png

as you can see i am getting 2 values for prod_num 3560

This image is the desired output on the query (which is what I am getting when running the query from access)

http://img24.imageshack.us/img24/2473/76845801.png

I think my main issue is the query returning null values and returning 2 outputs per prod_num

this is the SQL code im currently using which was done with the help i received on a previous thread

SELECT 
ARIEL_ONE_WEEK_SALES.PROD_NUM
, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION
, ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME
, Sum(case when CUST_NUM<>16 then ceil(UNITS/PD_UNTCAS) end) AS DELIVERED_CASES
, case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases
, ariel_all_commissions.rebate
, Sum(case when CUST_NUM<>16 then ceil(UNITS/PD_UNTCAS) end)*rebate as sold_cases_rebate
, nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate as rebate_for_samples
, Sum(case when CUST_NUM<>16 then ceil(UNITS/PD_UNTCAS) end)*rebate + nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) AS TOTAL_REBATE
, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE
FROM ARIEL_ONE_WEEK_SALES 
INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM
, ARIEL_ONE_WEEK_SALES.PM_PRDESC
, ARIEL_ONE_WEEK_SALES.PM_VOLUME
, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE
, ARIEL_ALL_COMMISSIONS.Rebate
, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE
, case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end

Open in new window

Sean Stuber

you are grouping by

CASE WHEN cust_num = 16 THEN NVL(units / pd_untcas, 0) END AS sample_cases,

which produces 2 results,

one with NULL
one with 1

since you are grouping by 2 different sets of data, you must have 2 rows

I have no idea how your expected results have 6 sample_cases - please explain

gnivkor

ASKER

I am getting 6 sample cases because

Sample Cases = (Sum of units) / pd_untcas when cust_num = 16

so i have 6 records with cust_num = 16 and prod_num = 3650

the sum of "UNITS" is 144 (24 units per record) and since pd_untcast = 24
144/24 = 6

but when i devide pd_untcas, i am not dividing the sum

the code that i have for that is

case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases

Open in new window

on the working access database, the sql code for that is

IIf((Sum(IIf(CUST_NUM=16,UNITS/PD_UNTCAS))) Is Null,0,(Sum(IIf(CUST_NUM=16,UNITS/PD_UNTCAS)))) AS SAMPLE CASES,

Open in new window

gnivkor

ASKER

so in reality, i am trying to do

sum of "Units" divided the value in pD_UNTCAS, not the sum of it

gnivkor

ASKER

anyone ? :\

Sean Stuber

can you provide sample input data and expected output.

please no pictures, post text I can import and test with

gnivkor

ASKER

sure,, gimme a few secs

gnivkor

ASKER

This is the data for ARIEL_ONE_WEEK_SALES

  CREATE TABLE "ARIEL_ONE_WEEK_SALES" ("CUST_NUM" NUMBER(14,0), "CUSTOMER_NAME" VARCHAR2(50), "ADDRESS" VARCHAR2(50), "PHONE" VARCHAR2(20), "SALESMAN" VARCHAR2(30), "SLSMAN_NUM" NUMBER(5,0), "PROD_NUM" VARCHAR2(10), "UNITS" NUMBER(5,0), "TH_DOLRS" NUMBER(20,6), "TH_PRICE" NUMBER(20,6), "CM_CHNID" VARCHAR2(3), "ID_FLDNUM" NUMBER(3,0), "CITY" VARCHAR2(28), "STATE" VARCHAR2(2), "ZIP" VARCHAR2(10), "PD_BRANCH" NUMBER(5,0), "PM_PRDESC" VARCHAR2(30), "PM_VOLUME" VARCHAR2(15), "PD_UNTCAS" VARCHAR2(4), "S_DATE" VARCHAR2(11), "PM_BRAND_CODE" NUMBER(6,0))
/


Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (32042,'#42nd Street Eatery','20 E 42ND STREET','(484 ) 682-4242','SOME SALESMAN',2,'3650',24,24.65,1.02709,'FV',2,'Canada','CA','10017',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99112,'Corbet & Conley','335 East 23rd Street','(484 ) 532-9119','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10010',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9726,'#Cafe O 42','130 West 42nd Street','(484 ) 852-4900','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10036',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (81842,'H k Seven Market Inc','707 SECOND AVENUE','(484 ) 922-1484','SOME SALESMAN',2,'3650',24,24.65,1.02709,'MR',2,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62005,'#Silo Cafe 2','805 3RD AVENUE','(484 ) 888-7456','SOME SALESMAN',2,'3650',24,24.65,1.02709,'FV',2,'Canada','CA','10022',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (47965,'#47th j & j Gourmet','155 W 47TH STREET','(484 ) 827-0877','SOME SALESMAN',2,'3650',24,24.65,1.02709,'FV',2,'Canada','CA','10036',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9748,'#Fine Foods & Deli','411 2nd Avenue','(484 ) 683-4699','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (68203,'#Bread & Butter','419 PARK AVENUE SOUTH','(484 ) 683-0444','SOME SALESMAN',2,'3650',24,24.65,1.02709,'FV',2,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9796,'#Health King','642 Lexington Avenue','(484 ) 593-1020','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10022',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99409,'*#Mangia','50 West 57th Street','(484 ) 582-5882','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99356,'#220 East Newstand','220 East 42nd Street','(484 ) 986-2745','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10017',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9961,'#Green Tomato','295 7th Avenue','(484 ) 352-9174','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10001',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9769,'#Deli On Madison','420 Madison Avenue','(484 ) 308-6140','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10017',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99409,'*#Mangia','50 West 57th Street','(484 ) 582-5882','SOME SALESMAN',2,'3650',48,49.3,1.02709,'AC',2,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/30/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99498,'#Health Nut','835 2nd Avenue','(484 ) 490-2979','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10017',33,'ALP SOME ITEM','8.33Z','24','3/28/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99409,'*#Mangia','50 West 57th Street','(484 ) 582-5882','SOME SALESMAN',2,'3650',48,49.3,1.02709,'AC',2,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/23/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62705,'#*Gourmet Park','119 EAST 60TH STREET','(484 ) 935-8500','SOME SALESMAN',2,'3650',-24,-24.65,1.02709,'MR',2,'Canada','CA','10022',33,'ALP SOME ITEM','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62705,'#*Gourmet Park','119 EAST 60TH STREET','(484 ) 935-8500','SOME SALESMAN',2,'3650',24,24.65,1.02709,'MR',2,'Canada','CA','10022',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99621,'#Essa Bagels','831 3rd Avenue','(484 ) 980-1010','SOME SALESMAN',2,'3650',48,49.3,1.02709,'AC',2,'Canada','CA','10022',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (668659,'Tudor City News','5 TUDOR CITY','(484 ) 557-4788','SOME SALESMAN',2,'3650',24,24.65,1.02709,'HV',2,'Canada','CA','10022',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (68200,'#Au Bon Gout','276 5TH AVENUE','(484 ) 685-2727','SOME SALESMAN',2,'3650',24,24.65,1.02709,'FV',2,'Canada','CA','10001',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (76012,'#Fork And Spoon Gourmet Food','7 WEST 36TH STREET','(484 ) 244-3204','SOME SALESMAN',2,'3650',24,24.65,1.02709,'MR',2,'Canada','CA','10018',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99412,'#Well Green','350 West 42nd Street','(484 ) 560-4300','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10036',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9819,'#Cafe "O"','622 3rd Avenue','(484 ) 599-1560','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10003',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99623,'#Patel Candy','1155 6th Avenue','(484 ) 997-0931','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10036',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99622,'#Canada Bagels','587 1st Avenue','(484 ) 725-3777','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99351,'#Cafe Trend','596 3rd Avenue','(484 ) 286-0388','SOME SALESMAN',2,'3650',24,24.65,1.02709,'AC',2,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (668210,'#Ramses Deli','732 10TH AVENUE','(484 ) 977-3500','SALESLADY',8,'3650',24,24.65,1.02709,'HV',8,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9701,'*#51 Market','721 11th Avenue','(484 ) 245-2882','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9702,'*#49 Market','682 11th Avenue','(484 ) 245-5551','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99601,'Pioneer Supermarket','289 Columbus Avenue','(484 ) 874-9506','SALESLADY',8,'3650',144,147.9,1.02709,'AC',8,'Canada','CA','10023',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (314823,'#301 Amsterdam Corp.','301 AMSTERDAM AVENUE','(484 ) 877-2253','SALESLADY',8,'3650',24,24.65,1.02709,'FV',8,'Canada','CA','10023',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9865,'Westerly','911 8th Avenue','(484 ) 586-5262','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (64719,'*Brian Deli','153 AMSTERDAM AVENUE','(484 ) 496-5190','SALESLADY',8,'3650',24,24.65,1.02709,'MR',8,'Canada','CA','10023',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9865,'Westerly','911 8th Avenue','(484 ) 586-5262','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10019',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99145,'*#63 Bites','5 W 63rd Street','(484 ) 877-6363','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10023',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9838,'*#99 Cents Brush','2050 Lexington Avenue','(484 ) 876-9851','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10035',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9835,'*#Midtown Deli','840 8th Avenue','(484 ) 586-6433','SALESLADY',8,'3650',0,0,0,'AC',8,'Canada','CA','10001',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9740,'*#99 Cents Dream','2 West 46th Street','(484 ) 840-1995','SALESLADY',8,'3650',48,49.3,1.02709,'AC',8,'Canada','CA','10036',33,'ALP SOME ITEM','8.33Z','24','3/26/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99129,'*#Ranch Deli','980 Columbus Avenue','(484 ) 531-8777','SALESLADY',8,'3650',24,24.65,1.02709,'AC',8,'Canada','CA','10029',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (81505,'Martin News','200 VESSEY STREET','(484 ) 640-0443','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'MR',10,'Canada','CA','10285',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (665770,'Gary Vending','290 BROADWAY','(484 ) 785-6590','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'MR',10,'Canada','CA','10007',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (668897,'#Han''s','645 BROADWAY','(484 ) 473-8596','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'FV',10,'Canada','CA','10012',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99381,'#Grand Street Deli Mini Market','42 Grand Street','(484 ) 625-3248','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'AC',10,'Canada','CA','10013',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99187,'#21 Crosby Grocery','21 Crosby Street','(484 ) 966-2020','SALESDUDDETTE',10,'3650',48,49.3,1.02709,'AC',10,'Canada','CA','10003',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (77801,'Dizzy Izzy','185 VARICK STREET','(484 ) 989-2422','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'MR',10,'Canada','CA','10014',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9759,'#Gateway News','180 Water Street','(551 ) 655-2754','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'AC',10,'Canada','CA','10004',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9882,'#Head Line Coffee Bar','250 Broadway','(484 ) 608-0393','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'AC',10,'Canada','CA','10007',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9892,'#Martin News','55 Water Street','(484 ) 747-1944','SALESDUDDETTE',10,'3650',24,24.65,1.02709,'AC',10,'Canada','CA','10004',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (61725,'#Sheepshead Bay Fruit & Veg','1717 AVENUE Z','(305 ) 975-7095','SALESDUDE2',12,'3650',72,73.95,1.02709,'B2',12,'Orange County','CA','11228',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9859,'#Natural Frontier','1102 Cortelyou Road','(305 ) 284-3593','SALESDUDE2',12,'3650',48,49.3,1.02709,'AC',12,'Orange County','CA','11218',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99245,'#Natural Frontier','1201 Jackson Avenue','(305 ) 937-9399','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'LONG ISLAND CITY','CA','11101',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99460,'#Rachel''s Deli','82 Nassau Avenue','(305 ) 349-9600','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'Orange County','CA','11222',33,'ALP SOME ITEM','8.33Z','24','3/29/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99612,'#Gulf Service Station','1938 Coney Island Avenue','(305 ) 375-5002','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'Orange County','CA','11230',33,'ALP SOME ITEM','8.33Z','24','3/29/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99469,'#Biedroka Polish & Int''l Foods','854 Manhattan Avenue','(305 ) 576-3133','SALESDUDE2',12,'3650',-48,-49.3,1.02709,'AC',12,'GREENPOINT','CA','11222',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99648,'#C-Town','953 Manhattan Avenue','(305 ) 349-3000','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'Orange County','CA','11222',33,'ALP SOME ITEM','8.33Z','24','3/29/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99469,'#Biedroka Polish & Int''l Foods','854 Manhattan Avenue','(305 ) 576-3133','SALESDUDE2',12,'3650',48,49.3,1.02709,'AC',12,'GREENPOINT','CA','11222',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9995,'#A Star Deli & Grocery','102 CORTELYOU ROAD','(646 ) 894-4865','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'Orange County','CA','11218',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (73537,'#Fine Fare','406 AVENUE P','(305 ) 998-2710','SALESDUDE2',12,'3650',24,24.65,1.02709,'RR',12,'Orange County','CA','11223',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99144,'#Pioneer St Supermaket','322 Van Brunt Street','(305 ) 624-1779','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'Orange County','CA','11231',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9855,'#Catskill Bagel','1416 Cortelyou Road','(305 ) 282-5049','SALESDUDE2',12,'3650',24,24.65,1.02709,'AC',12,'Orange County','CA','11226',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9859,'#Natural Frontier','1102 Cortelyou Road','(305 ) 284-3593','SALESDUDE2',12,'3650',48,49.3,1.02709,'AC',12,'Orange County','CA','11218',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9921,'#n.s.a Supermarket','720 Washington Avneue','(305 ) 638-7500','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99194,'#Cambridge Deli','1001 Fulton Street','(347 ) 382-7300','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99491,'#Met Foods','991 Fulton Street','(305 ) 636-1620','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9920,'#Met Food','632 Vanderbuilt Avenue','(305 ) 783-1887','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9919,'#Old Brookly Bagel','645 Vanderbuilt Avenue','(305 ) 622-6227','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9955,'#Yaffa Deli','907 Fulton Street','(305 ) 389-8630','SALESDUDE3',16,'3650',-48,-49.3,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9955,'#Yaffa Deli','907 Fulton Street','(305 ) 389-8630','SALESDUDE3',16,'3650',48,49.3,1.02709,'AC',16,'Orange County','CA','11238',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99135,'Pioneer Supermarket','191 Parkside Avenue','(305 ) 941-2777','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11226',33,'ALP SOME ITEM','8.33Z','24','3/15/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99149,'#Rosa''s Discount Store','1323 Fulton Street','(305 ) 623-8880','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11216',33,'ALP SOME ITEM','8.33Z','24','3/27/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99439,'#Melanie''s Restaurant','1285 Fulton Street','(305 ) 399-2960','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11216',33,'ALP SOME ITEM','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9972,'#Superior Market','81 Kingston Avenue','(305 ) 735-4959','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11213',33,'ALP SOME ITEM','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99411,'#Larson''s and Hennings','114 Montague Street','(305 ) 875-6272','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11201',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99105,'#Fulton Plaza Supermarket','1741 Fulton Street','(305 ) 774-5645','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11233',33,'ALP SOME ITEM','8.33Z','24','3/29/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9906,'#Associated Supermarket','905 Franklin Avenue','(305 ) 756-3069','SALESDUDE3',16,'3650',0,0,0,'AC',16,'Orange County','CA','11225',33,'ALP SOME ITEM','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99148,'Fulton Gourmet Deli','1339 Fulton Street','(305 ) 700-3900','SALESDUDE3',16,'3650',24,24.65,1.02709,'AC',16,'Orange County','CA','11216',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99468,'#Yasmihs Deli','18 Onderdonk Avenue','(305 ) 386-0888','Some Guy',17,'3650',48,49.3,1.02709,'AC',17,'RIDGEWOOD','CA','11385',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99489,'#Associated Marketplace','44-07 Greenpoint Avenue','(305 ) 361-5638','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'SUNCASIDE','CA','11104',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99500,'#Super Associated Supermarket','155-15 Aguilar Avenue','(305 ) 380-1234','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'FLUSHING','CA','11367',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99215,'#Vitality & Health Org Market','46-03 Broadway','(305 ) 777-8477','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'Astoria','CA','11103',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (664329,'#*P & O Supermarket','40-08 Rockland Drive BLVD.','(305 ) 392-3178','Some Guy',17,'3650',24,24.65,1.02709,'QS',17,'WOODSIDE','CA','11377',33,'ALP SOME ITEM','8.33Z','24','3/10/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99132,'#New Astoria Market','21-01 31st Street','(305 ) 274-7829','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'Astoria','CA','11105',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (83932,'#Food Dynasty Supermarket','63-76 WOODHAVEN BOULEVARD','(305 ) 326-8800','Some Guy',17,'3650',24,24.65,1.02709,'RR',17,'REGO PARK','CA','11374',33,'ALP SOME ITEM','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (82238,'Food Cellar CompaCA','4-76 47TH ROAD','(305 ) 606-9786','Some Guy',17,'3650',24,24.65,1.02709,'MR',17,'LONG ISLAND CITY','CA','11101',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99645,'#Berry Fresh Farms','43-26 Ditmars Boulevard','(305 ) 204-1662','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'ASTORIA','CA','11105',33,'ALP SOME ITEM','8.33Z','24','3/27/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (314317,'#The Dairy Mill','137-20 CROSS BAY BOULEVARD','(305 ) 845-0117','Some Guy',17,'3650',24,24.65,1.02709,'QS',17,'OZONE PARK','CA','11417',33,'ALP SOME ITEM','8.33Z','24','3/27/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99608,'Keyfood Supermarket','61-50 Springfield Boulevard','(305 ) 898-0044','Some Guy',17,'3650',48,49.3,1.02709,'AC',17,'BAYSIDE','CA','11364',33,'ALP SOME ITEM','8.33Z','24','3/27/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99330,'Farm Boy Country Market','127-20 Rockaway Boulevard','(305 ) 738-3750','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'SOUTH OZONE PARK','CA','11420',33,'ALP SOME ITEM','8.33Z','24','3/21/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99367,'#Milk Farm','88-11 Liberty Avenue','(305 ) 843-3471','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'JAMAICA','CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99380,'#Pioneer','70-63 Parsons Boulevard','(305 ) 591-1234','Some Guy',17,'3650',48,49.3,1.02709,'AC',17,'Rockland Drive','CA','11365',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99608,'Keyfood Supermarket','61-50 Springfield Boulevard','(305 ) 898-0044','Some Guy',17,'3650',24,24.65,1.02709,'AC',17,'BAYSIDE','CA','11364',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9842,'#Organic Forever','2053 Frederick Douglass Blvd','(484 ) 666-3012','SALESDUDE4',18,'3650',24,24.65,1.02709,'AC',18,'Canada','CA','10026',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99172,'#Fine Fare Supermarket','136 Lenox Avenue','(484 ) 228-9951','SALESDUDE4',18,'3650',24,24.65,1.02709,'AC',18,'Canada','CA','10026',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (660392,'*Subs','1213 AMSTERDAM AVENUE','(484 ) 864-2720','SALESDUDE4',18,'3650',24,24.65,1.02709,'HV',18,'Canada','CA','10027',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99217,'#University Deli','603 West 168th Street','(484 ) 568-3838','SALESDUDE4',18,'3650',24,24.65,1.02709,'AC',18,'Canada','CA','10032',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99200,'#Wild Olive','10 East 125th Street','(484 ) 369-2665','SALESDUDE4',18,'3650',24,24.65,1.02709,'AC',18,'Canada','CA','10035',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99200,'#Wild Olive','10 East 125th Street','(484 ) 369-2665','SALESDUDE4',18,'3650',-24,-24.65,1.02709,'AC',18,'Canada','CA','10035',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (54681,'#Penn Plaza Gourmet Deli Corp','367 7TH AVENUE','(484 ) 268-1141','SALESDUDE5',19,'3650',24,24.65,1.02709,'FV',19,'Canada','CA','10001',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99507,'#Adinahs Farm','262 East 2nd Street','(484 ) 677-6906','SALESDUDE5',19,'3650',24,24.65,1.02709,'AC',19,'Canada','CA','10009',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9910,'#Tri-Rite Deli','353 6th Avenue','(484 ) 924-6865','SALESDUDE5',19,'3650',48,49.3,1.02709,'AC',19,'Canada','CA','10011',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99506,'#Fresno Gourmet Deli','27 Avenue C','(484 ) 353-1110','SALESDUDE5',19,'3650',48,49.3,1.02709,'AC',19,'Canada','CA','10009',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (81802,'Brothers Deli','542 EAST 14TH STREET','(484 ) 473-8308','SALESDUDE5',19,'3650',24,24.65,1.02709,'MR',19,'Canada','CA','10003',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9903,'#Namu','252 3rd Avenue','(484 ) 228-9406','SALESDUDE5',19,'3650',48,49.3,1.02709,'AC',19,'Canada','CA','10010',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9822,'#Elm Health Drugs','56 7th Avenue','(484 ) 255-6100','SALESDUDE5',19,'3650',24,24.65,1.02709,'AC',19,'Canada','CA','10011',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (661318,'Garden Of Eden','7 EAST 14TH STREET','(484 ) 255-4200','SALESDUDE5',19,'3650',24,24.65,1.02709,'GZ',19,'Canada','CA','10003-3115',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (52017,'#Cafe Charlie','9 E.40TH STREET','(484 ) 725-1420','SALESDUDE5',19,'3650',24,24.65,1.02709,'FV',19,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99205,'#Campus Eatery','31 West 4th Street','(305 ) 475-3600','SALESDUDE5',19,'3650',0,0,0,'AC',19,'Canada','CA','10012',33,'ALP SOME ITEM','8.33Z','24','3/30/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (667365,'#Bingo Deli','605 2ND AVENUE','(484 ) 725-8005','SALESDUDE5',19,'3650',24,24.65,1.02709,'FV',19,'Canada','CA','10016',33,'ALP SOME ITEM','8.33Z','24','3/17/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99247,'*#Health Food Gourmet','2337-2339 1st Avenue','(484 ) 423-1035','SALESDUDE6',20,'3650',24,24.65,1.02709,'AC',20,'Canada','CA','10035',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9840,'#Elm Health','1695 1st Avenue','(484 ) 348-8500','SALESDUDE6',20,'3650',24,24.65,1.02709,'AC',20,'Canada','CA','10128',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62701,'#Straight From The Market','408 EAST 92ND STREET','(484 ) 876-8329','SALESDUDE6',20,'3650',48,49.3,1.02709,'MR',20,'Canada','CA','10128',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62701,'#Straight From The Market','408 EAST 92ND STREET','(484 ) 876-8329','SALESDUDE6',20,'3650',24,24.65,1.02709,'MR',20,'Canada','CA','10128',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (907177,'asdf.',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',12,9.36,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/10/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-48,-37.44,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (960600,'NewMAn, L.',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',24,18.72,0.78,'DV',58,'JAMAICA','CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',48,37.44,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (907177,'asdf.',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',12,9.36,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',48,37.44,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',48,37.44,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (866022,'MAnners, J.',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (966005,'MAnners,L',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',24,18.72,0.78,'DV',58,'JAMAICA','CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (866022,'MAnners, J.',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-24,-18.72,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/23/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (966005,'MAnners,L',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',24,18.72,0.78,'DV',58,'JAMAICA','CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/17/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-24,-18.72,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',48,37.44,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/29/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-144,-112.32,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/27/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-144,-112.32,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',48,37.44,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/23/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (966005,'MAnners,L',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-48,-37.44,0.78,'DV',58,'JAMAICA','CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (966012,'CURTIS,DANIEL',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',120,93.6,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/26/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Paris, Hilton',null,'( DO ) NOT NULL','COMPANY DRIVERS',58,'3650',-24,-18.72,0.78,'DV',58,null,'CA','11433',33,'ALP SOME ITEM','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (16,'SAMPLES FROM COMPANY DEPOT','95-04 149TH STREET','(000 ) 0000000','DONATION ACCOUNTS',62,'3650',24,18.6,0.775,'DA',62,'JAMAICA','CA','11435',33,'ALP SOME ITEM','8.33Z','24','3/3/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (16,'SAMPLES FROM COMPANY DEPOT','95-04 149TH STREET','(000 ) 0000000','DONATION ACCOUNTS',62,'3650',24,18.6,0.775,'DA',62,'JAMAICA','CA','11435',33,'ALP SOME ITEM','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (16,'SAMPLES FROM COMPANY DEPOT','95-04 149TH STREET','(000 ) 0000000','DONATION ACCOUNTS',62,'3650',24,18.6,0.775,'DA',62,'JAMAICA','CA','11435',33,'ALP SOME ITEM','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (16,'SAMPLES FROM COMPANY DEPOT','95-04 149TH STREET','(000 ) 0000000','DONATION ACCOUNTS',62,'3650',24,18.6,0.775,'DA',62,'JAMAICA','CA','11435',33,'ALP SOME ITEM','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (16,'SAMPLES FROM COMPANY DEPOT','95-04 149TH STREET','(000 ) 0000000','DONATION ACCOUNTS',62,'3650',24,18.6,0.775,'DA',62,'JAMAICA','CA','11435',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (16,'SAMPLES FROM COMPANY DEPOT','95-04 149TH STREET','(000 ) 0000000','DONATION ACCOUNTS',62,'3650',24,18.6,0.775,'DA',62,'JAMAICA','CA','11435',33,'ALP SOME ITEM','8.33Z','24','3/16/2012',20);

Open in new window

This is the data for ARIEL_ALL_COMMISSIONS

  CREATE TABLE ARIEL_ALL_COMMISSIONS 
   (	"PROD_NUM" VARCHAR2(10 BYTE), 
	"DESCRIPTION" VARCHAR2(50 BYTE), 
	"S_SIZE" VARCHAR2(10 BYTE), 
	"GRISTEDES" NUMBER(20,6), 
	"BEST_YET" NUMBER(20,6), 
	"STOP_AND_SHOP" NUMBER(20,6), 
	"AOM" NUMBER(20,6), 
	"KMART" NUMBER(20,6), 
	"CLASS" VARCHAR2(10 BYTE), 
	"REBATE" NUMBER(20,6), 
	"SAMPLE_REBATE" NUMBER(20,6)
   ) 
Insert into ARIEL_ALL_COMMISSIONS (PROD_NUM,DESCRIPTION,S_SIZE,GRISTEDES,BEST_YET,STOP_AND_SHOP,AOM,KMART,CLASS,REBATE,SAMPLE_REBATE) values ('3650','Alpina Renew Original','8.33 oz',1,1,1,1,0,'NON POLAR',2,18.6);

Open in new window

gnivkor

ASKER

This is where I am with the query

SELECT 
ARIEL_ONE_WEEK_SALES.PROD_NUM
, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION
, ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME
, Sum(case when CUST_NUM<>16 then ceil(UNITS/PD_UNTCAS) end) AS DELIVERED_CASES
, case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases
, ariel_all_commissions.rebate
, Sum(case when CUST_NUM<>16 then ceil(UNITS/PD_UNTCAS) end)*rebate as sold_cases_rebate
, nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate as rebate_for_samples
, Sum(case when CUST_NUM<>16 then ceil(UNITS/PD_UNTCAS) end)*rebate + nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) AS TOTAL_REBATE
, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE
FROM ARIEL_ONE_WEEK_SALES 
INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM
, ARIEL_ONE_WEEK_SALES.PM_PRDESC
, ARIEL_ONE_WEEK_SALES.PM_VOLUME
, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE
, ARIEL_ALL_COMMISSIONS.Rebate
, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE
, case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end

Open in new window

and this is what the correct output should look like (as it is in access which calculates correctly

http://img24.imageshack.us/img24/2473/76845801.png

Sean Stuber

can you post text results of what the data above should return?

gnivkor

ASKER

PROD_NUM = 3650
DESCRIPTION = ALP SOME ITEM
VOLUME = 8.33Z
DELIVERED_CASES = 126
SAMPLE_CASES = 6
REBATE = 2
SOLD_CASES_REBATE = 252
REBATE_FOR_SAMPLES = 111.6
TOTAL_REBATE 363.6

for PM_BRAND_CODE = 20

Sean Stuber

DELIVERED_CASES = 126 --- how does come about?

if I just sum the units/pd_untcas I get 127

if I use CEIL(units / pd_untcas) I get 128

if I use FLOOR(units / pd_untcas) I get 126

so, FLOOR seems correct, but none of your versions of the query indicate that

also, what is the grouping supposed to be?
I "think" it should be one row for each unique set of these 4 columns, is this correct?

ariel_one_week_sales.prod_num,
ariel_one_week_sales.pm_prdesc,
ariel_one_week_sales.pm_volume
ariel_one_week_sales.pm_brand_code

Sean Stuber

given the data above, this produces the results you requested in http:#a37827629

SELECT ariel_one_week_sales.prod_num,
ariel_one_week_sales.pm_prdesc AS description,
ariel_one_week_sales.pm_volume AS volume,
SUM(CASE WHEN cust_num <> 16 THEN FLOOR(units / pd_untcas) END) AS delivered_cases,
SUM(CASE WHEN cust_num = 16 THEN units / pd_untcas END) AS sample_cases,
MAX(ariel_all_commissions.rebate) rebate,
SUM(CASE WHEN cust_num <> 16 THEN FLOOR(units / pd_untcas) * rebate END)
AS sold_cases_rebate,
SUM(CASE WHEN cust_num = 16 THEN NVL(units / pd_untcas, 0) * sample_rebate END)
AS rebate_for_samples,
SUM(CASE WHEN cust_num <> 16 THEN FLOOR(units / pd_untcas) * rebate END)
+ SUM(CASE WHEN cust_num = 16 THEN NVL(units / pd_untcas, 0) END * sample_rebate)
AS total_rebate,
ariel_one_week_sales.pm_brand_code
FROM ariel_one_week_sales
INNER JOIN
ariel_all_commissions
ON ariel_one_week_sales.prod_num = ariel_all_commissions.prod_num
GROUP BY ariel_one_week_sales.prod_num,
ariel_one_week_sales.pm_prdesc,
ariel_one_week_sales.pm_volume,
ariel_one_week_sales.pm_brand_code

gnivkor

ASKER

the grouping is fine, and id say that query works 90%

on some other prod_num, the total_rebate is returned as a null value, and in some fields of delivered cases the value is off by either 1 or 2 (under) never over

after doing some research on the data. if found out that if rebates for sample has a null value, then total_rebate has a null value as well

Sean Stuber

post sample data with expected results

Sean Stuber

also, for delivered_cases (or any other calculated value)

please explain how the results are achieved. Your examples show CEIL, but as shown above, CEIL produces the wrong results for the data you provided but FLOOR works.

if you now have other data where FLOOR does not work, please explain how those results are supposed to be determined.
I've tried ROUND as well, perhaps you need TRUNC. TRUNC works for the data and results above, but maybe not for everything

gnivkor

ASKER

Prod_num = 93650
Descript = free alp sm/or
volume = 8.33z
delivered_cases =32
sample_cases = 0
rebate = 18.6
sold_cases_rebate = 595.2
rebate_for_samples = 0
total_rebate = 595.2
pm_brand_code = 20

is the expected for prod_num 93650

code for both tables

REM INSERTING into ARIEL_ONE_WEEK_SALES
SET DEFINE OFF;
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (314945,'Del Monico Gourmet','55 EAST 59TH STREET','(484 ) 751-5559','NICK KIM',2,'93650',24,0,0,'MR',2,'Los Angeles','CA','10022',33,'FREE ALP SM/ORG','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62705,'#*Gourmet Park','119 EAST 60TH STREET','(484 ) 935-8500','NICK KIM',2,'93650',-24,0,0,'MR',2,'Los Angeles','CA','10022',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62705,'#*Gourmet Park','119 EAST 60TH STREET','(484 ) 935-8500','NICK KIM',2,'93650',24,0,0,'MR',2,'Los Angeles','CA','10022',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9740,'*#99 Cents Dream','2 West 46th Street','(484 ) 840-1995','SAID ALY',8,'93650',24,0,0,'AC',8,'Los Angeles','CA','10036',33,'FREE ALP SM/ORG','8.33Z','24','3/26/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9803,'#Joe''s C','245 West 72nd Street','(484 ) 579-9028','SAID ALY',8,'93650',24,0,0,'AC',8,'Los Angeles','CA','10023',33,'FREE ALP SM/ORG','8.33Z','24','3/16/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99477,'Martin News (Atrium)','16 Wall Street','(484 ) 480-2986','YAIR MERA',10,'93650',24,0,0,'AC',10,'Los Angeles','CA','10004',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (81505,'Martin News','200 VESSEY STREET','(484 ) 640-0443','YAIR MERA',10,'93650',24,0,0,'MR',10,'Los Angeles','CA','10285',33,'FREE ALP SM/ORG','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (668897,'#Han''s','645 BROADWAY','(484 ) 473-8596','YAIR MERA',10,'93650',24,0,0,'FV',10,'Los Angeles','CA','10012',33,'FREE ALP SM/ORG','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9859,'#Natural Frontier','1102 Cortelyou Road','(989 ) 284-3593','EDUARDO PILNIK',12,'93650',24,0,0,'AC',12,'BROOKLYN','CA','11218',33,'FREE ALP SM/ORG','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9859,'#Natural Frontier','1102 Cortelyou Road','(989 ) 284-3593','EDUARDO PILNIK',12,'93650',24,0,0,'AC',12,'BROOKLYN','CA','11218',33,'FREE ALP SM/ORG','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99144,'#Pioneer St Supermaket','322 Van Brunt Street','(989 ) 624-1779','EDUARDO PILNIK',12,'93650',24,0,0,'AC',12,'BROOKLYN','CA','11231',33,'FREE ALP SM/ORG','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9955,'#Yaffa Deli','907 Fulton Street','(989 ) 389-8630','JAMES FOXWORTH',16,'93650',24,0,0,'AC',16,'BROOKLYN','CA','11238',33,'FREE ALP SM/ORG','8.33Z','24','3/13/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9922,'#Cepeda Grocery','746 Washington Avneue','(989 ) 638-6817','JAMES FOXWORTH',16,'93650',24,0,0,'AC',16,'BROOKLYN','CA','11238',33,'FREE ALP SM/ORG','8.33Z','24','3/8/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99491,'#Met Foods','991 Fulton Street','(989 ) 636-1620','JAMES FOXWORTH',16,'93650',24,0,0,'AC',16,'BROOKLYN','CA','11238',33,'FREE ALP SM/ORG','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99489,'#Associated Marketplace','44-07 Greenpoint Avenue','(989 ) 361-5638','ERIC BELFERDER',17,'93650',24,0,0,'AC',17,'SUNCASIDE','CA','11104',33,'FREE ALP SM/ORG','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99507,'#Adinahs Farm','262 East 2nd Street','(484 ) 677-6906','NICK TEJEDA',19,'93650',24,0,0,'AC',19,'Los Angeles','CA','10009',33,'FREE ALP SM/ORG','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (81802,'Brothers Deli','542 EAST 14TH STREET','(484 ) 473-8308','NICK TEJEDA',19,'93650',24,0,0,'MR',19,'Los Angeles','CA','10003',33,'FREE ALP SM/ORG','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (661318,'Garden Of Eden','7 EAST 14TH STREET','(484 ) 255-4200','NICK TEJEDA',19,'93650',24,0,0,'GZ',19,'Los Angeles','CA','10003-3115',33,'FREE ALP SM/ORG','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9822,'#Elm Health Drugs','56 7th Avenue','(484 ) 255-6100','NICK TEJEDA',19,'93650',24,0,0,'AC',19,'Los Angeles','CA','10011',33,'FREE ALP SM/ORG','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99155,'#Food For Health','1653 3rd Avenue','(484 ) 369-9202','KHAN TANAWAR',20,'93650',24,0,0,'AC',20,'Los Angeles','CA','10128',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (37808,'#You Dont Know Nothing Produce','1593 YORK AVENUE','(484 ) 535-4261','KHAN TANAWAR',20,'93650',24,0,0,'MR',20,'Los Angeles','CA','10028',33,'FREE ALP SM/ORG','8.33Z','24','3/10/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9840,'#Elm Health','1695 1st Avenue','(484 ) 348-8500','KHAN TANAWAR',20,'93650',24,0,0,'AC',20,'Los Angeles','CA','10128',33,'FREE ALP SM/ORG','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99156,'#Health Nuts','1208 2nd Avenue','(484 ) 593-0116','KHAN TANAWAR',20,'93650',24,0,0,'AC',20,'Los Angeles','CA','10065',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99427,'#Delissimo','39 East 60th Street','(484 ) 593-2333','KHAN TANAWAR',20,'93650',24,0,0,'AC',20,'Los Angeles','CA','10022',33,'FREE ALP SM/ORG','8.33Z','24','3/26/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (99164,'#Natural Frontier Market','1424 3rd Avenue','(484 ) 794-0922','KHAN TANAWAR',20,'93650',24,0,0,'AC',20,'Los Angeles','CA','10028',33,'FREE ALP SM/ORG','8.33Z','24','3/14/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (304171,'Madison Gourmet','766 MADISON AVENUE','(484 ) 288-8276','KHAN TANAWAR',20,'93650',24,0,0,'FV',20,'Los Angeles','CA','10021',33,'FREE ALP SM/ORG','8.33Z','24','3/19/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (62701,'#Straight From The Market','408 EAST 92ND STREET','(484 ) 876-8329','KHAN TANAWAR',20,'93650',24,0,0,'MR',20,'Los Angeles','CA','10128',33,'FREE ALP SM/ORG','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (9714,'#River East Deli','1320 York Avenue','(484 ) 861-3655','KHAN TANAWAR',20,'93650',24,0,0,'AC',20,'Los Angeles','CA','10001',33,'FREE ALP SM/ORG','8.33Z','24','3/7/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Dude2, Dude5',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/20/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (866022,'Dude1, J.',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (866020,'Dude3, V.',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/9/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (907177,'Dude6LEONE, J.',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (866022,'Dude1, J.',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',-12,-9.36,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/12/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Dude2, Dude5',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',98,76.44,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/22/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Dude2, Dude5',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',-98,-76.44,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/21/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (960600,'Dude4, L.',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',24,18.72,0.78,'DV',58,'JAMAICA','CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/6/2012',20);
Insert into ARIEL_ONE_WEEK_SALES (CUST_NUM,CUSTOMER_NAME,ADDRESS,PHONE,SALESMAN,SLSMAN_NUM,PROD_NUM,UNITS,TH_DOLRS,TH_PRICE,CM_CHNID,ID_FLDNUM,CITY,STATE,ZIP,PD_BRANCH,PM_PRDESC,PM_VOLUME,PD_UNTCAS,S_DATE,PM_BRAND_CODE) values (925453,'Dude2, Dude5',null,'( DO ) NOT NULL','BEYER DRIVERS',58,'93650',24,18.72,0.78,'DV',58,null,'CA','11433',33,'FREE ALP SM/ORG','8.33Z','24','3/6/2012',20);

Open in new window

and for ariel_all_commissions

REM INSERTING into ARIEL_ALL_COMMISSIONS
SET DEFINE OFF;
Insert into ARIEL_ALL_COMMISSIONS (PROD_NUM,DESCRIPTION,S_SIZE,GRISTEDES,BEST_YET,STOP_AND_SHOP,AOM,KMART,CLASS,REBATE,SAMPLE_REBATE) values ('93650','FREE ALP SM/ORG','8.33 oz',0,0,0,0,0,'FREE',18.6,null);

Open in new window

gnivkor

ASKER

i believe your calculations with the delivered cases is correct, i am going over it manually and will post results shortly

Sean Stuber

just use NVL to 0 around those fields

SELECT ariel_one_week_sales.prod_num,
ariel_one_week_sales.pm_prdesc AS description,
ariel_one_week_sales.pm_volume AS volume,
SUM(CASE WHEN cust_num <> 16 THEN TRUNC(units / pd_untcas) END) AS delivered_cases,
NVL(SUM(CASE WHEN cust_num = 16 THEN units / pd_untcas END), 0) AS sample_cases,
MAX(ariel_all_commissions.rebate) rebate,
SUM(CASE WHEN cust_num <> 16 THEN TRUNC(units / pd_untcas) * rebate END)
AS sold_cases_rebate,
NVL(SUM(CASE WHEN cust_num = 16 THEN NVL(units / pd_untcas, 0) * sample_rebate END), 0)
AS rebate_for_samples,
SUM(CASE WHEN cust_num <> 16 THEN TRUNC(units / pd_untcas) * rebate END)
+ NVL(SUM(CASE WHEN cust_num = 16 THEN NVL(units / pd_untcas, 0) END * sample_rebate), 0)
AS total_rebate,
ariel_one_week_sales.pm_brand_code
FROM ariel_one_week_sales
INNER JOIN
ariel_all_commissions
ON ariel_one_week_sales.prod_num = ariel_all_commissions.prod_num
GROUP BY ariel_one_week_sales.prod_num,
ariel_one_week_sales.pm_prdesc,
ariel_one_week_sales.pm_volume,
ariel_one_week_sales.pm_brand_code

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gnivkor

ASKER

Sir, you have been helpful beyond belief.

I appreciate the time you have spent with this matter. Everything seems perfect.