Help with a stored procedure
-
Hi, I am trying to use a SP to get me the sales of certain products, returns of the same products by store and in total. I have manged to write something that is clearly innefficient as it would mean I would have to enter each store id (e.g. 2007, 2065) to get my answer. Anyone know of a better way of writing it? Help is much appreciated:
CREATE PROCEDURE [dbo].[Returns_Report_Non_Tols]
@lowerdate smalldatetime,
@upperdate smalldatetime
AS
BEGIN
select stores.name, stores.storeid,
(select Distinct count (orders.orderid) as 'Sales'
from
stores left join orders on stores.storeid = orders.oaorstoreid
left join orderdetails on orders.orderid = orderdetails.orderid
left join products on orderdetails.productid = products.productid
where
Orders.OrderCancel <> 'Y' and
Orders.OrderDate between @lowerdate and @upperdate and
(Products.ProductID = '00154142-4'
or Products.ProductID = '00195913-2'
or Products.ProductID = '00471206-8'
or Products.ProductID = '00534207-2'
or Products.ProductID = '00660681-3'
or Products.ProductID = '00825717-4'
or Products.ProductID = '98765432-9'
or Products.ProductID = 'SKVF')) as 'TotalSales',
(select Distinct count (orders.orderid) as 'Sales'
from
stores left join orders on stores.storeid = orders.oaorstoreid
left join orderdetails on orders.orderid = orderdetails.orderid
left join products on orderdetails.productid = products.productid
where
Orders.Oaorstoreid = '2007' and
Orders.OrderCancel <> 'Y' and
Orders.OrderDate between @lowerdate and @upperdate and
(Products.ProductID = '00154142-4'
or Products.ProductID = '00195913-2'
or Products.ProductID = '00471206-8'
or Products.ProductID = '00534207-2'
or Products.ProductID = '00660681-3'
or Products.ProductID = '00825717-4'
or Products.ProductID = '98765432-9'
or Products.ProductID = 'SKVF')) as '2007Sales',
(select Distinct count (orders.orderid) as 'Sales'
from
stores left join orders on stores.storeid = orders.oaorstoreid
left join orderdetails on orders.orderid = orderdetails.orderid
left join products on orderdetails.productid = products.productid
where
Orders.Oaorstoreid = '2065' and
Orders.OrderCancel <> 'Y' and
Orders.OrderDate between @lowerdate and @upperdate and
(Products.ProductID = '00154142-4'
or Products.ProductID = '00195913-2'
or Products.ProductID = '00471206-8'
or Products.ProductID = '00534207-2'
or Products.ProductID = '00660681-3'
or Products.ProductID = '00825717-4'
or Products.ProductID = '98765432-9'
or Products.ProductID = 'SKVF')) as '2065Sales',
(select Distinct count (storereturns.frames_orderid) as 'Returns'
from
stores left join orders on stores.storeid = orders.oaorstoreid
left join orderdetails on orders.orderid = orderdetails.orderid
left join products on orderdetails.productid = products.productid
left join storereturns on orders.orderid = storereturns.frames_orderid
left join returnscode on storereturns.returnscode = returnscode.returnscode
where
returnscode.returnscode = 'T1' and
StoreReturns.ReturnsDate between @lowerdate and @upperdate and
(Products.ProductID = '00154142-4'
or Products.ProductID = '00195913-2'
or Products.ProductID = '00471206-8'
or Products.ProductID = '00534207-2'
or Products.ProductID = '00660681-3'
or Products.ProductID = '00825717-4'
or Products.ProductID = '98765432-9'
or Products.ProductID = 'SKVF')) as 'TotalReturns',
(select Distinct count (storereturns.frames_orderid) as 'Returns'
from
stores left join orders on stores.storeid = orders.oaorstoreid
left join orderdetails on orders.orderid = orderdetails.orderid
left join products on orderdetails.productid = products.productid
left join storereturns on orders.orderid = storereturns.frames_orderid
left join returnscode on storereturns.returnscode = returnscode.returnscode
where
returnscode.returnscode = 'T1' and
StoreReturns.storeid = '2007' and
StoreReturns.ReturnsDate between @lowerdate and @upperdate and
(Products.ProductID = '00154142-4'
or Products.ProductID = '00195913-2'
or Products.ProductID = '00471206-8'
or Products.ProductID = '00534207-2'
or Products.ProductID = '00660681-3'
or Products.ProductID = '00825717-4'
or Products.ProductID = '98765432-9'
or Products.ProductID = 'SKVF')) as '2007Returns',
(select Distinct count (storereturns.frames_orderid) as 'Returns'
from
stores left join orders on stores.storeid = orders.oaorstoreid
left join orderdetails on orders.orderid = orderdetails.orderid
left join products on orderdetails.productid = products.productid
left join storereturns on orders.orderid = storereturns.frames_orderid
left join returnscode on storereturns.returnscode = returnscode.returnscode
where
returnscode.returnscode = 'T1' and
StoreReturns.storeid = '2065' and
StoreReturns.ReturnsDate between @lowerdate and @upperdate and
(Products.ProductID = '00154142-4'
or Products.ProductID = '00195913-2'
or Products.ProductID = '00471206-8'
or Products.ProductID = '00534207-2'
or Products.ProductID = '00660681-3'
or Products.ProductID = '00825717-4'
or Products.ProductID = '98765432-9'
or Products.ProductID = 'SKVF')) as '2065Returns'
from
stores
where stores.opticianstore_flag = 'Y'
End
GO
-
Nadeem
You haven't really provided enough information. Do you always need the results from the same set of stores, or is it different each time? If the stores vary, then is it two stores all the time, or does the number of stores vary as well?
One thing I have noticed in your query is that you're using a LEFT JOIN and then imposing a filter (WHERE clause) on the right-hand table: this effectively turns your LEFT JOIN into an INNER JOIN. You should rewrite your query, changing LEFT JOINs to INNER JOINs where appropriate.
Also, do you really need to join to Products at all? You could rewrite that part of your WHERE clause as Orderdetails.ProductID = ''00154142-4' and so on, couldn't you?
John
-
Try this (Though I have made a lot of assumptions about your data, including that you really didn't need to do a lot of the joins you were doing to get the appropriate data):
if object_id('returns_report_non_tols') is not null drop procedure returns_report_non_tols;
go
CREATE PROCEDURE [dbo].[Returns_Report_Non_Tols] @lowerdate smalldatetime, @upperdate smalldatetime
AS
--Get all the order data we want to work with into a temp table
select o.orderid,
o.orderdate,
o.oaorstoreid as storeid,
od.productid
into #tmpOrders
from orders o join ordersdetail od on (o.orderid = od.orderid)
where o.ordercancel <> 'Y' and
o.orderdate between @lowerdate and @upperdate and
od.productid in ('00154142-4','00195913-2','00471206-8','00534207-2','00660681-3','00825717-4','98765432-9','SKVF')
--Get all the applicable return data that we want to work with into a temp table
--NOTE: I don't know your data well enough so you need to validate this one.
select o.orderid,
o.orderdate,
o.oaorstoreid as storeid,
od.productid,
sr.returnsdate
into #tmpReturns
from orders o join ordersdetail od on (o.orderid = od.orderid)
join storereturns sr on (sr.frames_orderid = o.orderid) --I'm assuming two stores can not have the same orderid otherwise we need to qualify this with the storeid
where o.ordercancel <> 'Y' and
od.productid in ('00154142-4','00195913-2','00471206-8','00534207-2','00660681-3','00825717-4','98765432-9','SKVF') and
sr.returnsdate between @lowerdate and @upperdate and
sr.returnscode = 'T1'
--now using our temp tables we can create a singe select against
--the stores table and a couple of computed tables using our temp tables and
--grouping on the storeid
select s.storeid, TotalSales, TotalReturns
from (
select storeid, count(*) as TotalSales
from #tmpOrders o
group by storeid
) ts join
(
select storeid, count(*) as TotalReturns
from #tmpReturns r
group by storeid
) tr on ts.storeid = tr.storeid
join stores s on s.storeid = ts.storeid
where s.opticianstore_flag = 'Y'
--Now you get one row per store with Total Sales and Total Returns.
--Of course I'm assuming that all stores will have at least one sale and one return
-- otherwise you need to account for missing data (outer joins, etc)
--Now if only I had some test data to verify I didn't do anything stupid.
--NOTE: If you really need just one row with the stores Sale/Return data pivoted, well that is another story. As it stands I'm sure someone smarter and with more time could get this down to a single select statement eliminating the temp tables.
--James.
go
-
The original query returns one row for each store, then does subqueries returning a totals column for each store in every row. Surely that can't be what you want. Also, the joins to products and returnscode appear to be unnecessary.
I think you'll get inaccurate counts with the previous solution. If there are multiple products on the target list in the same order, you'll count the same orderid more than once, so you still need to do a SELECT DISTINCT on the orderdetails table. Here's my version:
select
stores.name, stores.storeid,
sum(case when Orders.OrderDate between @lowerdate and @upperdate then 1 else 0 end) as TotalSales,
count(storereturns.frames_orderid) as [Returns]
from stores
left join orders
inner join (
select distinct orderid
from orderdetails
where productid IN ('00154142-4', '00195913-2', '00471206-8', '00534207-2', '00660681-3', '00825717-4', '98765432-9', 'SKVF')
) orderdetails on orders.orderid = orderdetails.orderid
on stores.storeid = orders.oaorstoreid
left join storereturns on orders.orderid = storereturns.frames_orderid
and storereturns.returnscode = 'T1'
and StoreReturns.ReturnsDate between @lowerdate and @upperdate
where stores.opticianstore_flag = 'Y'
and Orders.OrderCancel <> 'Y'
and (Orders.OrderDate between @lowerdate and @upperdate OR storereturns.frames_orderid IS NOT NULL)
group by stores.name, stores.storeid
-
Thanks for all your input.
John was correct that Product table was no longer required (I had used it earlier, but didn't need it any longer).
Scott - your solution looks perfect! Doing some figures on it using Crystal Reports and it's looking good.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply