asked on

SQL Query Syntax Cross Join

With previous help from the experts I have this working query. It simply counts the number of orders per year for a customer. Now I have been ask to add a second set of columns that will be the identical set of data only for a second customer. They are looking to see a side by side comparison of two customers for the same period of time. I believe a cross join is going to be in order because customer A may or may not order the same items as customer B and vice versa. So the new query will also have to have the custnmbr field as part of the group. How do you restructure this query to list

Item Numb, Desc, Cust Count #1 1stYear, Cust Count #2 1st Year,Cust Count #2 2nd Year,Cust Count #2 2nd Year

select itemnmbr,itemdesc
, sum(case when YEAR(sh.docdate)=2011 then 1 else 0 end ) as NumberPerYear2011
, sum(case when YEAR(sh.docdate)=2012 then 1 else 0 end ) as NumberPerYear2012
from SOP30300 sl
inner join SOP30200 sh on sl.SOPNUMBE=sh.SOPNUMBE and sl.SOPTYPE=sh.SOPTYPE
where custnmbr='195' and sh.SOPTYPE=3 and YEAR(sh.docdate) in (2011,2012)
group by ITEMNMBR,ITEMDESC
1:

Sharath S

You can try like this.

;with Customer1 as (
select itemnmbr,itemdesc
, sum(case when YEAR(sh.docdate)=2011 then 1 else 0 end ) as NumberPerYear2011
, sum(case when YEAR(sh.docdate)=2012 then 1 else 0 end ) as NumberPerYear2012
from SOP30300 sl
inner join SOP30200 sh on sl.SOPNUMBE=sh.SOPNUMBE and sl.SOPTYPE=sh.SOPTYPE
where custnmbr='195' and sh.SOPTYPE=3 and YEAR(sh.docdate) in (2011,2012)
group by ITEMNMBR,ITEMDESC),

Customer2 as (
select itemnmbr,itemdesc
, sum(case when YEAR(sh.docdate)=2011 then 1 else 0 end ) as NumberPerYear2011
, sum(case when YEAR(sh.docdate)=2012 then 1 else 0 end ) as NumberPerYear2012
from SOP30300 sl
inner join SOP30200 sh on sl.SOPNUMBE=sh.SOPNUMBE and sl.SOPTYPE=sh.SOPTYPE
where custnmbr='196' and sh.SOPTYPE=3 and YEAR(sh.docdate) in (2011,2012)
group by ITEMNMBR,ITEMDESC)
select *
from Customer1 c1
full join Customer1 c2
on c1.itemnmbr = c2.itemnmbr and c1.itemdesc = c2.itemdesc

rwheeler23

ASKER

What does the

;with do? Where can I find documentation on this structure?

This works. Thanks.

Scott Pletcher

One note just for general info:

YEAR(sh.docdate) in (2011,2012)

should be replaced with:

sh.docdate >= '20110101' AND sh.docdate < '20130101'

The second style allows an index with docdate to be used, whereas the first style does not.

When you use a function on any column, SQL can no longer use an index to search for the value.

rwheeler23

ASKER

Thanks for the tip!!!

rwheeler23

ASKER

I just realized those years may not always be consectutive. That is why I used the 'IN()'

Scott Pletcher

For years 2010 and 2010:

((sh.docdate >= '20100101' AND sh.docdate < '20110101') OR
(sh.docdate >= '20120101' AND sh.docdate < '20130101'))

rwheeler23

ASKER

That is true. Good point. The time portion does not cause a problem with this? I remember writing some code and I set the begin date to the same date as the end date and it would not return any results. It was only when there was at least a one day difference would it return anything.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

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

Start Free Trial

rwheeler23

ASKER