selecting missing records - 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)

selecting missing records

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-28 : 00:22:52

Hi,

I'm having a problem with this simple query that looks like it might get super complicated for the extra bit of functionality I need to add. I have posted its current state below. The problem is that for days there are no releases, I need to have my attention brought to this.

For instance below, the query would not bring back the date 1/2/2005 when the fact there are 0 records for that date is something the query needs to return.

Any suggestions on how to approach this?

Thanks alot once again ! :)

mike123

tblMedia structure

mediaID / DateRelease
1 1/1/2005
2 1/3/2005
3 1/3/2005
4 1/4/2005
5 1/5/2005

CREATE PROCEDURE dbo.select_updatesByDay_dateRelease

(
@dateStart as smalldatetime,
@dateEnd as smalldatetime
)

AS SET NOCOUNT ON

SELECT dateRelease, count(*) as totalReleases from tblMedia1 WHERE (dateRelease >= @dateStart AND dateRelease <= @dateEnd) GROUP BY dateRelease ORDER BY dateRelease

GO

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 01:02:42

Two ways of going about this from what I can see:
1. Create a table of dates you expect to have releases. Do a simple join query to see where there are gaps
2. Create a procedure that scans the table in date order and spits out an error if there are missing dates.
The way you go about it is up to you, but I'd probably choose the former because it's a cleaner solution.
HTH,

Tim

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-28 : 18:58:03

Hi Timmy,

I'm going to be querying usually by month and would ideally like something like

date / totalReleases

1/1/2005 / 0

brought back when a date is found with no records associated with it. How can I scan the table in date order and spit out an error if there are missing dates.? Or instead of an error bring back that date with a 0 as total ?

Thanks for the reply ..
mike123

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 19:07:49

The central issue here is this - how does SQL Server know the dates are missing? You need to instruct it to either a)compare the tblMedia table with a list of known dates, or b)Scan the table manually spitting out missing dates.
Going with my second choice, you could do the following

Declare @today DateTime

	SET @today = Convert(datetime, '1-Jan-2005')
	WHILE @today < '1-Jan-2006'
		BEGIN
			IF NOT EXISTS(SELECT mediaID FROM tblMedia WHERE releaseDate = @today)
				Print 'Release missing for date ' + Convert(varchar, @today, 101)		
			SET @today = DateAdd(d, 1, @today)
		END

If you tell us how you're trying to implement this (i.e. how do you want this info returned/displayed), then perhaps we can suggest a better solution.

Tim

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-07-28 : 19:55:05

Timmy,

I see what your saying, this is what I have so far ... I would like every single date between dateStart and dateEnd brought back and the associated "count" of records, whether 1,2,3 etc... or most importantly - 0

I don't think I want an error printed, rather just bring back a normal row with a 0 count. Do you think this is possible?

Thanks very much for your help
mike123

CREATE PROCEDURE dbo.select_updatesByDay_dateRelease

(
@dateStart as smalldatetime,
@dateEnd as smalldatetime
)

AS SET NOCOUNT ON

SELECT dateRelease, count(*) as totalReleases from tblMedia WHERE (dateRelease >= @dateStart AND dateRelease <= @dateEnd) GROUP BY dateRelease ORDER BY dateRelease

GO

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-28 : 20:19:19

Sure is:

CREATE PROCEDURE dbo.select_updatesByDay_dateRelease

(
	@dateStart as smalldatetime,
	@dateEnd as smalldatetime
)

AS SET NOCOUNT ON

	DECLARE @d 		DATETIME
	DECLARE	@output TABLE (dateRelease DATETIME, totalReleases INT)

	SET @d = @dateStart
	WHILE @d <= @dateEnd
		BEGIN
			INSERT INTO @output (dateRelease, totalReleases)
				SELECT 	@d, (SELECT Count(mediaID) FROM	tblMedia WHERE 	dateRelease = @d) as totalReleases
			SET @d = DATEADD(d, 1, @d)
		END

	SELECT * FROM @output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources