Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function for YTD calc in TSQL Expand / Collapse
Author
Message
Posted Monday, September 05, 2005 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2007 11:34 AM
Points: 2, Visits: 1
Hi , there is a date function in TSQL for calculating time period value like YTD?

Thanks By

Franco



Post #217026
Posted Monday, September 05, 2005 12:16 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:52 PM
Points: 1,478, Visits: 1,020

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.

 

Post #217037
Posted Monday, September 05, 2005 4:13 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 4,828, Visits: 11,184
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #217052
Posted Monday, September 05, 2005 6:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258

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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #217059
Posted Tuesday, September 06, 2005 1:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2007 11:34 AM
Points: 2, 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




Post #217119
Posted Tuesday, September 06, 2005 1:36 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:52 PM
Points: 1,478, Visits: 1,020

I was merely showing all date related functions from Books Online

I just pasted them in the thread.

Post #217373
Posted Tuesday, September 06, 2005 2:00 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:52 PM
Points: 1,478, Visits: 1,020

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

Post #217387
Posted Tuesday, September 06, 2005 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 12:34 PM
Points: 47, Visits: 30

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




Post #217395
Posted Thursday, April 02, 2009 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 12, 2009 6:16 AM
Points: 1, 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
Post #688766
Posted Thursday, April 02, 2009 8:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:18 PM
Points: 974, Visits: 687
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



Post #688987
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse