Finding available customer numbers

Hi All,
How do I find unused customer numbers in a table?
Thanks
Mac
Hi,
With this information it is difficult to give a solution. A tip will be,
Check the trasnaction table against the customer master table.
If you have not dealed with that customer , you wont have any transaction in
TRAN table.
With that you can identify the list of customers, query will be some thing
like:-
select cust_num,cust_name from cust_master where cust_num not in (select
cust_num from cust_transaction)
Thanks
Hari
MCDBA
"Mac" <jmcgrath@.planesunited.com> wrote in message
news:46ABDDA1-B5FC-4F33-8655-E99A7802D794@.microsoft.com...
> Hi All,
> How do I find unused customer numbers in a table?
> Thanks
> Mac
|||I apoligize, I did not give enough info. The problem is actually simpler than that. In my customer master all customers are asigned a unique number from 10000- 30000. I am looking for available numbers (not in the table) in this range.
Thanks,
Mac
|||This will give you all the gaps :
SELECT col + 1
FROM tbl
WHERE NOT EXISTS( SELECT * FROM tbl t1
WHERE t1.col = tbl.col + 1 )
AND IDNo < ( SELECT MAX( col ) FROM tbl );
To get the smallest one:
SELECT MIN( col ) + 1
FROM tbl
WHERE NOT EXISTS( SELECT * FROM tbl t1
WHERE t1.col = tbl.col + 1 ) ;
Anith
|||Thanks very much Anith!

find available time slot

I have a table that looks like:
CREATE TABLE [dbo].[UserAgenda] (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
) ON [PRIMARY]
GO
and I would like to find a time gap for the amount of time I am looking
for.
For example, I want the next available hour in the list or the next
available 15 minutes.
Any help would be appreciated.
Thanks
Richard MeetzeAm I correct that "next available" means a period of time that won't
overlap the period between StartTime and EndTime on any row in the
table? Try this:
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
David Portas
SQL Server MVP
--|||David - you are correct, thanks for the help.
I have another question if you or anyone else it up for it.
Using the same table schema as above, I have the following data:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
Now I want to place an item in from 8:30 to 8:45 and I want my end
result to look like:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
I had to insert the new time in a current time and split the other time
to two parts and make it fit. Does make since?
Thanks
Richard Meetze|||David,
I think will should check for existence of rows between the matching ones.
Somethnig like:
...
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
Example:
create table UserAgenda (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL
)
insert into UserAgenda values('2005-02-23T08:30:00', '2005-02-23T08:35:00')
insert into UserAgenda values('2005-02-23T08:36:00', '2005-02-23T08:46:00')
insert into UserAgenda values('2005-02-23T08:47:00', '2005-02-23T08:57:00')
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
drop table UserAgenda
go
AMB
"David Portas" wrote:

> Am I correct that "next available" means a period of time that won't
> overlap the period between StartTime and EndTime on any row in the
> table? Try this:
> DECLARE @.m INTEGER
> SET @.m = 10
> SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
> FROM UserAgenda AS A
> LEFT JOIN UserAgenda AS B
> ON A.endtime <= B.starttime
> WHERE DATEDIFF(MI,A.endtime,
> COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
> --
> David Portas
> SQL Server MVP
> --
>|||See if it hepls you
create table UserAgenda
(
StartTime datetime not null,
EndTime datetime not null
)
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:00','20000610
10:15')
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:30','20000610
10:50')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:00','20000610
11:25')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:45','20000610
11:55')
insert into UserAgenda(StartTime,EndTime)values ('20000610 12:57','20000610
13:00')
insert into UserAgenda(StartTime,EndTime)values ('20000610 13:15','20000610
13:30')
insert into UserAgenda(StartTime,EndTime)values ('20000610 14:04','20000610
14:40')
SELECT
StartTime,
ISNULL(
(SELECT MIN(EndTime)
FROM UserAgenda AS S3
WHERE S3.StartTime >= S1.StartTime
AND ISNULL(
DATEDIFF(
minute,
S3.EndTime,
(SELECT MIN(StartTime)
FROM UserAgenda AS S4
WHERE S4.StartTime > S3.EndTime)), 15) <= 15),
EndTime) AS EndTime
FROM UserAgenda AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTime)
FROM UserAgenda AS S2
WHERE S2.EndTime < S1.StartTime),S1.StartTime),15)<= 15
<meetze@.gmail.com> wrote in message
news:1109184358.837536.30570@.f14g2000cwb.googlegroups.com...
> David - you are correct, thanks for the help.
> I have another question if you or anyone else it up for it.
> Using the same table schema as above, I have the following data:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> Now I want to place an item in from 8:30 to 8:45 and I want my end
> result to look like:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
> 2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
> 2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> I had to insert the new time in a current time and split the other time
> to two parts and make it fit. Does make since?
> Thanks
> Richard Meetze
>