How To Calculate WTD,MTD,YTD in T-SQL?

# 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 edw

Now 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:

http://www.bigresource.com/MS_SQL-Best-way-to-enable-users-to-choose-between-WTD-MTD-QTD-YTD-at-run-time-MDX--S4KKuf73.html

• 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