WHERE LAST 6 months and Last 6 weeks

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

WHERE LAST 6 months and Last 6 weeks

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2012-03-16 : 15:52:16

I need to show last 6 months of data and last 6 weeks of data excluding current month in progress and current week in progress. I searched for solutions but no luck so far. Thsi is what I have written for 6 months so far but it does not work for me. CLOSE_TIME is datetime.

ALTER PROCEDURE [dbo].[SP_TEST]
@Team varchar(255)

AS

BEGIN

SELECT
CLOSED_YEAR_MONTH2,
COUNT (NUMBERPRGN) as IncidentCount,
SUM(convert(decimal(10,2),BPIIMPACT)) AS BPIIMPACT

FROM dbo.view_STrecords

WHERE TEAM = @Team

AND BPIIMPACT is not null
AND CLOSED_YEAR_MONTH2 is not null
AND
CLOSE_TIME >dateadd(mm,-7,getdate()-1), 0)
)

GROUP BY CLOSED_YEAR_MONTH2
ORDER BY CLOSED_YEAR_MONTH2

END

current output:
2011-10 460 7.83
2011-11 937 0.14
2011-12 779 8.60
2012-01 1059 3.92
2012-02 904 0.44
2012-03 205 0.00

what I need is:

2011-09 860 7.83
2011-10 460 7.83
2011-11 937 0.14
2011-12 779 8.60
2012-01 1059 3.92
2012-02 904 0.44

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 15:54:47

for previous 6 months use


CLOSE_TIME >=dateadd(mm,datediff(mm,0,getdate())-7,0)
AND CLOSE_TIME < dateadd(mm,datediff(mm,0,getdate()),0)

and for previous 6 weeks use

CLOSE_TIME >=dateadd(wk,datediff(wk,0,getdate())-7,0)
AND CLOSE_TIME < dateadd(wk,datediff(wk,0,getdate()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jszulc
Yak Posting Veteran

66 Posts

Posted - 2012-03-16 : 16:41:46

Tank You visakh16

Thsi gives me data I need. One question though, the first query is taking more than 15 sec to run. I am doing this on 35 000 recs.

is there a way to speed this up?:
CLOSE_TIME >=dateadd(mm,datediff(mm,0,getdate())-7,0)
AND CLOSE_TIME < dateadd(mm,datediff(mm,0,getdate()),0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-16 : 17:24:38

Yes. Put an index over close_time column.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources