MTD Calculations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MTD Calculations

Author

Topic

MBeal
Posting Yak Master

110 Posts

Posted - 2005-02-04 : 10:52:36

I am constantly creating reports for management. These reports require WTD, MTD and YTD calculations. Since YTD is a one time a year change, I can manually do this. MTD is more complicated. Our fiscal year starts May 1 and runs through April 30th. My weeks are always Wednesday to Tuesday. I have been using the following in my where clause to limit the results for WTD and it works well:

Select *
From MYTABLE
Where STAYDATE between
(SELECT
CASE
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 7)
THEN (Select Left((GetDate()-4),11))--4
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 6)
THEN (Select Left((GetDate()-3),11))--3
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 5)
THEN (Select Left((GetDate()-2),11))--2
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 4)
THEN (Select Left((GetDate()-1),11))--1
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 3)
THEN (Select Left((GetDate()-7),11))--7
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 2)
THEN (Select Left((GetDate()-6),11))--6
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 1)
THEN (Select Left((GetDate()-5),11))--5
ELSE '01/01/1900'
END)
and
(SELECT
CASE
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 7)
THEN (Select Left((GetDate()+2),11))
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 6)
THEN (Select Left((GetDate()+3),11))
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 5)
THEN (Select Left((GetDate()+4),11))
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 4)
THEN (Select Left((GetDate()+5),11))
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 3)
THEN (Select Left((GetDate()-1),11))
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 2)
THEN (Select Left((GetDate()),11))
WHEN (DatePart(DW,Left((GetDate()-1),11)) = 1)
THEN (Select Left((GetDate()+1),11))
ELSE '01/01/1900'
END)

I need something to determine MTD so I don't have to change it manually every 4-5 weeks. Since some months have 4 weeks and others have 5, I try to end the month based on how many days fall in that month. For example -- for the month of January, there were 5 weeks starting with 12/29/04 - 02/01/05. The first week of the month had 3 days in December but 4 days in January so I count it for January. If it has 4 days or more in the previous month, I count it for that pervious month's MTD calculations.

Any ideas on how I can do this using SQL?

MBeal

MuffinMan
Posting Yak Master

107 Posts

Posted - 2005-02-04 : 11:42:59

Wow, that's quite a SQL statement to retrieve the Wed-Tues data. Perhaps this is a little more easy to follow (and more efficient)


set nocount on

--  create table and fill with data
create table #MYTABLE (
	STAYDATE datetime not null primary key clustered, 
	DayOfWeek varchar(30) not null, 
	DayNumberOfWeek int not null
)

declare @date datetime
select @date = '2005-01-19'

while @date < '2005-02-05'
begin
	insert #MYTABLE (STAYDATE , DayOfWeek, DayNumberOfWeek) 
	select @date, datename(dw, @date), datepart(dw, @date)
	select @date = dateadd(d, 1, @date)
end

-- determine Wednesday's date for this week
declare @WednesdayDate datetime, @today datetime
-- return date as yyyy-mm-dd, without the 'time' value
select @today = convert(char(10), getdate(), 120)  

-- uncomment next line to change "today's" date for testing
--select @today = '2005-01-31'  -- this is a Tuesday

select @WednesdayDate = DateAdd(d, 0-(datepart(dw, @today)-4), @today)

-- if @today's date is less then or equal to @WednesdayDate 
	--then substract 7 days from @WednesdayDate
if @today <= @WednesdayDate
	select @WednesdayDate = DateAdd(d, -7, @WednesdayDate)

select * 
from #MYTABLE 
where STAYDATE >= @WednesdayDate 
  and STAYDATE < @today


drop table #MYTABLE

Results below:


STAYDATE                DayOfWeek      DayNumberOfWeek 
----------------------- -------------- --------------- 
2005-02-02 00:00:00.000 Wednesday      4
2005-02-03 00:00:00.000 Thursday       5

If you uncomment line 26 you get:


STAYDATE                DayOfWeek      DayNumberOfWeek 
----------------------- -------------- --------------- 
2005-01-26 00:00:00.000 Wednesday      4
2005-01-27 00:00:00.000 Thursday       5
2005-01-28 00:00:00.000 Friday         6
2005-01-29 00:00:00.000 Saturday       7
2005-01-30 00:00:00.000 Sunday         1

MuffinMan
Posting Yak Master

107 Posts

Posted - 2005-02-04 : 13:06:47

Ok, here's a solution for the MTD report. Create a stored procedure that will return the first and last day of the month. The first *day* of the month MUST be a Wednesday and the last *day* of the month MUST be a Tuesday (or the date the SP is run).


--***************************************************
--** START: create SP
--***************************************************
drop procedure #usp_MtdDates
go
create procedure #usp_MtdDates
	@TodaysDate datetime = NULL
	, @FirstWednesdayOfMonth datetime output
	, @LastTuesdayOfMonth datetime output
	, @Debug_Mode tinyint = 0

as


declare @date datetime

if @TodaysDate IS NULL
	Select @TodaysDate = GetDate()

select @date = convert(char(10), @TodaysDate , 120)

-- determine Wednesday's date for this week
declare @WednesdayDate datetime
select @WednesdayDate = DateAdd(d, 0-(datepart(dw, @date)-4), @date)
if @date <= @WednesdayDate
	select @WednesdayDate = DateAdd(d, -7, @WednesdayDate)

declare @FirstDayOfMonth datetime, @LastDayOfMonth datetime
select @FirstDayOfMonth = convert(char(7), @WednesdayDate, 120) + '-01'
select @LastDayOfMonth = DateAdd(d, - 1, DateAdd(m, 1, @FirstDayOfMonth))

select @WednesdayDate = DateAdd(d, 0-(datepart(dw, @FirstDayOfMonth)-4), @FirstDayOfMonth)


if @Debug_Mode = 1
begin
select 
@FirstDayOfMonth as FirstDayOfMonth
, DateName(dw, @FirstDayOfMonth) as FirstDayOfMonth_Day
, @LastDayOfMonth as LastDayOfMonth
, DateName(dw, @LastDayOfMonth) as LastDayOfMonth_Day
, @WednesdayDate as WednesdayDate
end

-- set the first day of the month to a 'Wednesday'
if DateDiff(d, @WednesdayDate, @FirstDayOfMonth) <= 4
	select @FirstDayOfMonth = @WednesdayDate

if @Debug_Mode = 1
begin
select 
@FirstDayOfMonth as FirstDayOfMonth
, DateName(dw, @FirstDayOfMonth) as FirstDayOfMonth_Day
, @LastDayOfMonth as LastDayOfMonth
, DateName(dw, @LastDayOfMonth) as LastDayOfMonth_Day
, @WednesdayDate as WednesdayDate
end

select @WednesdayDate = DateAdd(d, 0-(datepart(dw, @LastDayOfMonth)-4), @LastDayOfMonth)

-- set the last day of the month to a 'Tuesday'
select @LastDayOfMonth = DateAdd(d, -1, @WednesdayDate)


if @Debug_Mode = 1
begin
select 
@FirstDayOfMonth as FirstDayOfMonth
, DateName(dw, @FirstDayOfMonth) as FirstDayOfMonth_Day
, @LastDayOfMonth as LastDayOfMonth
, DateName(dw, @LastDayOfMonth) as LastDayOfMonth_Day
, @WednesdayDate as WednesdayDate
end

select @FirstWednesdayOfMonth = @FirstDayOfMonth, @LastTuesdayOfMonth = @LastDayOfMonth

if @date <= @LastTuesdayOfMonth
	select @LastTuesdayOfMonth = DateAdd(d, -1, @date)


return 0
go
--***************************************************
--** END  : create SP
--***************************************************


set nocount on

--***************************************************
--** START: create table and fill with data
--***************************************************
create table #MYTABLE (
	STAYDATE datetime not null primary key clustered, 
	DayOfWeek varchar(10) not null, 
	DayNumberOfWeek int not null
)

declare @date datetime
select @date = '2004-12-15'

while @date < '2005-02-25'
begin
	insert #MYTABLE (STAYDATE , DayOfWeek, DayNumberOfWeek) 
	select @date, datename(dw, @date), datepart(dw, @date)
	select @date = dateadd(d, 1, @date)
end
--***************************************************
--** END  : create table and fill with data
--***************************************************



--***************************************************
--** START: code that calls the SP and returns the MTD data
--***************************************************
declare @today datetime, @FirstDay datetime, @LastDay datetime
select @today = GetDate()

exec #usp_MtdDates @today
	, @FirstWednesdayOfMonth = @FirstDay OUTPUT
	, @LastTuesdayOfMonth = @LastDay OUTPUT	
	-- , @Debug_Mode = 1  /* uncomment to show debug code in SP */

---  example #1
select * 
from #MYTABLE 
where STAYDATE >= @FirstDay 
  and STAYDATE < DateAdd(d, 1, @LastDay)


---  example #2
select @today = '2005-02-01'
exec #usp_MtdDates @today
	, @FirstWednesdayOfMonth = @FirstDay OUTPUT
	, @LastTuesdayOfMonth = @LastDay OUTPUT	
	-- , @Debug_Mode = 1  /* uncomment to show debug code in SP */

select * 
from #MYTABLE 
where STAYDATE >= @FirstDay 
  and STAYDATE < DateAdd(d, 1, @LastDay)

---  example #3
select @today = '2005-02-02'
exec #usp_MtdDates @today
	, @FirstWednesdayOfMonth = @FirstDay OUTPUT
	, @LastTuesdayOfMonth = @LastDay OUTPUT	
	-- , @Debug_Mode = 1  /* uncomment to show debug code in SP */

select * 
from #MYTABLE 
where STAYDATE >= @FirstDay 
  and STAYDATE < DateAdd(d, 1, @LastDay)


---  example #4
select @today = '2005-02-20'
exec #usp_MtdDates @today
	, @FirstWednesdayOfMonth = @FirstDay OUTPUT
	, @LastTuesdayOfMonth = @LastDay OUTPUT	
	-- , @Debug_Mode = 1  /* uncomment to show debug code in SP */

select * 
from #MYTABLE 
where STAYDATE >= @FirstDay 
  and STAYDATE < DateAdd(d, 1, @LastDay)


drop table #MYTABLE
--***************************************************
--** END: code that calls the SP and returns the MTD data
--***************************************************

Here is the output:


**** ex.1 ***
STAYDATE      DayOfWeek  DayNumberOfWeek 
------------- ---------- --------------- 
2005-02-02    Wednesday  4
2005-02-03    Thursday   5


**** ex.2 ***
STAYDATE      DayOfWeek  DayNumberOfWeek 
------------- ---------- --------------- 
2004-12-29    Wednesday  4
2004-12-30    Thursday   5
2004-12-31    Friday     6
            . . .
  << other dates not shown >>
            . . .
2005-01-30    Sunday     1
2005-01-31    Monday     2


**** ex.3 ***
STAYDATE      DayOfWeek  DayNumberOfWeek 
------------- ---------- --------------- 
2004-12-29    Wednesday  4
2004-12-30    Thursday   5
2004-12-31    Friday     6
            . . .
  << other dates not shown >>
            . . .
2005-01-30    Sunday     1
2005-01-31    Monday     2
2005-02-01    Tuesday    3


**** ex.4 ***
STAYDATE      DayOfWeek  DayNumberOfWeek 
------------- ---------- --------------- 
2005-02-02    Wednesday  4
2005-02-03    Thursday   5
            . . .
  << other dates not shown >>
            . . .
2005-02-18    Friday     6
2005-02-19    Saturday   7

MBeal
Posting Yak Master

110 Posts

Posted - 2005-02-04 : 13:32:43

WOW... I need to look at this a little longer byt at first glance they look like they may work. If I have more questions I'll post them. Thanks again!

MBeal

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-04 : 14:06:07

Here is an interesting example to play with


Declare @tally table (n int identity(0,1), blah int)
Insert Into @tally
Select top 300 1 from master.dbo.syscolumns

--Select * from @tally

Declare @myTable table (cDate datetime, val int)
Insert Into @myTable
Select convert(datetime,'11/15/2004')+n, 5
From @tally t

--Select * from @myTable

Declare @wed int
Select @wed = (10-@@datefirst)%7+1
/*
Select cDate, datepart(dw,cDate), (datepart(dw,cDate)-@wed+7)%7, cDate - (datepart(dw,cDate)-@wed+7)%7
From @myTable
*/
Declare @WMY table (cDate datetime, WeekBegin datetime, MonthBegin datetime, YearBegin datetime)

	Insert Into @WMY
	Select
		cDate,
		WeekBegin,
		MonthBegin,
		YearBegin = dateadd(m,-(month(MonthBegin)+7)%12,MonthBegin)
	From
		(
		Select
			cDate,
			WeekBegin,
			MonthBegin = case when datediff(dy,WeekBegin,dateadd(m,1,WeekBegin - day(WeekBegin)+1)) < 4 
					then dateadd(m,1,WeekBegin - day(WeekBegin)+1)
					else WeekBegin - day(WeekBegin)+1 end
		From
			(
			Select 
				cDate, 
				WeekBegin = cDate - (datepart(dw,cDate)-@wed+7)%7
			From @myTable
			) weeks
		) months


Select WeekBegin, WTD = sum(Val) 
From @myTable A
Inner Join @WMY B
On A.cDate = B.cDate
Group By WeekBegin

Select MonthBegin, MTD = sum(Val) 
From @myTable A
Inner Join @WMY B
On A.cDate = B.cDate
Group By MonthBegin

Select YearBegin, YTD = sum(Val) 
From @myTable A
Inner Join @WMY B
On A.cDate = B.cDate
Group By YearBegin

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources