June 6, 2008 at 10:55 am
I know that I missing something very simple here, but for the life of me I can't figure out what. I'm creating a query that counts the number of times reports are run. What I and my supervisor want to do is have this query run as a stored procedure each month. As a result I need to structure the query in such a way as it gets the current date and then subtracts 12 months and runs the query against the given date (e.g. Run on 1/1/2008 - 12 months = 1/1/2007). This way we will have a rolling 12 months of history.
Here is the code I currently have.
SELECT b.rpt_name as Report, SUBSTRING (ACCT, 1, 2) as Bank, COUNT (*) as Times_Run
FROM frpruse AS a INNER JOIN frprpt AS b (nolock)
ON a.rpt_id = b.rpt_id
WHERE a.rundt > MONTH(a.rundt) - 12
GROUP BY SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY SUBSTRING (ACCT, 1, 2), b.rpt_name
The problem is this seems to count only the months with 12 in them (so December), but counts them for the entire table (which has history back to 2002)
Thank you,
June 6, 2008 at 11:02 am
I'd start with a variable,
declare @start datetime.
Set that to be the "date" of a year ago using Dateadd. Use months, -12.
Then in your WHERE clause, use the variable.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 6, 2008 at 12:34 pm
I think you can use any one of these 2...?
WHERE a.rundt > dateadd(month, -12, a.rundt )
GROUP BY SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY SUBSTRING (ACCT, 1, 2), b.rpt_name
or
WHERE datediff(month, a.rundt, getdate()) <= 12
GROUP BY SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY SUBSTRING (ACCT, 1, 2), b.rpt_name
June 6, 2008 at 12:48 pm
If you have an index on the date column, then the Dateadd one should be better probably resulting in index seek, rather than index scan.
June 6, 2008 at 2:38 pm
Thank you to all who replied. Here is what I finally came up with. It isn't elegant, but it does get the job done.
DECLARE @todaysdate DATETIME
set @todaysdate = GETDATE ()
DECLARE @rundate DATETIME
set @rundate = DATEADD (MONTH, -12, @todaysdate)
INSERT INTO #BOAreportstemp (Report, Bank, Times_Run)
SELECT b.rpt_name as Report, SUBSTRING (ACCT, 1, 2) as Bank, COUNT (*) as Times_Run
FROM frpruse AS a INNER JOIN frprpt AS b (nolock)
ON a.rpt_id = b.rpt_id
WHERE a.rundt > MONTH(a.rundt) - 12
GROUP BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
It seems to get the job done in terms of providing the data I need.
Thank you again,
Brian
June 6, 2008 at 4:47 pm
marchbrown1529 (6/6/2008)
Thank you to all who replied. Here is what I finally came up with. It isn't elegant, but it does get the job done.DECLARE @todaysdate DATETIME
set @todaysdate = GETDATE ()
DECLARE @rundate DATETIME
set @rundate = DATEADD (MONTH, -12, @todaysdate)
INSERT INTO #BOAreportstemp (Report, Bank, Times_Run)
SELECT b.rpt_name as Report, SUBSTRING (ACCT, 1, 2) as Bank, COUNT (*) as Times_Run
FROM frpruse AS a INNER JOIN frprpt AS b (nolock)
ON a.rpt_id = b.rpt_id
WHERE a.rundt > MONTH(a.rundt) - 12
GROUP BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
It seems to get the job done in terms of providing the data I need.
Thank you again,
Brian
If you want to make sure you always have a full 12 months in your report, you can use the following:
DECLARE @begin_date DATETIME;
DECLARE @end_date DATETIME;
SET @begin_date = DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -12, getdate())), 0);
SET @end_date = DATEADD(month, DATEDIFF(month, 0, getdate()), 0);
INSERT INTO #BOAreportstemp (Report, Bank, Times_Run)
SELECT b.rpt_name as Report, SUBSTRING (ACCT, 1, 2) as Bank, COUNT (*) as Times_Run
FROM frpruse AS a INNER JOIN frprpt AS b (nolock) ON a.rpt_id = b.rpt_id
WHERE a.rundt >= @begin_date
AND a.rundt < @end_date
GROUP BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
This will return all data from 06/01/2007 through 05/31/2008 when run in June, 2008. If you want this to be a rolling 12 months including everything through the end of yesterday, you can do this:
DECLARE @begin_date DATETIME;
DECLARE @end_date DATETIME;
SET @begin_date = DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -12, getdate())), 0);
SET @end_date = DATEADD(day, DATEDIFF(day, 0, getdate()), 0);
INSERT INTO #BOAreportstemp (Report, Bank, Times_Run)
SELECT b.rpt_name as Report, SUBSTRING (ACCT, 1, 2) as Bank, COUNT (*) as Times_Run
FROM frpruse AS a INNER JOIN frprpt AS b (nolock) ON a.rpt_id = b.rpt_id
WHERE a.rundt >= @begin_date
AND a.rundt < @end_date
GROUP BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
ORDER BY a.rundt, SUBSTRING (ACCT, 1, 2), b.rpt_name
This will give you everything greater than or equal to 2007-06-06 and less than 2008-06-06.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply