Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Function for YTD calc in TSQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 5, 2005 10:17 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, September 7, 2007 11:34 AM Points: 2, Visits: 1
 Hi , there is a date function in TSQL for calculating time period value like YTD?Thanks ByFranco
Post #217026
 Posted Monday, September 5, 2005 12:16 PM
 UDP Broadcaster Group: General Forum Members Last Login: Tuesday, August 16, 2016 3:59 PM Points: 1,487, Visits: 1,076
 Can you please elaborate?It will depend on what you want, calendar year to date, number of daysselect 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_dateFROM employeeWHERE DATEDIFF(year, hire_date, getdate()) > 5Locates 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 employeeDisplays 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_idFROM ordersWHERE order_date = GETDATE()Displays orders placed today.
Post #217037
 Posted Monday, September 5, 2005 4:13 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:25 AM Points: 7,547, Visits: 18,284
Post #217052
 Posted Monday, September 5, 2005 6:40 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:35 PM Points: 41,436, Visits: 38,770
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #217059
 Posted Tuesday, September 6, 2005 1:34 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, September 7, 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 , SalesQtyWith 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.ByeFranco - Nordest
Post #217119
 Posted Tuesday, September 6, 2005 1:36 PM
 UDP Broadcaster Group: General Forum Members Last Login: Tuesday, August 16, 2016 3:59 PM Points: 1,487, Visits: 1,076
 I was merely showing all date related functions from Books OnlineI just pasted them in the thread.
Post #217373
 Posted Tuesday, September 6, 2005 2:00 PM
 UDP Broadcaster Group: General Forum Members Last Login: Tuesday, August 16, 2016 3:59 PM Points: 1,487, Visits: 1,076
 for the last year of recordsSelect OrderDate , OrderNumber , CustomerID , ItemID , SalesQtyFrom OrderWhere 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 6, 2005 2:12 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, May 13, 2015 2:32 PM Points: 47, Visits: 34
 Easiest and clearlest syntax for YTD takes advantage of implicit character conversion:declare @FirstDate datetimeselect @FirstDate = '01/01/' + convert(char(4),datepart(year,getdate())) Select ..... where Orderdate >= @FirstDate
Post #217395
 Posted Thursday, April 2, 2009 6:18 AM
 Forum 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 2, 2009 8:41 AM
 SSC Eights! Group: General Forum Members Last Login: Friday, March 18, 2016 5:38 AM Points: 969, Visits: 704
 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.hthRich Hope this helps,Rich
Post #688987

 Permissions