October 21, 2010 at 2:44 pm
Hi there
Just wondering what the most effiecient way to do this is?
Scenario:
Getting output to be used on graphs etc. (query is called via .net app)
Need to be able to pass in a variable number of date ranges, hence using a table type param (date range could be months as per example below, or days, years etc.)
Example:
Getting total number of events per month for the last x months (NB the values in @drange would of course actually be populated in .net code)
DECLARE @drange DateTimeTT
INSERT @drange(dateA, dateB) VALUES (DATEADD(month, -3, '20101101'), DATEADD(month, -2, '20101101')), (DATEADD(month, -2, '20101101'), DATEADD(month, -1, '20101101')), (DATEADD(month, -1, '20101101'), '20101101')
SELECT p.PersonID, p.PersonFirstName, p.PersonLastName, d.dateA, d.dateB, (SELECT COUNT(EventID) FROM Events AS e WHERE e.EventPerson=p.PersonID AND EventDate >= d.dateA AND EventDate < d.dateB) FROM Person p CROSS JOIN @drange d WHERE p.PersonID IN (SELECT TOP 5 c.PersonID FROM Person c ORDER BY c.PersonDateLastModified DESC)
Desired output:
per person per month total number of events
Is there a better way or is this pretty good?
Another slightly different scenario:
In some instances the 'WHERE p.PersonID IN (SELECT TOP 5 c.PersonID FROM Person c ORDER BY c.PersonDateLastModified DESC)' would be replaced by a JOIN with a table type containing IDs - so specific people rather than the top 5 most recent modified people.
Any advice would be greatly appreciated.
Regards
Sam
October 22, 2010 at 1:17 am
If you provide table scripts and some sample data we can try to help.
Take a look at the article linked in my signature line, you will find how to post your question to get quick and helpful answers.
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply