Select count one-per-week in range

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 count one-per-week in range

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-12 : 05:30:07

Hi,

I'm apologizing in advance for using someone else's brainpower on what is basically a logic problem rather than a creative use of T-SQL, but it's doing me head in.

I have a table which contains information on bookings for runs of advertising. This includes a BookingStart dateTime field and a BookingEnd datetime field. Between BookingStart and BookingEnd an ad is considered to be "live".

What I need to do is construct a query which, given a start date and and end date returns a count of all the records which were "live" between those dates and - here's the catch - each week must count separately. In other words if my query asks for data across four weeks, and an ad was "live" for two of those weeks, it should return a count of two.

Any help much appreciated.

Cheers,
Matt

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-12 : 11:41:45

Do you need to count full weeks? Partial weeks? Weeks in which the ad was live at least 1 day?

Something like this could get you started if you want to only count full weeks:

WITH Bookings(AdID, AdStart, AdEnd) AS
(
SELECT AdID,
(Case When BookingStart > RangeStart Then BookingStart Else RangeStart End),
(Case When BookingEnd < RangeEnd Then BookingEnd Else RangeEnd End)
FROM BookingsTable
WHERE BookingEnd >= RangeStart
AND BookingStart <= RangeEnd
)
SELECT SUM(DATEDIFF(dd,AdStart,AdEnd)/7)
FROM Bookings

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:52:08

This?

-- Prepare sample data
DECLARE	@Sample TABLE (AdID INT, AdStart DATETIME, AdEnd DATETIME)

INSERT	@Sample
SELECT	1, '20080118', '20080130' UNION ALL
SELECT	2, '20080111', '20080212'

-- Prepare user supplied parameters
DECLARE	@Start DATETIME,
	@End DATETIME

SELECT	@Start = '20080110',
	@End = '20080209'

-- Show the expected output
SELECT		ISO_YEAR_WEEK_NO,
		COUNT(AdID) AS LiveAds
FROM		(
			SELECT DISTINCT	f.ISO_YEAR_WEEK_NO,
					s.AdID
			FROM		@Sample AS s
			RIGHT JOIN	F_TABLE_DATE(@Start, @End) AS f ON f.DATE BETWEEN s.AdStart AND s.AdEnd
		) AS x
GROUP BY	ISO_YEAR_WEEK_NO
ORDER BY	ISO_YEAR_WEEK_NO

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 13:12:28

[code]SELECT f.ISO_YEAR_WEEK_NO,
COUNT(DISTINCT s.AdID)
FROM @Sample AS s
RIGHT JOIN F_TABLE_DATE(@Start, @End) AS f ON f.DATE BETWEEN s.AdStart AND s.AdEnd
GROUP BY f.ISO_YEAR_WEEK_NO
ORDER BY f.ISO_YEAR_WEEK_NO[/code]The function F_TABLE_DATE can be found here on SQLTeam.

E 12°55'05.25"
N 56°04'39.16"

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-13 : 10:49:32

Thanks for the heads up on that F_TABLE_DATE function! That will come in very handy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources