upc and pos

  • Thread starter Thread starter lee
  • Start date Start date
L

lee

I need to change the below query such that it extract only those data
which have same upc but different pos.
how would i change the below query to satisfy the below condition


select



fact_auth_sales.primary_distributor_id ,



fact_auth_sales.po_no,

fact_auth_sales.upc,

sum(quantity) as total_quantity

from

fact_auth_sales

left outer join
dim_item on
dim_item.catalog_item_id = fact_auth_sales.item_id

left outer join
dim_reporting_hierarchy on
dim_reporting_hierarchy.pk_rpt_hierarchy_id =
dim_item.fk_rpt_hierarchy_id

where
(
fact_auth_sales.auth_date >= cast ('11/1/09' as date) and
fact_auth_sales.auth_date < cast ('11/16/10' as date)
)

and super_department_name ='gd'


and fact_auth_sales.primary_distributor_id IN ('5928')


group by


fact_auth_sales.po_no,
fact_auth_sales.upc,

fact_auth_sales.primary_distributor_id
 
You could try adding the following into the where clause.

AND fact_auth_sales.upc IN
(SELECT fact_auth_sales.upc
FROM
fact_auth_sales

left outer join
dim_item on
dim_item.catalog_item_id = fact_auth_sales.item_id

left outer join
dim_reporting_hierarchy on
dim_reporting_hierarchy.pk_rpt_hierarchy_id =
dim_item.fk_rpt_hierarchy_id

WHERE fact_auth_sales.auth_date >= cast ('11/1/09' as date) and
fact_auth_sales.auth_date < cast ('11/16/10' as date)
and super_department_name ='gd'
WHERE
GROUP BY UPC
HAVING Max(POS) <> Min(POS))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
You could try adding the following into the where clause.

AND fact_auth_sales.upc IN
(SELECT fact_auth_sales.upc
FROM
fact_auth_sales

left outer join
dim_item on
dim_item.catalog_item_id = fact_auth_sales.item_id

left outer join
dim_reporting_hierarchy on
dim_reporting_hierarchy.pk_rpt_hierarchy_id =
dim_item.fk_rpt_hierarchy_id

WHERE fact_auth_sales.auth_date >= cast ('11/1/09' as date) and
fact_auth_sales.auth_date < cast ('11/16/10' as date)
and super_department_name ='gd'
WHERE
GROUP BY UPC
HAVING Max(POS) <> Min(POS))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...



Thanks John. Are there two select statements? Can you please give the
entire Query? Thanks Again
 
The entire query would look something like the following. You may be able to
simplify this a bit, but I could not since I haven't got a good mental picture
of your table structure.

I think that using an aggregate query in an aggregate query may have problems,
but this is what I would try.

SELECT fact_auth_sales.primary_distributor_id ,
fact_auth_sales.po_no,
fact_auth_sales.upc,
sum(quantity) as total_quantity
from
fact_auth_sales
left outer join
dim_item on
dim_item.catalog_item_id = fact_auth_sales.item_id
left outer join
dim_reporting_hierarchy on
dim_reporting_hierarchy.pk_rpt_hierarchy_id =
dim_item.fk_rpt_hierarchy_id
WHERE (
fact_auth_sales.auth_date >= cast ('11/1/09' as date) and
fact_auth_sales.auth_date < cast ('11/16/10' as date)
)
and super_department_name ='gd'
and fact_auth_sales.primary_distributor_id IN ('5928')

AND fact_auth_sales.upc IN
(SELECT fact_auth_sales.upc
FROM
fact_auth_sales
left outer join
dim_item on
dim_item.catalog_item_id = fact_auth_sales.item_id
left outer join
dim_reporting_hierarchy on
dim_reporting_hierarchy.pk_rpt_hierarchy_id =
dim_item.fk_rpt_hierarchy_id
WHERE fact_auth_sales.auth_date >= cast ('11/1/09' as date) and
fact_auth_sales.auth_date < cast ('11/16/10' as date)
and super_department_name ='gd'
WHERE
GROUP BY UPC
HAVING Max(POS) <> Min(POS))

group by
fact_auth_sales.po_no,
fact_auth_sales.upc,
fact_auth_sales.primary_distributor_id

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top