select this week and last months equivilent week

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 2005 Forums

Transact-SQL (2005)

select this week and last months equivilent week

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-03 : 04:00:42

hi guys,
happy new year..

Can anyone assist me with first ,
selecting the date range this week. ( which will change each week, ) monday being the first day of the week.

then the same but for last month..

so I can see data for week 1 in jan and dec

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-03 : 04:08:55

Common practice to achieve this would be to create a calendar table where you can stipulate exactly where each week starts and ends in the month and perhaps each month in the year etc.

Populate the table accordingly and use it in your queries going forward.

An example would be something like this:

CREATE TABLE [dbo].[STG_Calendar](
[CalendarDate] [datetime] NULL,
[MONTH] [varchar](9) NULL,
[DAY_OF_WEEK] [varchar](3) NULL,
[HOLIDAY] [varchar](20) NULL,
[HOLIDAY_CODE] [varchar](1) NULL,
[YEAR_WEEK] [int] NULL,
[YEAR_MONTH] [int] NULL,
[MONTH_NUMBER] [int] NULL,
[STOCK_MONTH_END] [bit] NULL,
[DEBTORS_MONTH_END] [bit] NULL,
[FinYearWeek] [int] NULL,
[FinYearMonth] [int] NULL,
[FinYear] [int] NULL
)

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-03 : 04:16:44

really ? I am suprised at this, I thought using the sql date functions would be a better idea as it means a table does not need to be constantly updated..

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-03 : 04:20:31

Yes,
But how do you break up a 31 or 30 day month into equal weeks?

That is the main problem - from a business point of view these kinds of things need to be declared before hand.

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-03 : 04:30:13

IF I can show this months data so far by doing :
convert(varchar,depositdate,23) >=DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND convert(varchar,depositdate,23) <= convert(varchar,getdate(),23)

could I show last months equivilent with a similar query ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-03 : 04:49:52

I can work out the first day of last month using :
select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)

do you know how I can work out todays date , but last months month ?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-03 : 11:32:39

I took a stab at it. Not too pretty but it should return the first day of the previous months equivalent week (last week of the month if seed month days > previous month days):

declare @dt datetime,
@mstart datetime,
@lastmstart datetime,
@lastmend datetime,
@weeks int

set @dt = '20080127'

select
@mstart =
Cast(Cast(datepart(year, @dt) as varchar)
+'.'+
Cast(datepart(month, @dt) as varchar)
+'.1' as datetime),
@lastmstart =
Cast(Cast(datepart(year, dateadd(m, -1, @dt)) as varchar)
+'.'+
Cast(datepart(month, dateadd(m, -1, @dt)) as varchar)
+'.1' as datetime),
@lastmend = Cast(Cast(datepart(year, @dt) as varchar)
+'.'+
Cast(datepart(month, @dt) as varchar)
+'.1' as datetime)-1

select
@weeks = case
when datepart(week, @lastmstart)+
datepart(week, @dt)-
datepart(week, @mstart)>datepart(week, @lastmend)
then datepart(week, @lastmend)
when datepart(week, @lastmend) = 53
then datepart(week, @lastmstart)+
datepart(week, @dt)-
datepart(week, @mstart)-53
else datepart(week, @lastmstart)+
datepart(week, @dt)-
datepart(week, @mstart)
end

select dateadd(week, @weeks, Cast(Cast(datepart(year, @dt) as varchar)
+'.1.1' as datetime)-1)-1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources