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.
| Author |
Topic |
|
scottishcalvin
Starting Member
10 Posts |
 Posted - 2011-01-20 : 09:44:27
|
| I've been scouting about the net for a bit on this and I thought I'd see what the recommended style was here.Essentially I have transactions and dates and I want to aggregate products together to show moving totals, month on month. Ive been doing this with a query (AS400) that took about 5 minutes to refresh and recently I've become really keen on making queries more efficient.Previously this has been done likeSum((case when Source.TrDate<'2008-02-01' and Source.TrDate>='2008-02-01' then 1 else 0 end)*TheSource.Amount) and so forth for dozens of months.I thought one way to improve this would be to have a seperate subtable so that the can be called Month1, Month2 etc and refered to as such. I could change them to calculate rather than being specified.Then I thought I have a choice between moving them outisde the source table and using a Join rather than a Case to sift the data.Then there's the option of doing each month seperately first and then pulling everything together into one table before I link to other information like turning the user codes to user namesI suspect that this sort of aggregation problem must be pretty common, is there a 'best practive' way to filter the correct data based on dates? |
|
|
|
|
|
Date Filtering: Join vs Where vs Case