select pkrreview.DK_Number,pkrreview.BindingType,pkrrevie w.Category,pkcustomerorder.CONo
from
(select distinct p.category 'Category',t.bindingtype 'BindingType',p.dknumber 'DK_Number',
p.AddDate 'ReviewDate',t.title 'Title',t.titleid 'TitleId',cq.quantityOrdered 'QtyOrdered',
cq.customerOrderNO 'OrderNo',cq.customerOrderDate 'OrderDate',cq.CODKdate,cq.InvoiceNo,
co.billtoid 'BilledId' from title t,dkpub1.dbo.TitleReview p(Nolock),
COQuantityDetails cq(NOLOCK),customerorder co(NOLOCK),InvoicesEntry IE (NOLOCK)
Where t.category=p.category and t.dknumber=p.dknumber and co.coid=cq.coid and
ie.billedto=co.billtoid and cq.titleid=t.titleid and p.AddDate >'2005-12-31'
and p.AddDate < '2007-01-31')as pkrreview
left join
(SELECT count(co.CONo) 'Qty of Order No',t.DKNumber 'DKN0',ct.Description 'DKCategory',bm.Description 'DKBindingType',
co.CONo
FROM CustomerOrder co(NOLOCK),COQuantityDetails cq(NOLOCK), Title t(NOLOCK),
BindingTypeMaster bm(NOLOCK),CategoryMaster ct(NOLOCK),ProcessTypeMaster pt(NOLOCK),
CustomerMaster c1(NOLOCK),CustomerMaster c2(NOLOCK), MonthMaster mm(NOLOCK),
(select distinct p.category 'Category',t.bindingtype 'BindingType',p.dknumber 'DK_Number',
p.AddDate 'ReviewDate',t.title 'Title',t.titleid 'TitleId',cq.quantityOrdered 'QtyOrdered',
cq.customerOrderNO 'OrderNo',cq.customerOrderDate 'OrderDate',cq.CODKdate,cq.InvoiceNo,
co.billtoid 'BilledId'
from title t,dkpub1.dbo.TitleReview p(Nolock),COQuantityDetails cq(NOLOCK),
customerorder co(NOLOCK),InvoicesEntry IE (NOLOCK)
Where t.category=p.category and t.dknumber=p.dknumber and co.coid=cq.coid and
ie.billedto=co.billtoid and cq.titleid=t.titleid and p.AddDate >'2005-12-31' and
p.AddDate < '2007-01-31')pkr
WHERE co.COId = cq.COId AND cq.TitleId = t.TitleId AND t.Category = ct.Code AND t.BindingType = bm.Code
AND co.BillToId = c1.CustomerId AND co.ShipToId = c2.CustomerId AND co.Month = mm.Code
AND ct.code = pkr.category AND t.DKNumber = pkr.DK_Number AND bm.code = pkr.BindingType
AND co.ProcessTypeCode *= pt.Code
group by t.DKNumber,ct.Description,bm.Description, co.CONo)pkcustomerorder
on pkrreview.DK_Number=pkcustomerorder.DKN0