January 28, 2008 at 2:44 pm
I have a view (View1) which brings together several tables to get all the data I require.
The data is orders which have a Trade, raised date and completed date.
I use a Case statement against each date to transpose this into a Year and Week (RaisedPeriod and CompletePeriod) for Reporting (ie 2008 01)
I then have 2 futher views.
View 2 which looks at View 1 and summaries the data grouping on the Trade and RaisedPeriod (this therefore gives me the count of orders by period plus some other sums)
View 3 does the same but uses the Trade and CompletePeriod (this therefore gives me the count of orders completed by period plus some other sums)
I then Have View 4 which links view 2 and 3 based on Period (Raised Period to CompletePeriod) and Trade.
The result is a recordset that shows raised and completed order summary data by trade and Perod.
I am hoping to rewrite all this into 1 stored procedure using derived tables. Does this sound like the right way to procede?
View 1 returns
OrderNo---Trade--RaisedDate---RaisedPeriod---CompleteDate----Complete Period
1------------PL----01/01/2008---2008 01-------28/01/2008-------2008 01
2------------PL----12/12/2007---2007 53-------05/01/2008-------2008 01
3------------PL----13/12/2007---2007 53-------20/12/2007-------2007 53
View 2 Returns
Raised Period----Trade----OrderCount
2007 53-----------PL--------2
2008 01-----------PL--------1
View 3 Returns
Complete Period----Trade----OrderCount
2007 53--------------PL-------1
2008 01--------------PL-------2
View 4 Returns
Period------RaisedCount-----CompleteCount-----Diff
2007 53------2----------------1------------------1
2008 01------1----------------2------------------1 (minus 1)
Any advice most welcome
thanks
Mark.
January 28, 2008 at 3:52 pm
I am hoping to rewrite all this into 1 stored procedure using derived tables.
Any reason for this?
_____________
Code for TallyGenerator
January 29, 2008 at 1:21 am
Yes
I want it all in 1 stored procedure. The base view is also used for other things so it the bas was changed it would affect the other views.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply