select pkrreview.DK_Nu mber,pkrreview. BindingType,pkr review.Category ,pkcustomerorde r.CONo
from
(select distinct p.category 'Category',t.bi ndingtype 'BindingType',p .dknumber 'DK_Number',
p.AddDate 'ReviewDate',t. title 'Title',t.title id 'TitleId',cq.qu antityOrdered 'QtyOrdered',
cq.customerOrde rNO 'OrderNo',cq.cu stomerOrderDate 'OrderDate',cq. CODKdate,cq.Inv oiceNo,
co.billtoid 'BilledId' from title t,dkpub1.dbo.Ti tleReview p(Nolock),
COQuantityDetai ls cq(NOLOCK),cust omerorder co(NOLOCK),Invo icesEntry IE (NOLOCK)
Where t.category=p.ca tegory and t.dknumber=p.dk number and co.coid=cq.coid and
ie.billedto=co. billtoid and cq.titleid=t.ti tleid 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.Descr iption 'DKCategory',bm .Description 'DKBindingType' ,
co.CONo
FROM CustomerOrder co(NOLOCK),COQu antityDetails cq(NOLOCK), Title t(NOLOCK),
BindingTypeMast er bm(NOLOCK),Cate goryMaster ct(NOLOCK),Proc essTypeMaster pt(NOLOCK),
CustomerMaster c1(NOLOCK),Cust omerMaster c2(NOLOCK), MonthMaster mm(NOLOCK),
(select distinct p.category 'Category',t.bi ndingtype 'BindingType',p .dknumber 'DK_Number',
p.AddDate 'ReviewDate',t. title 'Title',t.title id 'TitleId',cq.qu antityOrdered 'QtyOrdered',
cq.customerOrde rNO 'OrderNo',cq.cu stomerOrderDate 'OrderDate',cq. CODKdate,cq.Inv oiceNo,
co.billtoid 'BilledId'
from title t,dkpub1.dbo.Ti tleReview p(Nolock),COQua ntityDetails cq(NOLOCK),
customerorder co(NOLOCK),Invo icesEntry IE (NOLOCK)
Where t.category=p.ca tegory and t.dknumber=p.dk number and co.coid=cq.coid and
ie.billedto=co. billtoid and cq.titleid=t.ti tleid 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.ProcessTypeC ode *= pt.Code
group by t.DKNumber,ct.D escription,bm.D escription, co.CONo)pkcusto merorder
on pkrreview.DK_Nu mber=pkcustomer order.DKN0
from
(select distinct p.category 'Category',t.bi ndingtype 'BindingType',p .dknumber 'DK_Number',
p.AddDate 'ReviewDate',t. title 'Title',t.title id 'TitleId',cq.qu antityOrdered 'QtyOrdered',
cq.customerOrde rNO 'OrderNo',cq.cu stomerOrderDate 'OrderDate',cq. CODKdate,cq.Inv oiceNo,
co.billtoid 'BilledId' from title t,dkpub1.dbo.Ti tleReview p(Nolock),
COQuantityDetai ls cq(NOLOCK),cust omerorder co(NOLOCK),Invo icesEntry IE (NOLOCK)
Where t.category=p.ca tegory and t.dknumber=p.dk number and co.coid=cq.coid and
ie.billedto=co. billtoid and cq.titleid=t.ti tleid 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.Descr iption 'DKCategory',bm .Description 'DKBindingType' ,
co.CONo
FROM CustomerOrder co(NOLOCK),COQu antityDetails cq(NOLOCK), Title t(NOLOCK),
BindingTypeMast er bm(NOLOCK),Cate goryMaster ct(NOLOCK),Proc essTypeMaster pt(NOLOCK),
CustomerMaster c1(NOLOCK),Cust omerMaster c2(NOLOCK), MonthMaster mm(NOLOCK),
(select distinct p.category 'Category',t.bi ndingtype 'BindingType',p .dknumber 'DK_Number',
p.AddDate 'ReviewDate',t. title 'Title',t.title id 'TitleId',cq.qu antityOrdered 'QtyOrdered',
cq.customerOrde rNO 'OrderNo',cq.cu stomerOrderDate 'OrderDate',cq. CODKdate,cq.Inv oiceNo,
co.billtoid 'BilledId'
from title t,dkpub1.dbo.Ti tleReview p(Nolock),COQua ntityDetails cq(NOLOCK),
customerorder co(NOLOCK),Invo icesEntry IE (NOLOCK)
Where t.category=p.ca tegory and t.dknumber=p.dk number and co.coid=cq.coid and
ie.billedto=co. billtoid and cq.titleid=t.ti tleid 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.ProcessTypeC ode *= pt.Code
group by t.DKNumber,ct.D escription,bm.D escription, co.CONo)pkcusto merorder
on pkrreview.DK_Nu mber=pkcustomer order.DKN0