| Author |
 Topic  |
|
|
sqlobsessed
Starting Member
USA
8 Posts |
 Posted - 06/09/2012 : 14:09:18
|
This query is for a revenue report. It creates 6 tables and selects quantity and revenue from each of these tables. The problem is that the select stmt is pulling a sku multiple times instead of counting it once. I tried taking out all sums and took inner off of the joins. Is there anything else I could be missing?
create table ##2011DailyBilling
(InventoryItemID int, sku varchar(10),ItemNumber int, Description varchar (50),PubMonth int, PubYear int, Cost smallmoney ,Ship_Month int , Ship_Year int, Quantity int , Revenue smallmoney)
insert into ##2011DailyBilling
SELECT DISTINCT i.InventoryItemID, i.sku, i.ItemNumber, i.Description,MONTH(i.pubstart) as Month, YEAR(i.pubstart) as Year, iup.UnitCost as Cost,MONTH(odh.Created) as Ship_Month,year(odh.Created) as Ship_Year, sum(od.QuantityTransacted) as Quantity, (SUM(od.quantitytransacted) * iup.UnitCost) as Revenue
FROM Inventory i (nolock)
join InventoryUnitPrice iup (nolock) on i.itemnumber=iup.ItemNumber
join dbo.OrderDetail od (nolock) ON od.InventoryItemID = i.InventoryItemID
join dbo.OrderHeader oh (nolock) ON oh.OrderID = od.OrderID
join dbo.Address AS a WITH (nolock) ON oh.ShipToAddressID = a.AddressID
join OrderDetailHistory odh (nolock) on odh.OrderDetailID=od.OrderDetailID and odh.OrderDetailStatusID=5
WHERE oh.OrderTypeID in (16,21,28) and YEAR(i.pubstart) =2011 and MONTH(i.PubStart) in (4,5)
and GETDATE() between iup.StartDate and iup.EndDate and PaymentTypeID <>3 and oh.CustomerSubTypeID<>43
group by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created) ,year(odh.Created)
order by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created)
create table ##2012DailyBilling
(InventoryItemID int, sku varchar(10),ItemNumber int, Description varchar (50),PubMonth int, PubYear int, Cost smallmoney ,Ship_Month int , Ship_Year int, Quantity int , Revenue smallmoney)
insert into ##2012DailyBilling
SELECT DISTINCT i.InventoryItemID, i.sku, i.ItemNumber, i.Description,MONTH(i.pubstart) as Month, YEAR(i.pubstart) as Year, iup.UnitCost as Cost,MONTH(odh.Created) as Ship_Month,year(odh.Created) as Ship_Year, sum(od.QuantityTransacted) as Quantity, (SUM(od.quantitytransacted) * iup.UnitCost) as Revenue
FROM Inventory i (nolock)
join InventoryUnitPrice iup (nolock) on i.itemnumber=iup.ItemNumber
join dbo.OrderDetail od (nolock) ON od.InventoryItemID = i.InventoryItemID
join dbo.OrderHeader oh (nolock) ON oh.OrderID = od.OrderID
join dbo.Address AS a WITH (nolock) ON oh.ShipToAddressID = a.AddressID
join OrderDetailHistory odh (nolock) on odh.OrderDetailID=od.OrderDetailID and odh.OrderDetailStatusID=5
WHERE oh.OrderTypeID in (16,21,28) and YEAR(i.pubstart) =2012 and MONTH(i.PubStart) in (4,5)
and GETDATE() between iup.StartDate and iup.EndDate and PaymentTypeID <>3 and oh.CustomerSubTypeID<>43
group by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created) ,year(odh.Created)
order by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created)
create table ##2011BellBilling
(InventoryItemID int, sku varchar(10),ItemNumber int, Description varchar (50),PubMonth int, PubYear int, Cost smallmoney ,Ship_Month int , Ship_Year int, Quantity int , Revenue smallmoney)
insert into ##2011BellBilling
SELECT DISTINCT i.InventoryItemID, i.sku, i.ItemNumber, i.Description,MONTH(i.pubstart) as Month, YEAR(i.pubstart) as Year, iup.UnitCost as Cost,MONTH(odh.Created) as Ship_Month,year(odh.Created) as Ship_Year, sum(od.QuantityTransacted) as Quantity, (SUM(od.quantitytransacted) * iup.UnitCost) as Revenue
FROM Inventory i (nolock)
join InventoryUnitPrice iup (nolock) on i.itemnumber=iup.ItemNumber
join dbo.OrderDetail od (nolock) ON od.InventoryItemID = i.InventoryItemID
join dbo.OrderHeader oh (nolock) ON oh.OrderID = od.OrderID
join dbo.Address AS a WITH (nolock) ON oh.ShipToAddressID = a.AddressID
join OrderDetailHistory odh (nolock) on odh.OrderDetailID=od.OrderDetailID and odh.OrderDetailStatusID=5
WHERE oh.OrderTypeID in (16,21,28,24) and YEAR(i.pubstart) =2011 and MONTH(i.PubStart) in (4,5)
and GETDATE() between iup.StartDate and iup.EndDate and oh.CustomerSubTypeID=43
group by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created) ,year(odh.Created)
order by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created)
create table ##2012BellBilling
(InventoryItemID int, sku varchar(10),ItemNumber int, Description varchar (50),PubMonth int, PubYear int, Cost smallmoney ,Ship_Month int , Ship_Year int, Quantity int , Revenue smallmoney)
insert into ##2012BellBilling
SELECT DISTINCT i.InventoryItemID, i.sku, i.ItemNumber, i.Description,MONTH(i.pubstart) as Month, YEAR(i.pubstart) as Year, iup.UnitCost as Cost,MONTH(odh.Created) as Ship_Month,year(odh.Created) as Ship_Year, sum(od.QuantityTransacted) as Quantity, (SUM(od.quantitytransacted) * iup.UnitCost) as Revenue
FROM Inventory i (nolock)
join InventoryUnitPrice iup (nolock) on i.itemnumber=iup.ItemNumber
join dbo.OrderDetail od (nolock) ON od.InventoryItemID = i.InventoryItemID
join dbo.OrderHeader oh (nolock) ON oh.OrderID = od.OrderID
join dbo.Address AS a WITH (nolock) ON oh.ShipToAddressID = a.AddressID
join OrderDetailHistory odh (nolock) on odh.OrderDetailID=od.OrderDetailID and odh.OrderDetailStatusID=5
WHERE oh.OrderTypeID in (16,21,28,24) and YEAR(i.pubstart) =2012 and MONTH(i.PubStart) in (4,5)
and GETDATE() between iup.StartDate and iup.EndDate and oh.CustomerSubTypeID=43
group by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created) ,year(odh.Created)
order by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created)
create table ##2011CreditBilling
(InventoryItemID int, sku varchar(10),ItemNumber int, Description varchar (50),PubMonth int, PubYear int, Cost smallmoney ,Ship_Month int , Ship_Year int, Quantity int , Revenue smallmoney)
insert into ##2011CreditBilling
SELECT DISTINCT i.InventoryItemID, i.sku, i.ItemNumber, i.Description,MONTH(i.pubstart) as Month, YEAR(i.pubstart) as Year, iup.UnitCost as Cost,MONTH(odh.Created) as Ship_Month,year(odh.Created) as Ship_Year, sum(od.QuantityTransacted) as Quantity, (SUM(od.quantitytransacted) * iup.UnitCost) as Revenue
FROM Inventory i (nolock)
join InventoryUnitPrice iup (nolock) on i.itemnumber=iup.ItemNumber
join dbo.OrderDetail od (nolock) ON od.InventoryItemID = i.InventoryItemID
join dbo.OrderHeader oh (nolock) ON oh.OrderID = od.OrderID
join dbo.Address AS a WITH (nolock) ON oh.ShipToAddressID = a.AddressID
join OrderDetailHistory odh (nolock) on odh.OrderDetailID=od.OrderDetailID and odh.OrderDetailStatusID=5
WHERE oh.OrderTypeID in (16,21,28,24) and YEAR(i.pubstart) =2011 and MONTH(i.PubStart) in (4,5)
and GETDATE() between iup.StartDate and iup.EndDate and PaymentTypeID =3 and oh.CustomerSubTypeID<>43
group by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created) ,year(odh.Created)
order by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created)
create table ##2012CreditBilling
(InventoryItemID int, sku varchar(10),ItemNumber int, Description varchar (50),PubMonth int, PubYear int, Cost smallmoney ,Ship_Month int , Ship_Year int, Quantity int , Revenue smallmoney)
insert into ##2012CreditBilling
SELECT DISTINCT i.InventoryItemID, i.sku, i.ItemNumber, i.Description,MONTH(i.pubstart) as Month, YEAR(i.pubstart) as Year, iup.UnitCost as Cost,MONTH(odh.Created) as Ship_Month,year(odh.Created) as Ship_Year, sum(od.QuantityTransacted) as Quantity, (SUM(od.quantitytransacted) * iup.UnitCost) as Revenue
FROM Inventory i (nolock)
join InventoryUnitPrice iup (nolock) on i.itemnumber=iup.ItemNumber
join dbo.OrderDetail od (nolock) ON od.InventoryItemID = i.InventoryItemID
join dbo.OrderHeader oh (nolock) ON oh.OrderID = od.OrderID
join dbo.Address AS a WITH (nolock) ON oh.ShipToAddressID = a.AddressID
join OrderDetailHistory odh (nolock) on odh.OrderDetailID=od.OrderDetailID and odh.OrderDetailStatusID=5
WHERE oh.OrderTypeID in (16,21,28,24) and YEAR(i.pubstart) =2012 and MONTH(i.PubStart) in (4,5)
and GETDATE() between iup.StartDate and iup.EndDate and PaymentTypeID =3 and oh.CustomerSubTypeID<>43
group by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created) ,year(odh.Created)
order by i.InventoryItemID, i.sku, i.ItemNumber, i.Description,iup.UnitCost,MONTH(i.pubstart), YEAR(i.pubstart),MONTH(odh.Created)
select
a.sku, a.ItemNumber, a.Description,
isnull(sum(a.quantity),0) as [2012DailyQty],
isnull(sum(a.revenue),0) As [2012_Daily_Revenue],
isnull(sum(b.Quantity),0) as [2011DailyQty],
isnull(sum(b.Revenue),0) As [2011_Daily_Revenue],
isnull((sum(a.quantity)-sum(b.Quantity)),0) as [Delta_Daily_Qty],
isnull((sum(a.revenue)-sum(b.Revenue)),0) as [Delta_Daily_Revenue],
isnull(sum(c.quantity),0) as [2012BellQty],
isnull(sum(c.revenue),0) as [2012_Bell_Revenue],
isnull(sum(g.quantity),0) as [2011BellQty],
isnull(sum(g.revenue),0) as [2011_Bell_Revenue],
isnull((sum(c.quantity)-sum(g.Quantity)),0) as [Delta_Bell_Qty],
isnull((sum(c.revenue)-sum(g.Revenue)),0) as [Delta_Bell_Revenue],
isnull(sum(e.quantity),0)as [2012CreditCardQty],
ISNULL(sum(e.revenue),0) as [2012_CreditCard_Revenue],
isnull(sum(f.quantity),0)as [2011CreditCardQty],
isnull(sum(f.revenue),0) as [2011_CreditCard_Revenue],
isnull((sum(e.quantity)-sum(f.Quantity)),0) as [Delta_CreditCard_Qty],
isnull((sum(e.revenue)-sum(f.Revenue)),0) as [Delta_CreditCard_Revenue]
from ##2012DailyBilling a
join ##2011DailyBilling b on a.sku=b.sku
left join ##2012BellBilling c on a.sku=c.sku
left join ##2012CreditBilling e on a.sku=e.sku
left join ##2011CreditBilling f (nolock) on a.sku=f.sku
left join ##2011bellBilling g (nolock) on a.sku=g.sku
where a.Ship_Month=4
group by a.sku, a.ItemNumber, a.Description
drop table ##2012BellBilling
drop table ##2011BellBilling
drop table ##2011DailyBilling
drop table ##2012DailyBilling
drop table ##2012CreditBilling
drop table ##2011CreditBilling
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 06/09/2012 : 14:25:05
|
instead of joining the tables try using union all like this
select sku, ItemNumber, Description,
sum([2012DailyQty]) as [2012DailyQty],
sum([2012_Daily_Revenue]) as [2012_Daily_Revenue],
sum([2011DailyQty]) as [2011DailyQty],
sum([2011_Daily_Revenue]) as [2011_Daily_Revenue],
...
from
(
select sku, ItemNumber, Description,
sum(quantity) as [2012DailyQty],
sum(revenue) as [2012_Daily_Revenue],
0 AS [2011DailyQty],
0 AS [2011_Daily_Revenue],
0 as [2012BellQty],
0 as [2012_Bell_Revenue],
0 as [2011BellQty],
0 as [2011_Bell_Revenue],
0 as [2012CreditCardQty],
0 as [2012_CreditCard_Revenue],
0 as [2011CreditCardQty],
0 as [2011_CreditCard_Revenue]
from ##2012DailyBilling
group by sku, ItemNumber, Description
union all
select sku, ItemNumber, Description,
0 as [2012DailyQty],
0 as [2012_Daily_Revenue],
sum(quantity) AS [2011DailyQty],
sum(revenue) AS [2011_Daily_Revenue],
0 as [2012BellQty],
0 as [2012_Bell_Revenue],
0 as [2011BellQty],
0 as [2011_Bell_Revenue],
0 as [2012CreditCardQty],
0 as [2012_CreditCard_Revenue],
0 as [2011CreditCardQty],
0 as [2011_CreditCard_Revenue]
from ##2012DailyBilling
group by sku, ItemNumber, Description
union all
...
)t
group by sku, ItemNumber, Description
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
| |
Topic |
|
|
|
Revenue report capturing data multiple times
