Function for YTD calc in TSQL

Question

Post reply

Function for YTD calc in TSQL

nordest

Old Hand

Points: 308
More actions
September 5, 2005 at 10:17 am

#168339

Hi , there is a date function in TSQL for calculating time period value like YTD?
Thanks By
Franco

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Can you please elaborate?

It will depend on what you want, calendar year to date, number of days

select datediff(dd, '01/01/2005', getdate())

The only date functions are.

Function Description Example

DATEDIFF( ) Calculates an interval between two dates. SELECT fname, lname, hire_date

FROM employee

WHERE DATEDIFF(year, hire_date, getdate()) > 5

Locates all employees hired more than five years ago.

DATEPART( ) Returns the specified portion of a date or datetime column, including the day, month, or year. SELECT DATEPART(year, hire_date)

FROM employee

Displays only the year in which an employee was hired (not the full date).

CURDATE( )1,

GETDATE( ) or DATE( ) Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today. SELECT order_id

FROM orders

WHERE order_date = GETDATE()

Displays orders placed today.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Hi Ray, just a quick comment on your response - your final query will, in fact, only return those orders that are placed at the same millisecond as the query is run.

The time portion of getdate() needs to be stripped out before it will work as you suggest.

Also, curdate() and date() are not standard SQL Server functions.

Regards

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Nordest,

There is no direct function and it depends on what you want...

If you wish to use a SELECT to return all records for the year of the current date to the current date, this automatically works every time...

SELECT yada-yada

FROM yourtable

WHERE somedatefield >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

If you wish to find the number of days in for YTD, this will work nicely...

SELECT DATEPART(dy,GETDATE())

Your request was a bit vague as to which type of YTD function you wanted. If you want a SUM or a COUNT of something, post back. It would be good include table schema and some sample data in the form of insert statements for the quickest help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nordest Old Hand Points: 308 More actions · Answer 4

Hi Ray

you are right! my question is no so exactly formulated.

I'm working with an order table with this field:

OrderDate , OrderNumber , CustomerID , ItemID , SalesQty

With YTD in this case I want to say the total sales order from today until a year ago. ( better from a specific day untill the same date-1 of the previous year ) for a particular CustomerID or ItemID or CustomerID/ItemID.

Bye

Franco - Nordest

Ray M SSC-Insane Points: 21093 More actions · Answer 5

I was merely showing all date related functions from Books Online

I just pasted them in the thread.

Ray M SSC-Insane Points: 21093 More actions · Answer 6

for the last year of records

Select OrderDate , OrderNumber , CustomerID , ItemID , SalesQty

From Order

Where OrderDate >= dateadd(yy,-1,getdate())

This pulls all records from exacly 1 year ago til now.

If you want current year, meaning from jan1 of current year.

use as Jeff suggested

rtompkins SSC Veteran Points: 239 More actions · Answer 7

Easiest and clearlest syntax for YTD takes advantage of implicit character conversion:

declare @FirstDate datetime

select @FirstDate = '01/01/' + convert(char(4),datepart(year,getdate()))

Select ..... where Orderdate >= @FirstDate

grahamrab Valued Member Points: 63 More actions · Answer 8

Hi,

Your posts have been really helpful but I was hoping that I would get something a bit more specific. We are using Business Objects on top of a SQL Server 2005 database. The datamart is dimensionally modelled with the main fact table being joined to the dimensions via surrogate keys (one of the dimensions is a time table). I’m looking how to obtain all data within a calendar or financial/fiscal year from the data entered by a user. That is to say, if a user chooses March 2009 then the data for that YTD will be based from Jan 2009 onwards. Likewise if a user selects a date such as Aug 2008 for financial year 2008/09 then all data will be returned from April 08 to Aug 08 (Financial year starting on April). Any help would be great

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 9

If you are using BO, then you could create a simple report in either Web Intelligence or Desktop intelligence using this field. Then look at the sql it suggests without actually looking at it.

I would image that you have a startdate and an end date for each period and you would use that in a between clause, but without seeing the schema it's difficult to say.

hth

Rich

Hope this helps,
Rich

[p]
[/p]