Sql Create error: indicating

Showing posts with label indicating. Show all posts

Thursday, March 8, 2012

Create Numeric Sequence ID

Can this be written without tmp tables? (tmp tables simulate real tables)
Needed: an extra column indicating the correct sequence based on the order by
condition of databasename,appname.
create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
varchar(200),active bit null,id int identity(1,1) not null)
insert into #tmp1(appname,databasename,comment,active) Select
'EDIBU','Archived','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ASNTransfer','ASND','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'atcentral.exe','ATCentral','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'AtCentral.exe','OrderEntry','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
create table #tmp (idx int identity(1,1),appname varchar(50) null,
databasename varchar(50) null,comment varchar(200) null,active bit null,id
int null)
insert into #tmp(appname,databasename,comment,active,id)
select * from #tmp1 order by databasename,appname
select * from #tmp
drop table #tmp1
drop table #tmp
--
Regards,
JamieSure...
SELECT
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id,
count(*)
FROM #tmp1 t1
JOIN #tmp1 t2 ON
t2.databasename <= t1.databasename
and
(t2.databasename < t1.databasename
or t2.appname <= t1.appname)
GROUP BY
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:32FD08CE-A2A8-4BE3-93D0-0F9D740536DA@.microsoft.com...
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order
> by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename
> varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||Which version of SS are you using?
select
appname, databasename, comment, active,
(
select count(*)
from dbo.t1 as b
where b.appname < a.appname
or (b.appname = a.appname and b.databasename <= a.databasename)
) as rn
from
dbo.t1 as a
order by
rn
-- 2005
select
appname, databasename, comment, active,
row_number() over(order by appname, databasename) as rn
from
dbo.t1
order by
rn
go
AMB
"thejamie" wrote:
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||In 2005 can do this: (looking for a 2000 solution still)
select
b.rownum,a.id, a.databasename, a.appname
from
#tmp1a
inner join
(
SELECT ROW_NUMBER () OVER (ORDER BY databasename,appname) AS rowNum, ID
FROM #tmp1
) as b
on a.[id] = b.[id]
order by b.rownum
--
Regards,
Jamie
"thejamie" wrote:
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||Alejandro,
Just a minor correction... (looking for databasename,appname order rather
than the other way around)
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
and it looks like the one below works too but with only 172 records in my
actual database, there is no way to be sure at this point.
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
Regards,
Jamie
"Alejandro Mesa" wrote:
> Which version of SS are you using?
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from dbo.t1 as b
> where b.appname < a.appname
> or (b.appname = a.appname and b.databasename <= a.databasename)
> ) as rn
> from
> dbo.t1 as a
> order by
> rn
> -- 2005
> select
> appname, databasename, comment, active,
> row_number() over(order by appname, databasename) as rn
> from
> dbo.t1
> order by
> rn
> go
>
> AMB
> "thejamie" wrote:
> > Can this be written without tmp tables? (tmp tables simulate real tables)
> > Needed: an extra column indicating the correct sequence based on the order by
> > condition of databasename,appname.
> >
> > create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> > varchar(200),active bit null,id int identity(1,1) not null)
> >
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'EDIBU','Archived','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'ASNTransfer','ASND','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'atcentral.exe','ATCentral','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'AtCentral.exe','OrderEntry','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> >
> > create table #tmp (idx int identity(1,1),appname varchar(50) null,
> > databasename varchar(50) null,comment varchar(200) null,active bit null,id
> > int null)
> > insert into #tmp(appname,databasename,comment,active,id)
> > select * from #tmp1 order by databasename,appname
> > select * from #tmp
> >
> > drop table #tmp1
> > drop table #tmp
> >
> > --
> > Regards,
> > Jamie|||Hi thejamie,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
You got it.
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
It could be working because of the data you have right now, but that is not
the way to proceed when you need a tie breaker.
Example:
declare @.t table (
databasename varchar(50),
appname varchar(50)
)
insert into @.t values('db1', 'app1')
insert into @.t values('db1', 'app2')
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
-- wrong result
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
go
AMB
"thejamie" wrote:
> Alejandro,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
>
> --
> Regards,
> Jamie
>
> "Alejandro Mesa" wrote:
> > Which version of SS are you using?
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from dbo.t1 as b
> > where b.appname < a.appname
> > or (b.appname = a.appname and b.databasename <= a.databasename)
> > ) as rn
> > from
> > dbo.t1 as a
> > order by
> > rn
> >
> > -- 2005
> > select
> > appname, databasename, comment, active,
> > row_number() over(order by appname, databasename) as rn
> > from
> > dbo.t1
> > order by
> > rn
> > go
> >
> >
> > AMB
> >
> > "thejamie" wrote:
> >
> > > Can this be written without tmp tables? (tmp tables simulate real tables)
> > > Needed: an extra column indicating the correct sequence based on the order by
> > > condition of databasename,appname.
> > >
> > > create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> > > varchar(200),active bit null,id int identity(1,1) not null)
> > >
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'EDIBU','Archived','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'ASNTransfer','ASND','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'atcentral.exe','ATCentral','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'AtCentral.exe','OrderEntry','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> > >
> > > create table #tmp (idx int identity(1,1),appname varchar(50) null,
> > > databasename varchar(50) null,comment varchar(200) null,active bit null,id
> > > int null)
> > > insert into #tmp(appname,databasename,comment,active,id)
> > > select * from #tmp1 order by databasename,appname
> > > select * from #tmp
> > >
> > > drop table #tmp1
> > > drop table #tmp
> > >
> > > --
> > > Regards,
> > > Jamie|||Toward a better understanding of traditional ranking queries:
http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html|||Thanks Steve.
--
Regards,
Jamie
"Steve Dassin" wrote:
> Toward a better understanding of traditional ranking queries:
> http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html
>
>|||Yep, missed the tie breaker... thanks
--
Regards,
Jamie
"Alejandro Mesa" wrote:
> Hi thejamie,
> > Just a minor correction... (looking for databasename,appname order rather
> > than the other way around)
> You got it.
> > and it looks like the one below works too but with only 172 records in my
> > actual database, there is no way to be sure at this point.
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from migrationdata as b
> > where b.databasename < a.databasename
> > or ( b.databasename < a.databasename and b.appname <= a.appname)
> > ) as rn
> > from
> > migrationdata as a
> > order by
> > rn
> It could be working because of the data you have right now, but that is not
> the way to proceed when you need a tie breaker.
> Example:
> declare @.t table (
> databasename varchar(50),
> appname varchar(50)
> )
> insert into @.t values('db1', 'app1')
> insert into @.t values('db1', 'app2')
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> -- wrong result
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> go
>
> AMB
>
> "thejamie" wrote:
> > Alejandro,
> > Just a minor correction... (looking for databasename,appname order rather
> > than the other way around)
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from migrationdata as b
> > where b.databasename < a.databasename
> > or ( b.databasename = a.databasename and b.appname <= a.appname)
> > ) as rn
> > from
> > migrationdata as a
> > order by
> > rn
> >
> > and it looks like the one below works too but with only 172 records in my
> > actual database, there is no way to be sure at this point.
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from migrationdata as b
> > where b.databasename < a.databasename
> > or ( b.databasename < a.databasename and b.appname <= a.appname)
> > ) as rn
> > from
> > migrationdata as a
> > order by
> > rn
> >
> >
> > --
> > Regards,
> > Jamie
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > Which version of SS are you using?
> > >
> > > select
> > > appname, databasename, comment, active,
> > > (
> > > select count(*)
> > > from dbo.t1 as b
> > > where b.appname < a.appname
> > > or (b.appname = a.appname and b.databasename <= a.databasename)
> > > ) as rn
> > > from
> > > dbo.t1 as a
> > > order by
> > > rn
> > >
> > > -- 2005
> > > select
> > > appname, databasename, comment, active,
> > > row_number() over(order by appname, databasename) as rn
> > > from
> > > dbo.t1
> > > order by
> > > rn
> > > go
> > >
> > >
> > > AMB
> > >
> > > "thejamie" wrote:
> > >
> > > > Can this be written without tmp tables? (tmp tables simulate real tables)
> > > > Needed: an extra column indicating the correct sequence based on the order by
> > > > condition of databasename,appname.
> > > >
> > > > create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> > > > varchar(200),active bit null,id int identity(1,1) not null)
> > > >
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'EDIBU','Archived','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'ASNTransfer','ASND','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'atcentral.exe','ATCentral','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'AtCentral.exe','OrderEntry','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> > > >
> > > > create table #tmp (idx int identity(1,1),appname varchar(50) null,
> > > > databasename varchar(50) null,comment varchar(200) null,active bit null,id
> > > > int null)
> > > > insert into #tmp(appname,databasename,comment,active,id)
> > > > select * from #tmp1 order by databasename,appname
> > > > select * from #tmp
> > > >
> > > > drop table #tmp1
> > > > drop table #tmp
> > > >
> > > > --
> > > > Regards,
> > > > Jamie

create new table based on union of range data

Hi, I was hoping someone could help me out.
Is it possible for SQL to take a table which has date range data (start and
end date indicating the contract period of the client) and create a new
table which creates a union of 'unionizable' range data for each specific
client. For example (here i am using numbers to indicate date order):
client startdate enddate
A 2 5
A 3 7
A 7 10
A 11 12
B 4 6
B 8 14
B 5 7
C 2 3
C 3 10
C 1 20
The table operation would give (for example {2..5} U {3..7} U {7..10} =
(2..10} in the resultant table. But {4..6} U {8..14} does not have a common
union, so I just leave them as {4..6} and {8..14} in the resultant table:
client startdate enddate
A 2 10
A 11 12
B 4 7
B 8 14
B 15 21
C 1 20
I am unable to determine how to do this. I was thinking to move towards
implementing cursors, but that in itself will be a complex algorithm. Is
there some easier method to use? I was also thinking of cross joining the
initial table with itself on the condition that t1.client = t2.client (yes
this is not a cross join, jut results in an inner join). Then deriving a new
table from this based upon a comparison between t1.startdate , t2.startdate
and t1.enddate, t2.enddate
Would anyone have any insight into this?
any help most appreciated!
thanks!
CathyHi Cathy
You may want to check out Itzik's articles in SQL Server Magazine
http://www.windowsitpro.com/Article...4570/44570.html
You may need to undo the current ranges such as (using your sample data,
plus a few more test cases)
CREATE TABLE #values ( Client char(1), Num int )
INSERT INTO #values ( Client , Num )
SELECT DISTINCT C.[Client], N.[Num]
FROM
( SELECT 'A' AS [client], 2 as [start], 5 as [end]
UNION ALL SELECT 'A', 3, 7
UNION ALL SELECT 'A', 7, 10
UNION ALL SELECT 'A', 11, 12
UNION ALL SELECT 'B', 4, 6
UNION ALL SELECT 'B', 8, 14
UNION ALL SELECT 'B', 5, 7
UNION ALL SELECT 'B', 16, 24
UNION ALL SELECT 'C', 2, 3
UNION ALL SELECT 'C', 3, 10
UNION ALL SELECT 'C', 1, 20
UNION ALL SELECT 'D', 2, 2
) C
JOIN (
SELECT 1 AS Num
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
UNION SELECT 13
UNION SELECT 14
UNION SELECT 15
UNION SELECT 16
UNION SELECT 17
UNION SELECT 18
UNION SELECT 19
UNION SELECT 20
UNION SELECT 21
UNION SELECT 22
UNION SELECT 23
UNION SELECT 24
UNION SELECT 25
UNION SELECT 26
UNION SELECT 27
UNION SELECT 28
UNION SELECT 29
) N ON C.[Start] <= N.Num and C.[end] >= n.num
John
"Cathy Smith" <cs@.cs.com.au> wrote in message
news:%23FPXb08EGHA.2856@.TK2MSFTNGP12.phx.gbl...
> Hi, I was hoping someone could help me out.
> Is it possible for SQL to take a table which has date range data (start
> and end date indicating the contract period of the client) and create a
> new table which creates a union of 'unionizable' range data for each
> specific client. For example (here i am using numbers to indicate date
> order):
> client startdate enddate
> A 2 5
> A 3 7
> A 7 10
> A 11 12
> B 4 6
> B 8 14
> B 5 7
> C 2 3
> C 3 10
> C 1 20
> The table operation would give (for example {2..5} U {3..7} U {7..10} =
> (2..10} in the resultant table. But {4..6} U {8..14} does not have a
> common union, so I just leave them as {4..6} and {8..14} in the resultant
> table:
> client startdate enddate
> A 2 10
> A 11 12
> B 4 7
> B 8 14
> B 15 21
> C 1 20
> I am unable to determine how to do this. I was thinking to move towards
> implementing cursors, but that in itself will be a complex algorithm. Is
> there some easier method to use? I was also thinking of cross joining the
> initial table with itself on the condition that t1.client = t2.client (yes
> this is not a cross join, jut results in an inner join). Then deriving a
> new table from this based upon a comparison between t1.startdate ,
> t2.startdate and t1.enddate, t2.enddate
> Would anyone have any insight into this?
> any help most appreciated!
> thanks!
> Cathy
>|||Cathy Smith (cs@.cs.com.au) writes:
> Is it possible for SQL to take a table which has date range data (start
> and end date indicating the contract period of the client) and create a
> new table which creates a union of 'unionizable' range data for each
> specific client. For example (here i am using numbers to indicate date
> order):
Have a look at
http://groups.google.com/group/comp...48dda4c48fb808b
your problem reminds me of the problem in that thread.
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|||1) Look up the Rick Snodgrass book at University of Arizona.
2) Look up the use of a Calendar Auxiliary table.
3) Look up SQL FOR SMARTIES for this kind of query using a calendar
table. I have to go to bed now, but the idea is to see what ranges each
calendar dates falls inside of. Make a list of cal_dates by client,
such that there is a missing date before the MIN() and after the MAX()
of the list.
It is a very simple set of joins and you do not need elaborate
subqueries.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||-- If you are using SQL Server 2005, you can use
-- recursive CTEs to get the results
create table Contracts(client char(1), startdate int, enddate int)
insert into Contracts(client,startdate,enddate) values ('A', 2 , 5)
insert into Contracts(client,startdate,enddate) values ('A', 3 , 7)
insert into Contracts(client,startdate,enddate) values ('A', 7 , 10)
insert into Contracts(client,startdate,enddate) values ('A', 11 , 12)
insert into Contracts(client,startdate,enddate) values ('B', 4 , 6)
insert into Contracts(client,startdate,enddate) values ('B', 8 , 14)
insert into Contracts(client,startdate,enddate) values ('B', 5 , 7)
insert into Contracts(client,startdate,enddate) values ('C', 2 , 3)
insert into Contracts(client,startdate,enddate) values ('C', 3 , 10)
insert into Contracts(client,startdate,enddate) values ('C', 1 , 20);
with cte_contracts(client,startdate,enddate,m
instartdate,maxenddate) as
(
select A.client,A.startdate,A.enddate,A.startdate,A.enddate
from Contracts A
union all
select A.client,A.startdate,A.enddate,B.startdate,C.enddate
from cte_contracts A
inner join Contracts B on B.client=A.client
and B.enddate >= A.minstartdate and B.startdate <= A.maxenddate
inner join Contracts C on C.client=A.client
and C.enddate >= A.minstartdate and C.startdate <= A.maxenddate
where (B.startdate < A.minstartdate and C.enddate >= A.maxenddate)
or (B.startdate <= A.minstartdate and C.enddate > A.maxenddate)
)
select distinct client,min(minstartdate),max(maxenddate)
from cte_contracts
group by client,startdate,enddate
drop table Contracts|||Cathy,
what about something like this:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( client CHAR(1), startdate INT, enddate INT )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 'A', 2, 5 )
INSERT INTO #tmp VALUES ( 'A', 3, 7 )
INSERT INTO #tmp VALUES ( 'A', 7, 10 )
INSERT INTO #tmp VALUES ( 'A', 11, 12 )
INSERT INTO #tmp VALUES ( 'B', 4, 6 )
INSERT INTO #tmp VALUES ( 'B', 8, 14 )
INSERT INTO #tmp VALUES ( 'B', 5, 7 )
INSERT INTO #tmp VALUES ( 'C', 2, 3 )
INSERT INTO #tmp VALUES ( 'C', 3, 10 )
INSERT INTO #tmp VALUES ( 'C', 1, 20 )
SET NOCOUNT OFF
-- SELECT * FROM #tmp
SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
FROM #tmp t1, #tmp t2
WHERE t1.client = t2.client
AND t2.startdate > t1.startdate
AND t2.startdate Between t1.startdate And t2.startdate
GROUP BY t1.client
UNION
SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
FROM #tmp t1
WHERE NOT EXISTS
(
SELECT *
FROM #tmp t2
WHERE t1.client = t2.client
AND t2.startdate > t1.startdate
AND t2.startdate Between t1.startdate And t2.startdate
)
GROUP BY t1.client
If the code doesn't quite do what you want, perhaps the theory is good, ie a
UNION of records which have range matches, and those that don't.
Let me know how you get on.
Damien
"Cathy Smith" wrote:

> Hi, I was hoping someone could help me out.
> Is it possible for SQL to take a table which has date range data (start an
d
> end date indicating the contract period of the client) and create a new
> table which creates a union of 'unionizable' range data for each specific
> client. For example (here i am using numbers to indicate date order):
> client startdate enddate
> A 2 5
> A 3 7
> A 7 10
> A 11 12
> B 4 6
> B 8 14
> B 5 7
> C 2 3
> C 3 10
> C 1 20
> The table operation would give (for example {2..5} U {3..7} U {7..10} =
> (2..10} in the resultant table. But {4..6} U {8..14} does not have a commo
n
> union, so I just leave them as {4..6} and {8..14} in the resultant table:
> client startdate enddate
> A 2 10
> A 11 12
> B 4 7
> B 8 14
> B 15 21
> C 1 20
> I am unable to determine how to do this. I was thinking to move towards
> implementing cursors, but that in itself will be a complex algorithm. Is
> there some easier method to use? I was also thinking of cross joining the
> initial table with itself on the condition that t1.client = t2.client (yes
> this is not a cross join, jut results in an inner join). Then deriving a n
ew
> table from this based upon a comparison between t1.startdate , t2.startdat
e
> and t1.enddate, t2.enddate
> Would anyone have any insight into this?
> any help most appreciated!
> thanks!
> Cathy
>
>|||-- If you are using SQL Server 2005, you can use
-- recursive CTEs to get the results
create table Contracts(client char(1), startdate int, enddate int)
insert into Contracts(client,startdate,enddate) values ('A', 2 , 5)
insert into Contracts(client,startdate,enddate) values ('A', 3 , 7)
insert into Contracts(client,startdate,enddate) values ('A', 7 , 10)
insert into Contracts(client,startdate,enddate) values ('A', 11 , 12)
insert into Contracts(client,startdate,enddate) values ('B', 4 , 6)
insert into Contracts(client,startdate,enddate) values ('B', 8 , 14)
insert into Contracts(client,startdate,enddate) values ('B', 5 , 7)
insert into Contracts(client,startdate,enddate) values ('C', 2 , 3)
insert into Contracts(client,startdate,enddate) values ('C', 3 , 10)
insert into Contracts(client,startdate,enddate) values ('C', 1 , 20);
with cte_contracts(client,startdate,enddate,m
instartdate,maxenddate) as
(
select A.client,A.startdate,A.enddate,A.startdate,A.enddate
from Contracts A
union all
select A.client,A.startdate,A.enddate,B.startdate,C.enddate
from cte_contracts A
inner join Contracts B on B.client=A.client
and B.enddate >= A.minstartdate and B.startdate <= A.maxenddate
inner join Contracts C on C.client=A.client
and C.enddate >= A.minstartdate and C.startdate <= A.maxenddate
where (B.startdate < A.minstartdate and C.enddate >= A.maxenddate)
or (B.startdate <= A.minstartdate and C.enddate > A.maxenddate)
)
select distinct client,min(minstartdate),max(maxenddate)
from cte_contracts
group by client,startdate,enddate
drop table Contracts|||Thanks everyone! I really appreciate the wonderful feedback!!!
I took everyone's suggestions into perspective and finally came up with a
solution based on two views and a select statement, taken from the following
article I found at:
http://groups.google.com.au/group/c...e3dba76e3bc5d57
I modified it to encompass an additional column called client.
Thanks so much everyone for your wonderful solutions!!!
Cathy
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:BC80A0E1-86A5-4EB5-83D3-821FEC1D0765@.microsoft.com...
> Cathy,
> what about something like this:
> -- DROP TABLE #tmp
> CREATE TABLE #tmp ( client CHAR(1), startdate INT, enddate INT )
> SET NOCOUNT ON
> INSERT INTO #tmp VALUES ( 'A', 2, 5 )
> INSERT INTO #tmp VALUES ( 'A', 3, 7 )
> INSERT INTO #tmp VALUES ( 'A', 7, 10 )
> INSERT INTO #tmp VALUES ( 'A', 11, 12 )
> INSERT INTO #tmp VALUES ( 'B', 4, 6 )
> INSERT INTO #tmp VALUES ( 'B', 8, 14 )
> INSERT INTO #tmp VALUES ( 'B', 5, 7 )
> INSERT INTO #tmp VALUES ( 'C', 2, 3 )
> INSERT INTO #tmp VALUES ( 'C', 3, 10 )
> INSERT INTO #tmp VALUES ( 'C', 1, 20 )
> SET NOCOUNT OFF
>
> -- SELECT * FROM #tmp
>
> SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
> FROM #tmp t1, #tmp t2
> WHERE t1.client = t2.client
> AND t2.startdate > t1.startdate
> AND t2.startdate Between t1.startdate And t2.startdate
> GROUP BY t1.client
> UNION
> SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
> FROM #tmp t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM #tmp t2
> WHERE t1.client = t2.client
> AND t2.startdate > t1.startdate
> AND t2.startdate Between t1.startdate And t2.startdate
> )
> GROUP BY t1.client
> If the code doesn't quite do what you want, perhaps the theory is good, ie
> a
> UNION of records which have range matches, and those that don't.
> Let me know how you get on.
>
> Damien
> "Cathy Smith" wrote:
>

Thursday, March 8, 2012

Create Numeric Sequence ID

Create Numeric Sequence ID

Create Numeric Sequence ID

create new table based on union of range data

Sql Create error

Blog Archive

About Me