upc and pos

[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

 

[John Spencer]

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

 

[lee]

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

Thanks John. Are there two select statements? Can you please give the
entire Query? Thanks Again

 

[John Spencer]

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