Solved

Oracle NVL splitting output on query

Posted on 2012-04-09
20
570 Views
Last Modified: 2012-04-10
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

0
Comment
Question by:gnivkor
  • 11
  • 9
20 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 37824178
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
0
 

Author Comment

by:gnivkor
ID: 37824514
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

0
 

Author Comment

by:gnivkor
ID: 37824532
so in reality, i am trying to do

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

Author Comment

by:gnivkor
ID: 37827410
anyone ? :\
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37827441
can you provide sample input data and expected output.

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

Author Comment

by:gnivkor
ID: 37827468
sure,, gimme a few secs
0
 

Author Comment

by:gnivkor
ID: 37827512
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

0
 

Author Comment

by:gnivkor
ID: 37827523
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37827593
can you post text results of what the data above should return?
0
 

Author Comment

by:gnivkor
ID: 37827629
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 37827715
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37827730
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
0
 

Author Comment

by:gnivkor
ID: 37827872
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37827884
post sample data with expected results
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37827911
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
0
 

Author Comment

by:gnivkor
ID: 37828120
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

0
 

Author Comment

by:gnivkor
ID: 37828129
i believe your calculations with the delivered cases is correct, i am going over it manually and will post results shortly
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37828217
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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37828230
note, I used TRUNC instead of FLOOR in that last one.  With the sample data both produce the same results,  and, for non-negative amounts that is to be expected.
0
 

Author Comment

by:gnivkor
ID: 37828354
Sir, you have been helpful beyond belief.

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now