Date Query Problem

Hi All,
I have a table that stores IDs and dates:
CREATE TABLE table_X
(
StationID nvarchar(10) NOT NULL,
FullDate datetime NOT NULL
)
In the fulldate field there are dates one for each day and station.
sometimes a station does not report the date to this table. Sometimes it
doesn't do it for a series of days. I want to write a query that can give
me all of the days that are missing or at the very least the start of the
missing period and the end of the missing period. I tcan think of how to do
this to save my life!!!!!
Any help is greatly appreciated
Calvin XUse a left join against a calendar table.
http://www.aspfaq.com/2519
"Calvin X" <spam freerobotno_spammingdrone@.rotsnail.com> wrote in message
news:u2WJwXBUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table that stores IDs and dates:
> CREATE TABLE table_X
> (
> StationID nvarchar(10) NOT NULL,
> FullDate datetime NOT NULL
> )
>
> In the fulldate field there are dates one for each day and station.
> sometimes a station does not report the date to this table. Sometimes it
> doesn't do it for a series of days. I want to write a query that can give
> me all of the days that are missing or at the very least the start of the
> missing period and the end of the missing period. I tcan think of how to
> do this to save my life!!!!!
>
> Any help is greatly appreciated
> Calvin X
>|||CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
IsoDate varchar(10),
isWday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
Now, let's populate it with data. Let's assume we want 30 years of data,
from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20000101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
GO
CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
GO
INSERT Calendar(dt)
SELECT DATEADD(DAY, Number, '20000101')
FROM dbo.Numbers
WHERE Number <= 10957
ORDER BY Number
UPDATE dbo.Calendar SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
IsoDate = CONVERT(varchar(10),IsoDate,112),
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt)
GO
SELECT * from TableX
WHERE FULLDATE NOT IN
(SELECT ISodate from Calender)
--If you want all the values , also the NUL values -->
SELECT * from TableX X
LEFT JOIN
Calender C
ON x.Fulldate = C.IsoDate
--best thing is t convert the date to isodate
CONERT(varchar(10),Fulldate,112)
If you still got problem raise a hand or send me an email, i will postit
back to the ng if solved.
HTH, Jens Suessmeyer.
"Calvin X" <spam freerobotno_spammingdrone@.rotsnail.com> schrieb im
Newsbeitrag news:u2WJwXBUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table that stores IDs and dates:
> CREATE TABLE table_X
> (
> StationID nvarchar(10) NOT NULL,
> FullDate datetime NOT NULL
> )
>
> In the fulldate field there are dates one for each day and station.
> sometimes a station does not report the date to this table. Sometimes it
> doesn't do it for a series of days. I want to write a query that can give
> me all of the days that are missing or at the very least the start of the
> missing period and the end of the missing period. I tcan think of how to
> do this to save my life!!!!!
>
> Any help is greatly appreciated
> Calvin X
>|||Wow, that's some pretty good cut & paste action, however there are some
inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
Number <= 10957, and you insert the same dates twice by using "the old
fashioned way" AND using the Numbers table).
Not that my articles are infallible, but why not just point the user at the
source, which is far less prone to transcription errors. Never mind the
whole giving credit where credit is due part...
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE dbo.Calendar
> (
> dt SMALLDATETIME NOT NULL
> PRIMARY KEY CLUSTERED,
> IsoDate varchar(10),
> isWday BIT,
> isHoliday BIT,
> Y SMALLINT,
> FY SMALLINT,
> Q TINYINT,
> M TINYINT,
> D TINYINT,
> DW TINYINT,
> monthname VARCHAR(9),
> dayname VARCHAR(9),
> W TINYINT
> )
> GO
> Now, let's populate it with data. Let's assume we want 30 years of data,
> from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:|||I always do quote the sources where the solution is from, i think evervyone
know the great gainers in here with Aaron and Tibor (just to names these
two)
Whatever, programming always has to do with C&P and just setting the right
variables / names and values.
e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957,
Thank you for correcting me.
Not that my articles are infallible, but why not just point the user at the
> source, which is far less prone to transcription errors. Never mind the
> whole giving credit where credit is due part...
Just as i said above. I dont know the skill level of the original poster,
so trying to help him with giving him the right direction and code samples
wont hurt somebody ego as far as we are doing this all in here just-4-help.
Jens Suessmeyer.
"AB - MVP" <ten.xoc@.dnartreb.noraa> schrieb im Newsbeitrag
news:evM73kBUFHA.952@.TK2MSFTNGP10.phx.gbl...
> Wow, that's some pretty good cut & paste action, however there are some
> inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957, and you insert the same dates twice by using "the old
> fashioned way" AND using the Numbers table).
> Not that my articles are infallible, but why not just point the user at
> the source, which is far less prone to transcription errors. Never mind
> the whole giving credit where credit is due part...
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||I thought you were a new member and now I realize what AB means. Good you ar
e
back.
AMB
"AB - MVP" wrote:

> Wow, that's some pretty good cut & paste action, however there are some
> inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957, and you insert the same dates twice by using "the old
> fashioned way" AND using the Numbers table).
> Not that my articles are infallible, but why not just point the user at th
e
> source, which is far less prone to transcription errors. Never mind the
> whole giving credit where credit is due part...
>
>
>
> "Jens Sü?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
>
>|||> Just as i said above. I dont know the skill level of the original poster,
> so trying to help him with giving him the right direction and code samples
> wont hurt somebody ego as far as we are doing this all in here
> just-4-help.
I understand, my main point was not that my feelings were hurt because my
article was chopped up and not credited (that happens all the time), it was
that the sloppiness of it will likely yield even further confusion / errors.|||>I thought you were a new member and now I realize what AB means. Good you
>are
> back.
Thanks.
It doesn't take me forever to see past thoughtless statements by an
ignoramus (or multiple). Not that I have any more respect than I did a w
ago, I just off and then increase the size of my twitfile.
A|||I dont wanna argue with you on that,
perhaps we meet sometime and drink this out. ;-)
Just to say, Hats off for the work youve done.. :-)
Jens
"AB - MVP" <ten.xoc@.dnartreb.noraa> schrieb im Newsbeitrag
news:OyqUOtBUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> I understand, my main point was not that my feelings were hurt because my
> article was chopped up and not credited (that happens all the time), it
> was that the sloppiness of it will likely yield even further confusion /
> errors.
>|||You can get the start of the gaps with:
SELECT A.* FROM table A LEFT JOIN table B ON A.dateField=B.dateField-1
WHERE B.primaryKey IS NULL
or the end of the gaps with:
SELECT A.* FROM table A LEFT JOIN table B ON A.dateField=B.dateField+1
WHERE B.primaryKey IS NULL
It does have the side effect of including the first record and won't be as
efficient as using a calendar.sql