Sum by year and week number restrictions

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)

Sum by year and week number restrictions

Author

Topic

Adde
Starting Member

9 Posts

Posted - 2007-03-28 : 16:05:08

Any suggestions how to create a query on the following table...

PRODUCTS
----------------------------
RegDate smalldatetime,
InvDate smalldatetime,
NetWeight int,
InpValue int

...that generates a result that look like this:

Year, Week, TotNetWeight, TotInpValue
---------------------------------------------
2007, 2, 100, 250
2007, 1, 200, 500
2006, 52, 300, 750
2006, 51, 200, 500

Date restriction on each row:
RegDate <= (Year,Week) AND InvDate > (Year,Week)

Thanks in advance,
Adde

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 16:09:38

Do you have some examples of the source data too?

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-28 : 17:57:31

Also, what is your definition of week?

That is something that varies from one organization to the next, so you need to be specific about how week is defined.

CODO ERGO SUM

Adde
Starting Member

9 Posts

Posted - 2007-03-29 : 01:43:54

quote:
Originally posted by Peso

Do you have some examples of the source data too?

Peter Larsson
Helsingborg, Sweden

RegDate, InvDate, NetWeight, InpValue
2007-01-01, 2007-01-03, 50, 150
2007-01-02, 2007-01-04, 50, 100
...
2006-12-01, 2006-12,03, 100, 100
...

(Date format: YYYY-MM-DD)

Adde
Starting Member

9 Posts

Posted - 2007-03-29 : 01:53:09

quote:
Originally posted by Michael Valentine Jones

Also, what is your definition of week?

That is something that varies from one organization to the next, so you need to be specific about how week is defined.

CODO ERGO SUM

I've created a function that returns a week number for provided smalldatetime input variable, and I'm using this function the same way I'm using the DATEPART(yyyy, '2007-01-01') command.

SELECT dbo.fncIsoWeek('2007-01-01') ==> 1
SELECT dbo.fncIsoWeek('2007-01-08') ==> 2
...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 02:03:52

There is a flaw with this approach. You can actually have the same weeknumber (52 or 53) both for Jan 1 and Dec 31, but they belong to different years.

SELECT		DATEPART(YEAR, RegDate) AS RegYear,
		dbo.fncIsoWeek(RegDate) AS RegWeek,
		SUM(NetWeight) AS TotNetWeight,
		SUM(InpValue) AS TotInpValue
FROM		Table1
GROUP BY	DATEPART(YEAR, RegDate),
		dbo.fncIsoWeek(RegDate)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources