Week of Month Problem. - 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

General SQL Server Forums

New to SQL Server Programming

Week of Month Problem.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-19 : 10:45:06

I have the following table which I derived from MJV's Calendar table script.

SET  ANSI_NULLS ON;
GO
SET  QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[RedFridays] (
   [DATE_ID] int NOT NULL,
   [DATE] datetime NOT NULL,
   [YEAR] smallint NOT NULL,
   [MONTH] tinyint NOT NULL,
   [DAY_OF_WEEK] tinyint NOT NULL,
   [REDFRIDAY] bit NULL,
   [RFMonth] tinyint NULL,
   [ForecastSales] money NULL,
   [RFWeek] decimal (6, 1) NULL,
   CONSTRAINT [PK__tblRedFridayAllD__30F848ED] PRIMARY KEY
      CLUSTERED
      ([DATE] ASC)
      WITH FILLFACTOR = 100
      ON [PRIMARY]
)
ON [PRIMARY];
GO

As I've mentioned in previous posts, my company doesn't track statistics by month, but rather by Red Friday Months. The RFMonth represents the period of the year that the particular day belongs to. A stored procedure I wrote pulls the Red Fridays from our ERP system and updates my table accordinglyi. I need to create a stored procedure which would do the following:

1. Determine the previous Red Friday
2. Count the number of Mondays between the current date and the past red friday
3. Generate values like the following:
200809.1 -- Week 1 of the 9th period of 2008
200809.2
200809.3
200809.4

Any ideas?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-19 : 11:41:43

one change. I'm going to use Varchar (8) for the RFWeek Field.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-19 : 12:22:38

1. select top 1 * from RedFridays where DATE < getdate() and REDFRIDAY = 1
2. select count(*) from RedFridays where DATE between dateFromFirstQuery and getdate() and day_of_week = 1 -- 1 or other number for monday
3. you'll have to provide some sample data for that one...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-19 : 14:09:12

Just find the first Monday on or after the start of the RedFriday period, and use datediff to calculate the week number:

select RedFridayWeekNumber = (datediff(dd,,@FirstMondayofRedFriday,getdate())/7)+1

-- Find first Monday on or after a given date
select
	a.DT,
	FirstMondayMethod1 =
		--Function F_START_OF_WEEK here:
		--http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
		dbo.F_START_OF_WEEK(dateadd(dd,6,a.DT),2),
	FirstMondayMethod2 =
		dateadd(dd,(datediff(dd,-53690,dateadd(dd,6,a.DT))/7)*7,-53690)
from
	( --Test Dates
	select DT=convert(datetime,'20080905')	union all
	select DT=convert(datetime,'20080906')	union all
	select DT=convert(datetime,'20080907')	union all
	select DT=convert(datetime,'20080908')	union all
	select DT=convert(datetime,'20080909')	union all
	select DT=convert(datetime,'20080910')	union all
	select DT=convert(datetime,'20080911')	union all
	select DT=convert(datetime,'20080912')
	) a
order by
	a.DT

Results:
DT                       FirstMondayMethod1       FirstMondayMethod2
-----------------------  -----------------------  -----------------------
2008-09-05 00:00:00.000  2008-09-08 00:00:00.000  2008-09-08 00:00:00.000
2008-09-06 00:00:00.000  2008-09-08 00:00:00.000  2008-09-08 00:00:00.000
2008-09-07 00:00:00.000  2008-09-08 00:00:00.000  2008-09-08 00:00:00.000
2008-09-08 00:00:00.000  2008-09-08 00:00:00.000  2008-09-08 00:00:00.000
2008-09-09 00:00:00.000  2008-09-15 00:00:00.000  2008-09-15 00:00:00.000
2008-09-10 00:00:00.000  2008-09-15 00:00:00.000  2008-09-15 00:00:00.000
2008-09-11 00:00:00.000  2008-09-15 00:00:00.000  2008-09-15 00:00:00.000
2008-09-12 00:00:00.000  2008-09-15 00:00:00.000  2008-09-15 00:00:00.000

(8 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources