SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Function for YTD calc in TSQL


Function for YTD calc in TSQL

Author
Message
nordest
nordest
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
Hi , there is a date function in TSQL for calculating time period value like YTD?

Thanks By

Franco



Ray M
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2197 Visits: 1076

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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18810 Visits: 20459
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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87470 Visits: 41116

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
nordest
nordest
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
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
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2197 Visits: 1076

I was merely showing all date related functions from Books Online

I just pasted them in the thread.


Ray M
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2197 Visits: 1076

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
rtompkins
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 34

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
grahamrab
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 15
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
RichardDouglas
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1103 Visits: 707
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search