Creating unique weekly fields - 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)

Creating unique weekly fields

Author

Topic

BIGGY
Starting Member

17 Posts

Posted - 2006-04-26 : 18:42:51

This code will be for a subreport that will drive a Crystal report. I'm not as familiar with SQL, but perhaps this is much easier than I think. I need to calculate weekly total sales for a field:
INTran.QTY
The only time I need to calculate weekly fields is if for the current month starting on the first of the month and ending on each Saturday. So going back to March for example... 3/1/2006 falls on a Wednesday. That week would then end on Saturday, 3/4/2006, and so on for each week e.g.

Week1: 3/1/2006 - 3/4/2006
Week2: 3/5/3006 - 3/11/2006
Week3: 3/12/2006 - 3/18/2006
Week4: 3/19/2006 - 3/25/2006
Week5: 3/26/2006 - 3/31/2006

I understand that to calculate the days correctly through saturday that I need to use: select datepart( weekday, getdate() )

If it helps anyone, I'm a little better in VB and will post the code below in VB as to what I'm looking to do if anyone is good at both:

***
FirstDay := date(year(currentdate) , month(currentdate) , 01 ); --Sets whatever month we are in variable to first of month
DaysToEnd := 7 - dayofweek(firstday); --dayofweek is similar to datepar function
ToDate := FirstDay + DaysToEnd;
***

Now on the above I would simply run a: Select INTran.QTY Where INTran.TranDate is between FirstDay and ToDate. And do this for each week in the current month. Hope that's somewhat clear if anyone is able to help!

Thanks!
Mike

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 20:46:21

This is easy to do if you have a date table.

Don't have a date table? You can get the Mother of All Date Tables function, F_TABLE_DATE, from the Script Library forum:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


declare @start datetime, @end datetime
select @start = '2006-03-01'
select @end = '2006-03-31'

-- Table for test data
declare @INTran table (TranDate datetime not null, QTY int not null)

-- Load test data
insert into @INTran
select
	DATE,
	-- Generate a random number from 0 to 999
	QTY = abs(convert(int,convert(varbinary(20),newid()))%1000)
from
	dbo.F_TABLE_DATE(@start,@end)
where
	-- No sunday sales
	DAY_OF_WEEK <> 1



-- Return results grouped by week
Select
	FROM_DATE = min(a.DATE),
	TO_DATE = max(a.DATE),
	QTY  = sum(isnull(b.QTY,0))
from
	dbo.F_TABLE_DATE(@start,@end) a
	left join
	@INTran b
	on a.DATE = b.TranDate
group by
	a.WEEK_STARTING_SUN_SEQ_NO
order by
	a.WEEK_STARTING_SUN_SEQ_NO

Results:


FROM_DATE                TO_DATE                  QTY
-----------------------  -----------------------  ----------
2006-03-01 00:00:00.000  2006-03-04 00:00:00.000  2711
2006-03-05 00:00:00.000  2006-03-11 00:00:00.000  3594
2006-03-12 00:00:00.000  2006-03-18 00:00:00.000  2639
2006-03-19 00:00:00.000  2006-03-25 00:00:00.000  2555
2006-03-26 00:00:00.000  2006-03-31 00:00:00.000  2713

(5 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources