Hi experts:
Im having trouble trying to calculate MonthBegin and MonthToDate.
For this task Im always looking at the previous week, if the previous week has two months in it then:

MB = The 1st of the current month
MTD = Last Sunday of the current week
else
MB = The 1st of last month.
MTD = Last Sunday of the previous week

Example:
If I run it on 2007-11-07 then
MB = 2007-11-01 and MTD = 2007-11-04

If I run it on 2007-08-01 then
MB = 2007-07-01 and MTD = 2007-07-29

I keep trying and trying but not going anywhere, will really appreciate any help or tips!

--Beginning of this month is
select dateadd( month, datediff( month, 0, getdate() ), 0 )
-- Results 2007-12-01 00:00:00.000

--Beginning of this week is
select dateadd( week, datediff( week, 0, getdate() ), 0 )
-- Results 2007-12-03 00:00:00.000 which is a monday, so need to dial it back a couple of days.

Previous Saturday is
--Beginning of this week is
select dateadd( week, datediff( week, -2, getdate() ) - 1, -2 )

HTH
David

0

Oneill0003Author Commented:

dtodd:
Thanks for the responce, i know what the syntax to get MB and WB is, what i need is help in figuring out how to calculate those dates by looking at the previous week and checking to see if the previous week has 2 months in it.

Look at the example provided.

IF previous week has two months in it then
MB = The 1st of the current month
MTD = Last Sunday of the current week

else
MB = The 1st of last month.
MTD = Last Sunday of the previous week

end IF

If I run it on 2007-11-07 then
MB = 2007-11-01 and MTD = 2007-11-04

If I run it on 2007-08-01 then
MB = 2007-07-01 and MTD = 2007-07-29

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

DECLARE @dta AS datetimeSELECT @dta='2007-11-07'SELECT CASE WHEN month(@dta- CASE datename(weekday,@dta) WHEN 'Monday' THEN 7 WHEN 'Tuesday' THEN 8 WHEN 'Wednesday' THEN 9 WHEN 'Thursday' THEN 10 WHEN 'Friday' THEN 11 WHEN 'Saturday' THEN 12 WHEN 'Sunday' THEN 13 END) <>month(@dta- CASE datename(weekday,@dta) WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 5 WHEN 'Sunday' THEN 7 END) THEN cast(Year(@dta) AS varchar)+'-'+right('0'+cast(month(@dta) AS varchar),2)+'-01' ELSE cast(year(@dta- CASE datename(weekday,@dta) WHEN 'Monday' THEN 7 WHEN 'Tuesday' THEN 8 WHEN 'Wednesday' THEN 9 WHEN 'Thursday' THEN 10 WHEN 'Friday' THEN 11 WHEN 'Saturday' THEN 12 WHEN 'Sunday' THEN 13 END) AS varchar)+'-'+right('0'+cast(month(@dta-CASE datename(weekday,@dta) WHEN 'Monday' THEN 7 WHEN 'Tuesday' THEN 8 WHEN 'Wednesday' THEN 9 WHEN 'Thursday' THEN 10 WHEN 'Friday' THEN 11 WHEN 'Saturday' THEN 12 WHEN 'Sunday' THEN 13 END) AS varchar),2)+'-01' END AS MB, convert(varchar(10),@dta- CASE datename(weekday,@dta) WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 5 WHEN 'Sunday' THEN 7 END,121) AS MTD

Zberteoc:
I have been trying your code and it seems to work just fine, can you give me some explanations of what you are doing, and will these code work for any future year?

I'm planning on making these into a function so i can just call it from my dts package.

Thanks a lot for the help.

0

Oneill0003Author Commented:

Hey Zberteoc:
Your code work perfect do you think you can explain a little what the code is doing, i follow part of it but will like a better explanation.

it's been a while since I wrote the code but basically I used the SQL date functions to calcuate and compare the given date with a date from previus week.

- datename(weekday,@dta) function returns the name of the day (Monday, Tuesday) for the date given if the first parameter is weekday. What the case does is turning every weekday name into a number from 1 to 7 so you can calculate the difference.

- month(date) returns the month number like 1 for January, 2 for Feb, etc.

I compare the month of the given date with the month for the monday of the previous week. If they are the same return one thing, according to your requirements, if they differ return something else.

When you have an expression like date1 - n by default SQL server returns a date2 which is from n days before day1. So, for instance, if is the given date is on Tuesday the date for the Monday previous week will be date - 8, thats why:

month(@dta - CASE WHEN 'Moday' then 7 WHEN 'Tuesdat' then 8...) gives you the date for the previous moday from where I compare the month with the month of the given date. This is how I check if the last week had days in different months.

I follow the same logic to cover all the cases and to pull out the dates you needed.

I hope I was clear enough.

0

Oneill0003Author Commented:

Clear enough!

Thanks!

0

Featured Post

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

--Beginning of this month is

select dateadd( month, datediff( month, 0, getdate() ), 0 )

-- Results 2007-12-01 00:00:00.000

--Beginning of this week is

select dateadd( week, datediff( week, 0, getdate() ), 0 )

-- Results 2007-12-03 00:00:00.000 which is a monday, so need to dial it back a couple of days.

hth

David