"with <tablename> as" syntax help

I'm starting with:

dtTimeIn bStatus

1899-12-30 12:00:00 0
1899-12-30 12:00:01 0
1899-12-30 12:00:02 0
1899-12-30 12:00:03 1
1899-12-30 12:00:04 1
1899-12-30 12:00:05 0
1899-12-30 12:00:06 0
1899-12-30 12:00:07 1

I'm trying to get to:

dtTimeStart dtTimeFinish bStatus

1899-12-30 12:00:00 1899-12-30 12:00:02 0
1899-12-30 12:00:03 1899-12-30 12:00:04 1
1899-12-30 12:00:05 1899-12-30 12:00:06 0
1899-12-30 12:00:07 2005-09-09 22:00:00 1

Hi,

what is the logic with which you want to create the second table?|||

I'm trying to group the 0's and 1's but still maintain the sequence of 0's and 1's.

Ex: For the first three seconds it's off then, for two seconds it's on, then for....

|||The query below should get what you want. There are some assumptions in the query that you need to modify based on your data/requirements.

with tt_seq
as
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
)
select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from tt_seq as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;

|||

Thanks for the help! Took me awhile to figure what you did.

Nicely done.

|||Well done!
I hope i would act as you one day.|||I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything.

Does anyone know of a keyword I can use to find out more about this syntax?

Thx.|||WITH is CTE syntax, new in SQL Server 2005. You can use a derived table in SQL Server 2000 to do the same.

select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
) as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;|||This is a SQL Server 2005 feature. See the following entry in SQL Server 2005 BOL for more info: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Bullfrog@.discussions.microsoft.com> wrote in message news:2a2f39de-068c-4a7b-b71f-38961a9b5b69@.discussions.microsoft.com...I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything. Does anyone know of a keyword I can use to find out more about this syntax?Thx.

"with <tablename> as" syntax help

I'm starting with:

dtTimeIn bStatus

1899-12-30 12:00:00 0
1899-12-30 12:00:01 0
1899-12-30 12:00:02 0
1899-12-30 12:00:03 1
1899-12-30 12:00:04 1
1899-12-30 12:00:05 0
1899-12-30 12:00:06 0
1899-12-30 12:00:07 1

I'm trying to get to:

dtTimeStart dtTimeFinish bStatus

1899-12-30 12:00:00 1899-12-30 12:00:02 0
1899-12-30 12:00:03 1899-12-30 12:00:04 1
1899-12-30 12:00:05 1899-12-30 12:00:06 0
1899-12-30 12:00:07 2005-09-09 22:00:00 1

Hi,

what is the logic with which you want to create the second table?|||

I'm trying to group the 0's and 1's but still maintain the sequence of 0's and 1's.

Ex: For the first three seconds it's off then, for two seconds it's on, then for....

|||The query below should get what you want. There are some assumptions in the query that you need to modify based on your data/requirements.

with tt_seq
as
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
)
select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from tt_seq as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;

|||

Thanks for the help! Took me awhile to figure what you did.

Nicely done.

|||Well done!
I hope i would act as you one day.|||I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything.

Does anyone know of a keyword I can use to find out more about this syntax?

Thx.|||WITH is CTE syntax, new in SQL Server 2005. You can use a derived table in SQL Server 2000 to do the same.

select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
) as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;|||This is a SQL Server 2005 feature. See the following entry in SQL Server 2005 BOL for more info: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Bullfrog@.discussions.microsoft.com> wrote in message news:2a2f39de-068c-4a7b-b71f-38961a9b5b69@.discussions.microsoft.com...I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything. Does anyone know of a keyword I can use to find out more about this syntax?Thx.

"ALTER INDEX <tablename> REBUILD"

I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)

Barkingdog

This will rebuild all indexes on a table

Code Snippet

ALTER INDEX ALL ON YourTable REBUILD.

The following will rebuild an individual index:

Code Snippet

ALTER INDEX YourIndexName ON YourTable REBUILD

This is often a resource intensive process as locks are taken out on the table and should ideally be scheduled for a period of low usage. As for how often you need to do this, i would guess it depends on how fragmented your indexes get. This can be checked by looking in sys.dm_db_index_physical_stats.

HTH!

<Tablename> . . <ColumnName>

Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working

Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b

Can anyone let me know.

TIA,
RaviThe difference would be that [Table].[Column] works, while [Table]..[Column] doesn't. At least I've never used syntax like that and I don't know of any reason why it would work.

I think you are getting this confused with table references, which may or may not include the object owner's name.

The full reference for an object (such as a table) is:
[Database].[Owner].[Object]

...but if the object is in the current database you can simply refer to it as
[Owner].[Object]

In either case, you are allowed to omit the owner. SQL Server will search first for an object owned by the current user, and if it cannot find one then it will search for an object that is owned by DBO:
[Database]..[Object]
or..
[Object]|||If you mean that your code refers to something like:SELECT name FROM master..sysdatabasesThe "master" is a database, the "sysdatabases" is a table, and the ".." implies either your own user or more likely dbo.

SQL Server has never allowed the use of tablename..columname in any circumstances that I remember.

-PatP