How To Calculate WTD,MTD,YTD in T-SQL?
-
Tuesday, November 20, 2012 6:24 PM
Hi,
I have a requirement lets say i.e to create the MTD,WTD sales for a column based on the given date.I can do it in MDX because we have the date cals defined but i dont know how to do it in t-sql can some one help me please?
My Mdx query looks like this
with
set [Date] as [Date].[Hierarchy - Fiscal].[Date].&[20121118]
member [Fcst] AS SUM([Date] * [Fiscal Date Calculations].[Date Calculation].&[MTD],[Measures].[Gross Demand Dollars])
select {[Fcst]} on 0 from edwNow i have to get the MTd for the given date and lets say column name is Gross_dollars from temp table.
Anyhelp will be appreciated.
Thanks,
Sujith.
All Replies
-
Tuesday, November 20, 2012 6:33 PM
Hi,
See below url:
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
- Proposed As Answer by Ed Price - MSFTMicrosoft Employee, Owner Wednesday, November 21, 2012 7:50 PM
- Marked As Answer by Iric WenModerator Monday, December 03, 2012 6:39 AM
-
Tuesday, November 20, 2012 10:58 PM
I'm just confused today. ;) anyways is this what you ment?
declare @temp table (dt datetime,sales decimal(10,2)) insert into @temp (dt,sales) values ('11-20-2012',5.00), ('11-19-2012',6.42), ('10-01-2012',7.84), ('10-11-2012',11.02), ('09-13-2012',15.63), ('09-19-2012',30.24), ('01-01-2010',0.01) select SUM(sales) as Total from @temp where dt > DATEADD(MM,-1,getdate()) -- month select SUM(sales) as Total from @temp where dt > DATEADD(WW,-1,getdate()) -- week select SUM(sales) as Total from @temp where dt > DATEADD(DD,-1,getdate()) -- day
Pérez
- Proposed As Answer by Ed Price - MSFTMicrosoft Employee, Owner Wednesday, November 21, 2012 7:50 PM
- Marked As Answer by Iric WenModerator Monday, December 03, 2012 6:39 AM
.png)
