finding missing dates in a sequence

I have 200 tables that have data entered into the daily. I need to identify
all dates that are missing from the tables.
for example:
date column1 column2
1/1/2005 5 20
2/1/2005 67 35
4/1/2005 3 17
5/1/2005 9 6
8/1/2005 7 99
I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
can you please assist?
jayi should have also said i do not want to have a lookup table, ie a table tha
t
permenantly stores dates.
cheers jay
jay
"jay" wrote:

> I have 200 tables that have data entered into the daily. I need to identi
fy
> all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
> --
> jay|||How about a temporary lookup table?
DROP TABLE #tmp_data
CREATE TABLE #tmp_data ( entry_date DATETIME PRIMARY KEY, col1 INT NOT NULL,
col2 INT NOT NULL )
SET NOCOUNT ON
INSERT INTO #tmp_data VALUES ( '20050101', 5, 20 )
INSERT INTO #tmp_data VALUES ( '20050102', 67, 35 )
INSERT INTO #tmp_data VALUES ( '20050104', 3, 17 )
INSERT INTO #tmp_data VALUES ( '20050105', 9, 6 )
INSERT INTO #tmp_data VALUES ( '20050108', 7, 99 )
DROP TABLE #tmp_lookup
CREATE TABLE #tmp_lookup ( entry_date DATETIME PRIMARY KEY )
DECLARE @.i INT
DECLARE @.stop INT
DECLARE @.min_date DATETIME
DECLARE @.max_date DATETIME
-- Initialise
SET @.i = 0
-- Calculate the range of dates to be added to the temp lookup table
SELECT
@.min_date = MIN( entry_date ),
@.max_date = MAX( entry_date )
FROM #tmp_data
SET @.stop = DATEDIFF( day, @.min_date, @.max_date ) + 1
-- Add the dates to the lookup table
WHILE @.i < @.stop
BEGIN
INSERT INTO #tmp_lookup SELECT DATEADD( day, @.i, @.min_date )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- Show the missing values
SELECT t1.*
FROM #tmp_lookup t1
LEFT JOIN #tmp_data t2 ON t1.entry_date = t2.entry_date
WHERE t2.entry_date IS NULL
This could even be wrapped in a parameterized stored procedure.
Let me know how you get on.
Damien
"jay" wrote:
> i should have also said i do not want to have a lookup table, ie a table t
hat
> permenantly stores dates.
> cheers jay
> --
> jay
>
> "jay" wrote:
>|||jay <jay@.discussions.microsoft.com> wrote:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
It's difficult to generate data that isn't there with an SQL query. Your
best bet is to write a stored procedure that does the job.
Another approach is to find wholes in the data sequence. Not exactly what
you want but this might work (untested):
select t1.ts, t2.mts
from tab t1, (
select min(ts) as mts
from tab tx
where tx.ts > t1.ts
) t2
where datediff('dd', t1.ts, t2.mts) > 1
order by t1.ts
Kind regards
robert|||"jay" <jay@.discussions.microsoft.com> wrote in message
news:BE8860E2-EB68-4EA0-A389-B5D53996608E@.microsoft.com...
> "jay" wrote:
>
to identify
> i should have also said i do not want to have a lookup table, ie a
table that
> permenantly stores dates.
> cheers jay
> --
> jay
>
jay,
Why?
Books on SQL and RDBMs recommend it. Many top names on the subjects
recommend it. It's the way to go.
Sincerely,
Chris O.|||>i should have also said i do not want to have a lookup table, ie a table
>that
> permenantly stores dates.
WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
bring any of that flu vaccine near me!
A calendar table seems to be exactly what you need, and is going to be far
more efficient than looping solutions or generating your entire date range
on the fly every time. Please read http://www.aspfaq.com/2519|||because...the calendar table would need to be kept up to date...who would do
that? you do not know the restrictions on the situation so please do not so
easily pass judgement when you do not know all the issues!
I appreciate any help NOT judgements from ill informed people!
cheers jay
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:efmMbhe$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
> bring any of that flu vaccine near me!
> A calendar table seems to be exactly what you need, and is going to be far
> more efficient than looping solutions or generating your entire date range
> on the fly every time. Please read http://www.aspfaq.com/2519
>
>|||"Jay Walker" <jay@.bladecomputing.com.au> wrote in message
news:eCF4kyf$FHA.3064@.TK2MSFTNGP10.phx.gbl...
> because...the calendar table would need to be kept up to
date...who would do
> that? you do not know the restrictions on the situation so please
do not so
> easily pass judgement when you do not know all the issues!
> I appreciate any help NOT judgements from ill informed people!
> cheers jay
>
Jay Walker,
Aaron is among the best informed people around here.
Also, you did not explain the restrictions of your situation. I
asked, earlier, what those restrictions were, and still have no
answer.
You mentioned: "because...the calendar table would need to be kept
up to date...who would do that?"
What do you mean? A calendar table is loaded, and that is that.
There is no "maintenance" (at least not in our lifetimes).
Sincerely,
Chris O.|||Chris2 (rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com) writes:
> What do you mean? A calendar table is loaded, and that is that.
> There is no "maintenance" (at least not in our lifetimes).
Depends on what you fill it with. If you fill it with dates, and only
dates, you can fill it up until 2150 or so. And for Jay's problem this
would do.
But for a more elaborate calendar that keeps track of business days,
there is of course maintenance to do, as holidays are changed. For instance,
my pocket calendar for 2005 printed May 16th as red, and June 6th as
black, when the days come, May 16th was a busiess day and June 6th was not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jay (jay@.discussions.microsoft.com) writes:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
Here is a query that does not use a calendar table. It will not list all
dates though, only the first and last date in an interval. Also, performance
is not likely to be fantastic. To that end a calendar table will be
better. For this reason, I'm including a script that fills up a dates
table with all dates from 1990 to 2149.
Here is the query (runs in Northwind):
SELECT gapstart, MIN(gapend)
FROM (select gapstart = dateadd(DAY, 1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, 1, A.OrderDate))) X
JOIN (select gapend = dateadd(DAY, -1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, -1, A.OrderDate))) Y
ON gapstart <= gapend
GROUP BY gapstart
ORDER BY gapstart
And here is the script:
CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)
-- Make sure it's empty.
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) +
' rows into #numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

finding bad date

I have a database where a varchar field is being used to store dates. I wrot
e
the following function to check if the data is a valid date. If it is not a
valid date, Null is returned.
ALTER function f_ToDate(@.inDate varchar(50))
returns datetime
begin
declare @.returndate datetime
if isdate(@.inDate) = 0
select @.returndate = null
else
select @.returndate = convert(datetime,@.inDate,101)
return @.returndate
end
In my database, I have a table called Generation with a field called
CreateDate. When I run the following query, I get an error message stating
that “syntax error converting datetime from character string”
select dbo.f_ToDate(Generation.CreationDate) from Generation
To check, I ran the following query and it returned all null records which
is expected
select CreationDate from general where isdate(CreationDate) = 0
The following returns no records which is expected.
select CreationDate from general where isdate(CreationDate) = 0 and
CreationDate is not Null.
I can’t find the record that is causing “select
dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.I have had a similar issue w/ Dates. A quick and dirty way that I used was t
o
export out the records using DTS to a table that mirrored the working one.
Only change I made was to change the varchar date field to a true datetime
field in the temp table. In my dts package I set the max number of errors to
the max (999) and started the export.
In my case I had 632 fewer records in my temp table after the export. I ran
a NOT EXISTS against the tables and was able to find the 632 records w/ bad
dates in my production table.
If the number of records w/ bad dates is GT 999, you will have to do the
export in bacthes to find all the bad recs.
Just a thought!
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Emma,
The convert function with style 101 is supposed to be used when converting
from datetime to char or varchar. Can you execute:
select case when isdate(CreationDate) = 0 then null else convert(datetime,
CreationDate)
from Generation
AMB
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Thanks Jay for your response. I did just what you said. The number of record
s
exported to the temp table was the same as in my original table, and the
query work on the temp table and not on the original. I can’t figure it ou
t.
"Jay Kusch" wrote:
> I have had a similar issue w/ Dates. A quick and dirty way that I used was
to
> export out the records using DTS to a table that mirrored the working one.
> Only change I made was to change the varchar date field to a true datetime
> field in the temp table. In my dts package I set the max number of errors
to
> the max (999) and started the export.
> In my case I had 632 fewer records in my temp table after the export. I ra
n
> a NOT EXISTS against the tables and was able to find the 632 records w/ ba
d
> dates in my production table.
> If the number of records w/ bad dates is GT 999, you will have to do the
> export in bacthes to find all the bad recs.
> Just a thought!
> "Emma" wrote:
>|||Thanks. That worked.
"Alejandro Mesa" wrote:
> Emma,
> The convert function with style 101 is supposed to be used when converting
> from datetime to char or varchar. Can you execute:
> select case when isdate(CreationDate) = 0 then null else convert(datetime,
> CreationDate)
> from Generation
>
> AMB
> "Emma" wrote:
>

Find time interval between dates

Hi, all. I've got a classic ASP app that I've been tasked with modifying to
track users' IP addresses. If they fail to login, we want to record their IP
and the date/time when they failed. If they fail more than 5 times within 10
minutes, we want to lock them out for 5 minutes. Below are the two SQL
Server 2000 tables I've created.
The problem I'm having is this: How do you figure out if user has failed 5
times within the last 10 minutes. If you believe there's a better way to
design the tables for this task, please let me know. Thank you :)
CREATE TABLE [tblHits] (
[hitID] [int] IDENTITY (1, 1) NOT NULL ,
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
(
[hitID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
(
[IP]
) REFERENCES [tblIPs] (
[IP]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblIPs] (
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
(
[IP]
) ON [PRIMARY]
) ON [PRIMARY]
GOHi
smalldatetime data type has only the accuracy of 1 minute, so you might be
working between 9 and 11 minutes. The data type datetime might be more
appropriate.
Unless this is an intranet application, there is no guarantee that the IP
address could not be the source for multiple users. Corporates have large
proxy servers that do not expose the internal source IP. You might make a
lot of users unhappy if one of them fails to login correctly.
That aside. The following will return the number of hits in the last 10
minutes:
SELECT
COUNT(*)
FROM
tblHits
WHERE
dateAdded > DATEADD(mi, -10, GETDATE())
AND IP = @.YourCurrentIPAddress
Regards
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Hi
. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
>
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
you get the idea and an modify the query.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Thank you, Michael and Uri. You've got some very creative solutions. By the
way, the point about public computers or proxy servers is a valid one; we
discussed it, but the powers-that-be wanted to go ahead and do this anyway.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhYgKlbIGHA.3144@.TK2MSFTNGP10.phx.gbl...
> Hi
> . If you believe there's a better way to
>
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
> you get the idea and an modify the query.
>
>
> "curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
> news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
>

Find the newest date of 2 date fields

I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!

Hello, you can use a CASE statement in your SQL Query

Eample:
SELECT CASE WHEN clidlp > clidlc THEN clidlp ELSE clidlc END AS NewestDate
FROM [Table]

find records which are in between 2 dates....

I have a table Hostavailable, fields are
ID, HostID, fromDate, To date

I have to pass 2 dates form front end dt1 and dt2 (program requirement)

i have to get all the hostID available in between 2 dates(dt1 and dt2.)

Quote:

Originally Posted by nileshgarg

I have a table Hostavailable, fields are
ID, HostID, fromDate, To date

I have to pass 2 dates form front end dt1 and dt2 (program requirement)

i have to get all the hostID available in between 2 dates(dt1 and dt2.)

Hi Nilesh,

Welcome to TSDN!!

Clarify one thing, is dt1 and dt2 equal to from date and to date respectively??

Find Consecutive Occurrences

Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate

I have a details table where I capture the following details

CustID, DateofPurchase, PurchaseDetails

I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.

Thanks for your help in advance.
Regards
DineshProbably, Please read the hint link in the sticky at the top of the forum to help us with more information|||Brett,

In my table I have details as below

CustID DateofPurchase PurchaseDetails
------------------
1 01/05/2006 Item1, Item2, Item3
2 01/06/2006 Item2, Item5, Item1, Item7
1 01/06/2006 Item4, Item5, Item6
3 01/06/2006 Item4, Item5, Item6
1 01/07/2006 Item3, Item4, Item5
3 01/07/2006 Item1, Item2, Item3
1 01/08/2006 Item1, Item2, Item3
2 01/08/2006 Item4, Item5, Item6
3 01/08/2006 Item4, Item5, Item6
2 01/09/2006 Item2, Item5, Item1, Item7
2 01/10/2006 Item1, Item2, Item3, Item4
1 01/10/2006 Item1, Item2, Item3, Item4
1 01/11/2006 Item4, Item5, Item6

I need the query which will return say Cust ID 1 has come 4 days in a row, or Cust ID 2 has come 3 days in a Row.
I want the below output

Cust ID No of Consecutive Days
1 4
2 3
3 3
2 2
1 2

Would this help. Please let me know.

Find 2nd Working Day

Hi.

I utilize the Calendar table, and I'm able to find how many working
days between 2 dates, but does anyone use this table to find the 2nd or
5th working date?

if 11/30/06 then 12/4/06

I'm sure it's not too difficult but i can't seem to get it to work

(select caldate from calendar where...?...and workingday = 'Y')

thanks,
PaulOn 30 Nov 2006 05:34:29 -0800, paulmac106@.gmail.com wrote:

Quote:

Originally Posted by

>Hi.
>
>I utilize the Calendar table, and I'm able to find how many working
>days between 2 dates, but does anyone use this table to find the 2nd or
>5th working date?
>
>if 11/30/06 then 12/4/06
>
>I'm sure it's not too difficult but i can't seem to get it to work
>
>(select caldate from calendar where...?...and workingday = 'Y')

Hi Paul,

Basics first: here's a query to get the next working day after @.StartDt.

SELECT TOP (1) TheDate
FROM Calendar
WHERE TheDate @.StartDt
AND WorkingDay = 'Y'
ORDER BY TheDate;

Unfortunately, we need to add a bit more complexity for the second
business day: first, we get the TWO next business days, then pick the
last of them:

SELECT TOP (1) TheDate
FROM (SELECT TOP (2) TheDate
FROM Calendar
WHERE TheDate @.StartDt
AND WorkingDay = 'Y'
ORDER BY TheDate) AS d
ORDER BY TheDate DESC;

This can be easily adapted to get the third, fourth, etc. working day:
just replace TOP (2) with TOP (3), TOP (4), etc.

Note 1: If on SQL Server 2000, replace TOP (1) and TOP (2) with TOP 1
and TOP 2.

Note 2: If on SQL Server 2005, you may also use TOP (@.NumOfDays) to make
the number of business days to go forward variable.

--
Hugo Kornelis, SQL Server MVP|||Worked perfectly!

thanks Hugo, I really appreciate it.|||Worked perfectly!

thanks Hugo, I really appreciate it.|||(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

I utilize the Calendar table, and I'm able to find how many working
days between 2 dates, but does anyone use this table to find the 2nd or
5th working date?
>
if 11/30/06 then 12/4/06
>
I'm sure it's not too difficult but i can't seem to get it to work
>
(select caldate from calendar where...?...and workingday = 'Y')

If this is a common operation, adding a business-dayno column to the
table can be a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Add a julianized business column to your table and the math is very
simple. Look at the current day's Julianized number, subtract and
return the MIN(cal_date) with that julian_business_day value.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
julian_business_day INTEGER NOT NULL,
..);

etc.
INSERT INTO Calendar VALUES ('2006-12-01', 10); -- fri
INSERT INTO Calendar VALUES ('2006-12-02, 10); -- sat
INSERT INTO Calendar VALUES ('2006-12-01', 10); -- sun
INSERT INTO Calendar VALUES ('2006-12-01', 11); -- mon
INSERT INTO Calendar VALUES ('2006-12-01', 12); -- tue
etc.

Filtering records between 2 dates for the report(Using CR 9.2)

HI,
I am developing this application which stores purchases requisitions information from the user in a d/b.I have to generate reports based on the user inputted dates.The reports should list all the records between the 2 dates which the user inputs at run time.
This is the code I have provided to do the filtering of records for the CR.
********************************************************
MsgBox "Please enter 2 dates in between which to generate the Cost allocation Report", vbOKOnly + vbInformation, "Printing Reports.."
date1 = InputBox("Please enter the starting date for the Report!", "Cost Allocation Report")
date2 = InputBox("please enter the ending date for the Report!", "Cost Allocation Report")
date1 = CDate(MakeDateFormated(date1, "/"))
date2 = CDate(MakeDateFormated(date2, "/"))
Dim str3
str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= '" & date1 & "' AND {purchase.podate} <= '" & date2 & "'"
CrystalReport6.RecordSelectionFormula = str3
CrystalReport6.PrintOut True, 1
CrystalReport6.DiscardSavedData
********************************************************
Date1 and date 2 are of type Date.
When this code is run,I get an error message which says that "a Date-Time is required here" for the line giving the Record selection formula.
When I checked the CR in the designer,the datatype of the field "podate" is shown as Date-Time instead of just Date,even though,it is of type date in the Database.
I have tried deleting the field and adding it again to the report.It still shows datatype to be Date-Time.
How can I change this?
Please help me as I am very very new to this area.
Thank you for your time.Your help is highly appreciated.
Lakshmi VinayTry this

str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= #" & date1 & "# AND {purchase.podate} <= #" & date2 & "#"