Weekly Figures - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Weekly Figures

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-29 : 18:07:47

Hi Guys

I am using the following queries to determine the number of returns made for each week of the year:

SELECT

DATEPART(wk,ri.DateEntered) AS 'Week Number'

,vr.ReasonName

,COUNT(VoidItemId)

FROM [dbo].[voiditem] vi (nolock)

LEFT JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid

LEFT JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid

WHERE vi.enteredbyreasonid IN (7,9,21)

GROUP BY

DATEPART(wk,ri.DateEntered)

,vr.ReasonName

ORDER BY

DATEPART(wk,ri.DateEntered)

The thing I have noticed that it brings back figures for all years, as it is showing figures for week 52 (Which hasnt occured yet for this year). Does anyone know how i could change this query to reflect data just for the ccurrent year?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-29 : 18:16:43

AND DATEDIFF(YEAR, ri.DateEntered, GETDATE()) = 0

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

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-30 : 08:04:34

Hey Peso

That was GREAT!!! Thanks for that!!!

If I wanted to let the end user choose the year through a parameter where would I add it? wouuld it be something like 'DATEDIFF(YEAR, ri.DateEntered, GETDATE()) = @year'?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:12:12

declare @year varchar(4)
set @year='2008'--sample value

then use it like this in WHERE condition

AND ri.DateEntered>=DATEADD(yy,0,@year)
AND ri.DateEntered<DATEADD(yy,1,@year)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-30 : 12:00:10

And just in case parameter is not CHAR but INT instead.

AND ri.DateEntered >= DATEADD(YEAR, @Year - 1900, 0)
AND ri.DateEntered < DATEADD(YEAR, @Year - 1899, 0)

This suggestion works for both CHAR and INT datatype for @Year variable.

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

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-10-30 : 12:13:29

Thanks guys!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources