October 4, 2005 at 10:19 am
Hi there,
I have an Orders table like this:
StoreID Int
OrderID Int
dtStamp smalldatetime
OrderNet currency
So basically, I have multiple stores in my database, having multiple orders.
I need to produce a report than sums the OrderNet for a required period, normally a weekly basis.
However, along side this, I need to have a comparison of the Sales for that period exactly one year ago.
The results should look like this:
StoreID ThisPeriod LastYear
1 £500 £400
2 £900 £800
etc
I currently have something working, but its certainly not efficient. Its uses 3 views joined together to get the final result. Currently the database has 7 stores with an average of 200 orders per day. The report take a few minutes to run. This will increase to 100 stores over the next 4 months as they migrate to our software, so you can imagine this report will just grind to a halt.
Bottom line, how can I simplify this, for speed. I'm sure this could be effectively put into 1 stored procedure but I'm stumped.
Any response gratefully appreciated.
Andy
October 4, 2005 at 10:47 am
Assume the parameter for the period is @PeriodDate, which is a datetime.
Togenerate the report for the week contains this date,
First of all, find out the @WeekStartTime and @WeekEndTime that contains the period @PeriodDate;
DECLARE @Current (StoreID int not null, sales int not null)
INSERT @Current (StoreID,Sales)
SELECT StoreID,SUM(OrderNet) AS sales
FROM Orders
WHERE stStamp between @WeekStartTime and @WeekEndTime
GROUP BY StoreID
secondly figure out the @WeekStartTime and @WeekEndTime that contains the period DATEADD(year, -1,@PeriodDate);
SELECT a.StoreID, a.Sales as ThisPeriod, ISNULL(b.Sales,0) as LastYear
FROM @Current a LEFT JOIN (
SELECT StoreID,SUM(OrderNet) AS sales
FROM Orders
WHERE stStamp between @WeekStartTime and @WeekEndTime
GROUP BY StoreID
) b
on a.StoreID=b.StoreID
October 4, 2005 at 1:41 pm
Thanks for the reply.
Silly question, but how do I join the 2 sections together?
Just simply put GO in the middle?
I mean like to have the whole thing in 1 stored procedure? Or shouldnt I be doing that?
Using your example I've put together the following:
CREATE PROCEDURE dbo.Test AS
DECLARE @Current table (StoreName varchar(50) not null, sales int not null)
INSERT @Current (StoreName,Sales)
SELECT StoreName,SUM(NetSale) AS sales
FROM Orders
WHERE dtStamp>=CONVERT(DATETIME,'2005-09-01 00:00:00', 102)
AND
dtStamp = CONVERT(DATETIME, '2004-09-01 00:00:00', 102)
AND
dtStamp <= CONVERT(DATETIME, '2004-09-07 00:00:00', 102)
GROUP BY StoreID
) b
on a.Storename=b.Storename
However, When I check syntax it says I must declare B
October 4, 2005 at 1:59 pm
Doesnt matter. I figured the problem.
I took the 'GO' out from the middle.
Works perfectly now, thankyou so much.
Kind Regards
Andy
October 4, 2005 at 2:03 pm
Actually, just one final thing.
The results I get look something like this:
Store ThisPeriod LastYear
StoreA 20 19.383
StoreB 30 32.343
The ThisPeriod column is decimal?
October 4, 2005 at 2:05 pm
SELECT StoreName,SUM(NetSale * 1.0) AS sales
FROM Orders
October 4, 2005 at 3:27 pm
Unfortunately that doesnt work.
If I had to the second half of the sp, I get an aggregate error message on the a.StoreName and b.Sales
SELECT a.StoreName, SUM(a.Sales * 1.0) as ThisPeriod, ISNULL(b.Sales,0) as LastYear
FROM @Current a LEFT JOIN (
SELECT StoreName,SUM(NetSale) AS sales
FROM Orders
WHERE dtStamp >= CONVERT(DATETIME, '2004-09-01 00:00:00', 102)
AND
dtStamp <= CONVERT(DATETIME, '2004-09-07 00:00:00', 102)
GROUP BY StoreName
) b
on a.Storename=b.Storename
GO
October 4, 2005 at 4:15 pm
Andy,
From what the Previous poster did I can see that @Current has the data already agregated!!! you don't need the SUM again!
ex:
SELECT a.StoreName, a.Sales as ThisPeriod, ISNULL(b.Sales,0) as LastYear
FROM @Current a LEFT JOIN (
SELECT StoreName,SUM(NetSale * 1.0) AS sales
FROM Orders
WHERE dtStamp >= CONVERT(DATETIME, '2004-09-01 00:00:00', 102)
AND
dtStamp <= CONVERT(DATETIME, '2004-09-07 00:00:00', 102)
GROUP BY StoreName
) b
on a.Storename=b.Storename
Cheers!
* Noel
October 5, 2005 at 2:37 am
Noel,
Actually I fixed it another way.
The original declaration had:
DECLARE @Current table (StoreName varchar(50) not null, sales int not null)
I changed the int to money:
DECLARE @Current table (StoreName varchar(50) not null, sales money not null)
Many thanks for all yours help
Andy
October 5, 2005 at 4:22 am
Hello again,
I've run into abit of a problem with this query.
To summarise all the above, here's the final query:
DECLARE @Current table (StoreName varchar(50) not null, sales money not null)
INSERT @Current (StoreName,Sales)
SELECT StoreName,SUM(NetSale) AS sales
FROM Orders
WHERE dtStamp>=CONVERT(DATETIME,'2005-09-01 00:00:00', 102)
AND
dtStamp = CONVERT(DATETIME, '2004-09-01 00:00:00', 102)
AND
dtStamp <= CONVERT(DATETIME, '2004-09-07 00:00:00', 102)
GROUP BY StoreName
) b
on a.Storename=b.Storename
GO
The output looks something like this:
StoreName -+- ThisPeriod -+- LastPeriod
StoreA -+- 100.23 -+- 123.98
StoreB -+- 123.35 -+- 432.23
So the period is based on 7 days.
What if rather than output grouped by store, I would want to output by Day.
Simple I thought, change the grouping to dtStamp, done.....wrong!
That actually works, however, there is no join between thisperiod and lastperiod. We know there is a link between todays date (Wednesday) and one year ago, but the query doesnt, so I cant understand the join.
Should look Something like this:
Date -+- ThisPeriod -+- LastPeriod
Monday -+- 23.45 -+- 20.43
Tuesday -+- 89.21 -+- 76.32
Wednesday -+- 56.22 -+- 23.43
Sorry if this sounds strange. But actually this is all valid to my cause. The results all appear in a presentation. A chart on the left showing Sales for a period based Monday to Sunday, and a grid on the right showing the stores for this period.
This query is brilliant for my grid, I just need to flip it around and group by date, but still include the LastPeriod column.
Any ideas?
October 5, 2005 at 7:29 am
Try this
DECLARE @startdate datetime
SET @startdate = '20050901'
SELECT @startdate+n.number,DATEPART(dw,@startdate+n.number),
DATENAME(dw,@startdate+n.number) AS [Date],
SUM(ISNULL(t.ThisPeriod,0)) AS [ThisPeriod],
SUM(ISNULL(l.LastPeriod,0)) AS [LastPeriod]
FROM master.dbo.spt_values n
LEFT OUTER JOIN
(SELECT DATEPART(dw,ot.dtStamp) AS [dayno],SUM(ot.OrderNet) AS [ThisPeriod]
FROM [Orders] ot
WHERE ot.dtStamp >= @startdate AND ot.dtStamp < @startdate+7
GROUP BY DATEPART(dw,ot.dtStamp)) t
ON t.dayno = DATEPART(dw,@startdate+n.number)
LEFT OUTER JOIN
(SELECT DATEPART(dw,DATEADD(year,1,ol.dtStamp)) AS [dayno],SUM(ol.OrderNet) AS [LASTPeriod]
FROM [Orders] ol
WHERE ol.dtStamp >= DATEADD(year,-1,@startdate) AND ol.dtStamp < DATEADD(year,-1,@startdate)+7
GROUP BY DATEPART(dw,DATEADD(year,1,ol.dtStamp))) l
ON l.dayno = DATEPART(dw,@startdate+n.number)
WHERE n.[type] = 'P' AND n.number between 0 AND 6
GROUP BY @startdate+n.number,n.number
ORDER BY @startdate+n.number
You may have to break up the query for performance though, by using temp tables to accumulate the subqueries first or do two queries with a UNION
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply