summing query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

summing query problem

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2012-09-06 : 22:53:50

Here is the problem. I have to total daily sales for a week (though not showing full week here (only mon thru wed)) suming them by day and by store for a particular customer. I have to also show the total sales.

In addition to summing each invoice total by day in the week for each store for a particular customer, however, I have to sum the quantity shipped for bananas and nonbananas (produce) separately. To do this I have to look at each detail record and the productID in the detail record. The productID is linked to the product table which tells me if it isbanana or not isbanana. The quantityshipped for each banana or nonbanana is in each detail record. I also have to sum these to a total.

The problem is that I am not summing each invoicetotal once, but for the number of detail records in that invoice. So if an invoice has 3 detail records I will show the invoicetotal as invoice total * 3.

Here is a sample of what the report would look like.

Store# mon tue wed wktot totqtyBananas totqtyproduce totqyshiped
#23 1.00 2.00 3.00 6.00 20 10 30

#24 2.00 10.00 4.00 16.00 25 15 40

Here is the query I am using. I am passing the invoice date as variables: @mon, @tue, @wed, etc.

SELECT SUM(CASE WHEN invoicedate = @mon THEN dbo.tblOrder.InvoiceTotal else 0 END) AS totmon,
SUM(CASE WHEN invoicedate = @tue THEN dbo.tblOrder.InvoiceTotal else 0 END) AS tottue,
SUM(CASE WHEN invoicedate = @wed THEN dbo.tblOrder.InvoiceTotal else 0 END) AS totwed,

sum( case when invoicedate between @mon and @wed then dbo.tblOrder.InvoiceTotal end) as weektot,
sum(case when invoicedate between @mon and @wed and tblproduct.isbananas = 1 then dbo.tblOrderDetail.QuantityShipped else 0 END) as bananastot ,
sum(case when invoicedate between @mon and @wed and tblproduct.isbananas = 0 then dbo.tblOrderDetail.QuantityShipped else 0 END) as producetot,
dbo.tblCustomerShippingAddress.StoreOrAddressCode, dbo.tblCustomerShippingAddress.Address1
FROM dbo.tblOrder INNER JOIN
dbo.tblOrderDetail ON dbo.tblOrder.OrderID = dbo.tblOrderDetail.OrderID INNER JOIN
dbo.tblCustomerShippingAddress ON dbo.tblOrder.CustomerShippingID = dbo.tblCustomerShippingAddress.CustomerShippingID INNER JOIN
dbo.tblProduct ON dbo.tblOrderDetail.ProductID = dbo.tblProduct.ProductID
WHERE tblCustomerShippingAddress.CustomerID = @CID)
group by StoreOrAddressCode
order by StoreOrAddressCode

Thanks for any help

smh
Yak Posting Veteran

94 Posts

Posted - 2012-09-06 : 23:22:30

I should mention that the tblcustomershippingaddress which has the store number (storeoraddresscode) is a detail table of the tblcustomer so the customerID (the paramter @CID in the query) which is in the tblorder will link to that shipping address table to get the storeID for each order.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-09-07 : 02:23:00

not tested

select
SUM(CASE WHEN invoicedate = @mon THEN dbo.tblOrder.InvoiceTotal else 0 END) AS totmon,
SUM(CASE WHEN invoicedate = @tue THEN dbo.tblOrder.InvoiceTotal else 0 END) AS tottue,
SUM(CASE WHEN invoicedate = @wed THEN dbo.tblOrder.InvoiceTotal else 0 END) AS totwed,
sum( case when invoicedate between @mon and @wed then dbo.tblOrder.InvoiceTotal end) as weektot,
SUM(bananastot ) AS bananastot ,
SUM(producetot) AS producetot,
dbo.tblCustomerShippingAddress.StoreOrAddressCode, dbo.tblCustomerShippingAddress.Address1

from tblOrder
INNER JOIN dbo.tblCustomerShippingAddress ON dbo.tblOrder.CustomerShippingID = dbo.tblCustomerShippingAddress.CustomerShippingID
outer apply
( select
sum(case when invoicedate between @mon and @wed and tblproduct.isbananas = 1 then dbo.tblOrderDetail.QuantityShipped else 0 END) as bananastot ,
sum(case when invoicedate between @mon and @wed and tblproduct.isbananas = 0 then dbo.tblOrderDetail.QuantityShipped else 0 END) as producetot
from dbo.tblOrder A
INNER JOIN dbo.tblOrderDetail ON A.OrderID = dbo.tblOrderDetail.OrderID
INNER JOIN dbo.tblProduct ON dbo.tblOrderDetail.ProductID = dbo.tblProduct.ProductID
where tblOrder.orderID=A.orderID
)od
GROUP BY dbo.tblCustomerShippingAddress.StoreOrAddressCode, dbo.tblCustomerShippingAddress.Address1

smh
Yak Posting Veteran

94 Posts

Posted - 2012-09-07 : 17:50:42

HI:

I am getting some results from your sample but I need to put a customerID see my code (WHERE tblCustomerShippingAddress.CustomerID = @CID) in order to check the results. Where would it go?

Thanks

smh
Yak Posting Veteran

94 Posts

Posted - 2012-09-07 : 20:32:26

Well , the only code that seemed to work with the outer apply was to put tblCustomerShippingAddress.CustomerID = @cid and to include in each case statement and include INNER JOIN dbo.tblCustomerShippingAddress ON dbo.tblOrder.CustomerShippingID = dbo.tblCustomerShippingAddress.CustomerShippingID
in the outer apply section but that did not give the proper results. Any ideas?

smh
Yak Posting Veteran

94 Posts

Posted - 2012-09-08 : 16:18:52

Well, I was forced to find the solution using 2 temp tables. Here is my solution (day of week parameters are small dates, (@CID) customerID is int. Any better solutions (like not using temp tables) is greatly appreciated.

declare @wkSalesTemp table(

totmon decimal(9,2) not null,
tottue decimal(9,2) not null,
totwed decimal(9,2) not null,
weektot decimal(9,2) not null,
bananastot decimal(9,2) not null,
producetot decimal(9,2) not null,
StoreOrAddressCode char(5))

declare @wkSalesTot table(
bananastot decimal(9,2) not null,
producetot decimal(9,2) not null,
StoreOrAddressCode char(5))

insert into @wkSalesTemp (totmon, tottue, totwed, weektot, bananastot, producetot,StoreOrAddressCode)
SELECT SUM(CASE WHEN invoicedate = @mon THEN dbo.tblOrder.InvoiceTotal else 0 END) AS totmon,
SUM(CASE WHEN invoicedate = @tue THEN dbo.tblOrder.InvoiceTotal else 0 END) AS tottue,
SUM(CASE WHEN invoicedate = @wed THEN dbo.tblOrder.InvoiceTotal else 0 END) AS totwed,

SUM(CASE WHEN invoicedate between @mon and @wed THEN dbo.tblOrder.InvoiceTotal else 0 END) AS weektot,
0,0, dbo.tblCustomerShippingAddress.StoreOrAddressCode
FROM dbo.tblOrder INNER JOIN
dbo.tblCustomerShippingAddress ON dbo.tblOrder.CustomerShippingID = dbo.tblCustomerShippingAddress.CustomerShippingID

WHERE tblCustomerShippingAddress.CustomerID = @CID)
group by StoreOrAddressCode
order by StoreOrAddressCode

insert into @wkSalesTot ( bananastot, producetot,StoreOrAddressCode)
select sum(case when invoicedate between @mon and @wed and isbananas = 1 then dbo.tblOrderDetail.QuantityShipped else 0 END) as bananastot ,
sum(case when invoicedate between @mon and @wed and isbananas = 0 then dbo.tblOrderDetail.QuantityShipped else 0 END) as producetot,
dbo.tblCustomerShippingAddress.StoreOrAddressCode
FROM dbo.tblOrder INNER JOIN
dbo.tblOrderDetail ON dbo.tblOrder.OrderID = dbo.tblOrderDetail.OrderID INNER JOIN
dbo.tblCustomerShippingAddress ON dbo.tblOrder.CustomerShippingID = dbo.tblCustomerShippingAddress.CustomerShippingID INNER JOIN
dbo.tblProduct ON dbo.tblOrderDetail.ProductID = dbo.tblProduct.ProductID
WHERE tblCustomerShippingAddress.CustomerID = @CID)
group by StoreOrAddressCode
order by StoreOrAddressCode

update @wkSalesTemp
set bananastot = t.bananastot, producetot = t.producetot
from @wkSalesTemp w, @wkSalesTot t
where w.StoreOrAddressCode = t.StoreOrAddressCode

select * from @wkSalesTemp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources