Date Issue

[obrienkev]

Hi all,

I have a table contains daily log information. I need to find out if any rows missing out in a given date range.

Is it possible to find out in a single select statement?

Scenario:

FileDate ID
----- ------
10/May/2007 3
12/May/2007 3
13/May/2007 3

So I want to display the row as "File Not Existing" for 11/May/2007

How can I do this?

Thanks.

[frozenmist]

Hi obrienkev,

Do you want to display the rows if the rows are present?
If u dont want then you can use count .
What you can do is take the count of rows with that particular date.
If the count is zero then you can display that such a row is not present .
You can use the case construct for this.
eg:

select case when count(*) = 0 then 'ROW NOT PRESENT '
else 'ROW PRESENT ' end as status from table where FILEDATE=[I]<date>[/I]

Is this your requirement? Hope you understood the approach.

Cheers

[obrienkev]

I want to display rows that are present and rows that aren't.

For those that aren't present I want to display a message such as "No Data"

Any ideas?

[frozenmist]

I want to display rows that are present and rows that aren't.

For those that aren't present I want to display a message such as "No Data"

Any ideas?

Hi obrienkev,
Are you using this in a stored proc or something. If not ,I dont get why you want to display not present.
If the select returns nothing then its implied that no row is present right.
Can please give an input on why u need it?

Cheers

[obrienkev]

ok. The output is displayed in a webpage.

So for each fileDate row the relevant data is displayed like so...

FileDate ID
04/May/2007 3
05/May/2007 3
08/May/2007 3
09/May/2007 3

But 6th and 7th had no entries so should be displayed as...

FileDate ID
04/May/2007 3
05/May/2007 3
no entry
no entry
08/May/2007 3
09/May/2007 3

[obrienkev]

tried below but it only returns ONE missing date after a date is in a row...

SELECT DISTINCT t.FileDate + 1 AS missingDate
FROM Summary AS t LEFT OUTER JOIN
Summary AS d ON t.FileDate + 1 = d.FileDate
WHERE     (d.FileDate IS NULL)

so for example 11th and 12th are missing below and only the 11th is returned.

FileDate ID
----- ------
10/May/2007 3
13/May/2007 3
14/May/2007 3

[obrienkev]

Here's my stored procedure...

ALTER PROCEDURE [mytest]
   @newDate Datetime,
   @StartDate Datetime,
   @EndDate Datetime
AS
SET NOCOUNT ON

CREATE table #temp
(
myID int IDENTITY,
myDate Datetime
)

SELECT @StartDate = MIN(FileDate) FROM Summary
SELECT @EndDate = MAX(FileDate) FROM Summary

SET @newDate = @startDate

WHILE(@newDate <= @EndDate)
BEGIN
  SELECT @newDate = DATEADD(d, 1, @StartDate);
  INSERT INTO #temp(myDate) VALUES(@newDate);
END

How do I compare @newDate to the FileDate?