Current Year

Hi,
I am new at T-SQL.
I need to access a table with the field inv_dt.
I need the sum of the invoices where it takes only the sum of the current
year invoices.
When 2006 arrives I only want the sum of 2006 invoices without having to
rewrite the code for each year that rolls around.
Any help would be appreciated.
Thanks,
TerryHi, Terry
Try something like this:
SELECT SUM(ammount) FROM invoices
WHERE YEAR(inv_dt)=YEAR(GETDATE())
Razvan|||SELECT SUM(Col)
FROM YourTable
WHERE
inv_dt >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
AND inv_dt < DATEADD(yy, DATEDIFF(yy, 0, GETDATE())+1, 0)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"newbi" <twillett@.cox-internet.com> wrote in message
news:Vapgf.24167$4l5.6904@.dukeread05...
> Hi,
> I am new at T-SQL.
> I need to access a table with the field inv_dt.
> I need the sum of the invoices where it takes only the sum of the current
> year invoices.
> When 2006 arrives I only want the sum of 2006 invoices without having to
> rewrite the code for each year that rolls around.
> Any help would be appreciated.
> Thanks,
> Terry
>|||DECLARE
@.firstOfThisYear SMALLDATETIME,
@.firstOfNextYear SMALLDATETIME
SET @.firstOfThisYear = CONVERT(CHAR(4), YEAR(CURRENT_TIMESTAMP))+'0101'
SET @.firstOfNextYear = DATEADD(YEAR,1,@.FirstOfThisYear)
SELECT SUM(amount)
FROM invoices
WHERE inv_dt >= @.firstOfThisYear
AND inv_dt < @.firstOfNextYear
Now if inv_dt has an index, you can use it.
"newbi" <twillett@.cox-internet.com> wrote in message
news:Vapgf.24167$4l5.6904@.dukeread05...
> Hi,
> I am new at T-SQL.
> I need to access a table with the field inv_dt.
> I need the sum of the invoices where it takes only the sum of the current
> year invoices.
> When 2006 arrives I only want the sum of 2006 invoices without having to
> rewrite the code for each year that rolls around.
> Any help would be appreciated.
> Thanks,
> Terry
>|||Careful with that one, Razvan -- since you're using a function on the column
the query can't be satisfied using an index s.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1132601271.210016.241970@.g44g2000cwa.googlegroups.com...
> Hi, Terry
> Try something like this:
> SELECT SUM(ammount) FROM invoices
> WHERE YEAR(inv_dt)=YEAR(GETDATE())
> Razvan
>|||Thanks Guys,
That worked perfectly.
Terry
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eRtAxHt7FHA.2176@.TK2MSFTNGP14.phx.gbl...
> SELECT SUM(Col)
> FROM YourTable
> WHERE
> inv_dt >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
> AND inv_dt < DATEADD(yy, DATEDIFF(yy, 0, GETDATE())+1, 0)
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "newbi" <twillett@.cox-internet.com> wrote in message
> news:Vapgf.24167$4l5.6904@.dukeread05...
>|||> Careful with that one, Razvan -- since you're using a function on the columnd">
> the query can't be satisfied using an index s.
Yes, indeed. But probably, we won't have more than 5-10 years of data
in the table, so an index scan would be used anyway. Anyway, you are
correct that your query is preferable, because the optimizer has more
options available.
Razvan