Sql Create error: based

Showing posts with label based. Show all posts

Thursday, March 29, 2012

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

sql

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

Sunday, March 25, 2012

Create table - default for column (sql 2000)

When I have a table with two columns, can the second column default to
a value based on the value from the first column on an inserted record?
I read the section below in BOL ALTER TABLE but can't make head nor
toes.
E. Alter a table to add several columns with constraints
...
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
...
Can someone explain what REFERENCES is for?
regards,
Gerard> When I have a table with two columns, can the second column default to
> a value based on the value from the first column on an inserted record?
CREATE TABLE dbo.foo
(
column_a VARCHAR(32),
column_b AS CONVERT(CHAR(8), LEFT(column_a, 8))
);
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo;
DROP TABLE dbo.foo;
However, my suggestion is usually to have this kind of thing in a view,
since you can always calculate it at SELECT time, without having to store it
and without tempting users to try and update it, have it be included in
column lists produced by code generators, etc. etc. For example, this
accomplishes the same thing:
CREATE TABLE dbo.foo
(
column_a VARCHAR(32)
);
GO
CREATE VIEW dbo.foo_view
AS
SELECT
column_a,
column_b = LEFT(column_a, 8)
FROM
dbo.foo
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo_view;
DROP VIEW dbo.foo_view;
DROP TABLE dbo.foo;

> Can someone explain what REFERENCES is for?
A foreign key constraint is completely different from what you are asking
about (computed columns). REFERENCES is indicating a separate table (think
master/detail, child/parent, and just about any type of entity
relationship). If you have an Orders table, a Customers table, a Products
table and an OrderDetails table, it is usually set up something like this
(Celko, you know where you can cram your IDENTITY comments):
CREATE TABLE dbo.Products
(
ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Customers
(
CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Orders
(
OrderID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID BIGINT NOT NULL FOREIGN KEY REFERENCES
dbo.Customers(CustomerID),
/*...other columns...*/
);
GO
CREATE TABLE dbo.OrderDetails
(
OrderID BIGINT FOREIGN KEY REFERENCES dbo.Orders(OrderID),
ProductID INT FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT,
/*...other columns...*/
PRIMARY KEY(OrderID, ProductID)
);
GO|||"References" token as shown here is a method to explain that the new
column contents must conform to the contents of another table/column
before an INSERT or UPDATE is allowed.
No related to what you are asking to get accomplished. Sounds more like
you might be asking for a trigger which should only be used as a last
ditch effort when making the changes at the (each) of the client
interface is not possible.
Example of simple trigger:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tgr_sample_insert_update]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[tgr_sample_insert_update]
GO
CREATE TRIGGER dbo.tgr_sample_insert_update ON dbo.tmp_sample
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE inserted SET colb = cola * tax_percentage
GO
Cheers
http://rickhathaway.blogspot.com/|||Thanks to you both for your replies. I will experiment a little to see
which is best for me.
regards,
Gerard|||The computed column was not an option as it can not be updated, quite
logical really.
A trigger was too much overhead for what I was trying to achieve so I I
have resolved my issue by including the logic to set the value of the
column on the "client side"
The reason I was wondering about REFERENCES was that I hoped that
something like this would be possible:
create table aTest (
col_a int default 0,
col_b as case when col_a = 1 then 1 when col_a = 2 then 2 else 3 end
)
insert into aTest (col_a) values (0)
select * from aTest
update aTest set col_b = 9
drop table aTest
--
But as I noted above, the update cannot be done.
Thanks again for your replies.
regards,
Gerard

Thursday, March 22, 2012

Create SQL table from Excel or DataTable?

Hello,

I am trying to create a new table in SQL Server based on an excel sheet someone uploads to my site (ie No DTS, and I don't know the field names). How can I easily do that?

Can I make a sql table based on a DataTable without going row-by-row? Cause then I could go excel to datatable to sql table.

Thanks a bunch,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=373468&SiteID=1

same thing what you want see last answer

|||

Can someone do this in VB? I can convert a little C#, but don't understand the syntax enough to convert all that.

|||

http://www.kamalpatel.net/ConvertCSharp2VB.aspx

Monday, March 19, 2012

create report incrementing date on left and results based on that date in subsequent

I would like to create a report in SQL Analyzer. Is there a For Next
construct or equivalent?
declare @.now datetime
set @.now = '2005-09-19 17:57:00.00'
for i = 1 to 60 -- pseudocode
select @.now
select count(Create_DT) from customer where EmailSent_DT > @.now and
EmailSent_IN = 1
select count(Create_DT) from customer where EmailSent_DT > @.now and
EmailSent_IN = 2
next
********** output results *****************
Col1 Col2
Col3
date Count(query result based on date)
Count(query result based on date)
date + 1 day Count(query result based on date + 1)
Count(query result based on date + 1 )
date + 2 day Count(query result based on date + 2)
Count(query result based on date + 2)
date + 3 day Count(query result based on date + 3)
Count(query result based on date + 3)
******************************
thank you - gregThere is a WHILE loop construct in SQL. However, for general application
related tasks, it is seldom needed. If you post your table structures,
sample schema & expected results ( www.aspfaq.com/5006 ) someone here can
perhaps show you how to generate the required resultset without iteration.
One general trick employed in SQL for such requirements is using a table of
sequentially incrementing numbers. You can find several solutions related to
this, if you search the archives of this newsgroup.
Anith|||Try using WHILE with a counter and then increment the counter in the code.
HTH
Jerry
"hazz" <hazz@.sonic_net> wrote in message
news:eMk64gN0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>I would like to create a report in SQL Analyzer. Is there a For Next
>construct or equivalent?
> declare @.now datetime
> set @.now = '2005-09-19 17:57:00.00'
> for i = 1 to 60 -- pseudocode
> select @.now
> select count(Create_DT) from customer where EmailSent_DT > @.now and
> EmailSent_IN = 1
> select count(Create_DT) from customer where EmailSent_DT > @.now and
> EmailSent_IN = 2
> next
> ********** output results *****************
> Col1 Col2 Col3
> date Count(query result based on date) Count(query
> result based on date)
> date + 1 day Count(query result based on date + 1) Count(query
> result based on date + 1 )
> date + 2 day Count(query result based on date + 2) Count(query
> result based on date + 2)
> date + 3 day Count(query result based on date + 3) Count(query
> result based on date + 3)
> ******************************
> thank you - greg
>|||Beautiful. Thank you.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OgT8xoN0FHA.3904@.TK2MSFTNGP15.phx.gbl...
> Try using WHILE with a counter and then increment the counter in the code.
> HTH
> Jerry
> "hazz" <hazz@.sonic_net> wrote in message
> news:eMk64gN0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Create query based on a field that wont be the same value in both tables

I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.

CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)

INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')

Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB

Here's what the results should look like for the above data.

Name TestA Date TestB Date
-- ---- ----
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Any help would be much appreciated!!!!On 29 Sep 2004 07:41:18 -0700, Jim G wrote:

>Here's what the results should look like for the above data.
>Name TestA Date TestB Date
>-- ---- ----
>Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
>Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
>Account 1 9/3/2004 3:00pm (NULL)
>Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Hi Jim,

Thanks for posting DDL ans INSERTS for sample data!

The following query gives the above results:

SELECT a.Name, a.RunDate, b.RunDate
FROM TestA AS a
LEFT JOIN TestB AS b
ON b.Name = a.Name
AND b.RunDate >= a.RunDate
AND NOT EXISTS (SELECT *
FROM TestA AS a2
WHERE a2.Name = a.Name
AND a2.RunDate > a.RunDate
AND a2.RunDate < b.RunDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Awesome! That worked perfectly. Thanks!

Sunday, March 11, 2012

Create output columns based on input in custom component

I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.

I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).

Are there any good examples for creating output columns dynamically based on the input buffer?

Should I just give up on on the transform and create a custom source component instead?

Since there aren't any rows in the buffer until runtime, I don't see how this will work. Packages can't change their metadata (inputs / outputs) at runtime. You could write a source that uses the connection manager at design time to read the first line from the file, and add the output columns, but that would be by directly reading the flat file, not by using a row from the buffer.|||

You could try something like this-

IDTSInput90 input = ComponentMetaData.InputCollection[inputID];

This is a design-time action, in the same way as you would "normally" use the flat file source to load a CSV file, and let the designer UI figure out the columns. This will not allow you to change the file layout at run-time, and magically load any file you happen to find. You area aware of this distinction?

From a design pattern perspective, this is not the place to be selecting and generating columns. It would be more sensible to do this either in a UI or ReinitializeMetadata. Validate could detect the stupid state of no input columns selected, and/or it not matching the input, and call RMD.

A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.

|||

Thanks,

I'm trying to solve the problem related to a CSV source missing columns for some rows, it's been brought up a few times here in the past but I haven't seen a generic solution that is suitable for multiple DTS packages that are dependent on multiple CSV files all with 50+ columns.

Here's a forum entry on it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17

And Jamie T's explanation with more links:

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx

I'll see if I can get the custom source component working today.

|||

I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

(The site has a page for downloading the examples).

The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).

Here's some of the key code:

Code Snippet

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);

if (cm.CreationName != "FLATFILE")

{

throw new Exception("The Connection Manager is not a FILE Connection Manager");

}

else

{

_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

throw new Exception("The type of FILE connection manager must be an Existing File");

}

else

{

_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

throw new Exception("Nothing returned when grabbing the filename");

}

The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"

Code Snippet

private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)

{

if (_filename != null || _filename.Length > 0)

{

TextReader tr = File.OpenText(_filename);

string columns = tr.ReadLine();

tr.Close();

_columnNames = columns.Split(",".ToCharArray());

foreach (string columnName in _columnNames)

{

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = columnName.Trim();

outName.Description = columnName.Trim();

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

//Create an external metadata column to go alongside with it

CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);

}

Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.

Code Snippet

private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)

{

IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);

IDTSExternalMetadataColumn90 mdc;

bool rtnVal = true;

int cCount = 0;

foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)

{

if (col.ExternalMetadataColumnID == 0)

{

rtnVal = false;

}

else

{

//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];

mdc = output.ExternalMetadataColumnCollection[cCount];

if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision

|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)

{

rtnVal = false;

}

cCount++;

}

return rtnVal;

}

This was the other change I needed to make to the example, the collection index doesn't match the column ID.

I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.

Create output columns based on input in custom component

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).

Are there any good examples for creating output columns dynamically based on the input buffer?

Should I just give up on on the transform and create a custom source component instead?

You could try something like this-

IDTSInput90 input = ComponentMetaData.InputCollection[inputID];

A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.

|||

Thanks,

Here's a forum entry on it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17

And Jamie T's explanation with more links:

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx

I'll see if I can get the custom source component working today.

|||

I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

(The site has a page for downloading the examples).

The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).

Here's some of the key code:

Code Snippet

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);

if (cm.CreationName != "FLATFILE")

{

throw new Exception("The Connection Manager is not a FILE Connection Manager");

}

else

{

_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

throw new Exception("The type of FILE connection manager must be an Existing File");

}

else

{

_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

throw new Exception("Nothing returned when grabbing the filename");

}

The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"

Code Snippet

private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)

{

if (_filename != null || _filename.Length > 0)

{

TextReader tr = File.OpenText(_filename);

string columns = tr.ReadLine();

tr.Close();

_columnNames = columns.Split(",".ToCharArray());

foreach (string columnName in _columnNames)

{

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = columnName.Trim();

outName.Description = columnName.Trim();

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

//Create an external metadata column to go alongside with it

CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);

}

Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.

Code Snippet

private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)

{

IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);

IDTSExternalMetadataColumn90 mdc;

bool rtnVal = true;

int cCount = 0;

foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)

{

if (col.ExternalMetadataColumnID == 0)

{

rtnVal = false;

}

else

{

//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];

mdc = output.ExternalMetadataColumnCollection[cCount];

if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision

|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)

{

rtnVal = false;

}

cCount++;

}

return rtnVal;

}

This was the other change I needed to make to the example, the collection index doesn't match the column ID.

I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.

Create output columns based on input in custom component

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).

Are there any good examples for creating output columns dynamically based on the input buffer?

Should I just give up on on the transform and create a custom source component instead?

You could try something like this-

IDTSInput90 input = ComponentMetaData.InputCollection[inputID];

A source may be cleaner, or even a package generator. I am not clear on what you are really trying to do, and what problem you need to solve.

|||

Thanks,

Here's a forum entry on it:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2025483&SiteID=17

And Jamie T's explanation with more links:

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_--Flat-File-Connection-Manager-issues.aspx

I'll see if I can get the custom source component working today.

|||

I was able to get the source component working based off an example from Professional SQL Server 2005 Integration Services

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html

(The site has a page for downloading the examples).

The example for creating a source component had a couple errors in it (probably from being based off a pre-release version of SSIS).

Here's some of the key code:

Code Snippet

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager);

if (cm.CreationName != "FLATFILE")

{

throw new Exception("The Connection Manager is not a FILE Connection Manager");

}

else

{

_fileExist = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (_fileExist != Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

throw new Exception("The type of FILE connection manager must be an Existing File");

}

else

{

_filename = ComponentMetaData.RuntimeConnectionCollection["File To Read"].ConnectionManager.AcquireConnection(transaction).ToString();

if (_filename == null || _filename.Length == 0)

{

throw new Exception("Nothing returned when grabbing the filename");

}

The original example checked "if (cm.CreationName != "FILE")" which should actually be "if (cm.CreationName != "FLATFILE")"

Code Snippet

private void CreateOutputAndMetaDataColumns(IDTSOutput90 output)

{

if (_filename != null || _filename.Length > 0)

{

TextReader tr = File.OpenText(_filename);

string columns = tr.ReadLine();

tr.Close();

_columnNames = columns.Split(",".ToCharArray());

foreach (string columnName in _columnNames)

{

IDTSOutputColumn90 outName = output.OutputColumnCollection.New();

outName.Name = columnName.Trim();

outName.Description = columnName.Trim();

outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);

//Create an external metadata column to go alongside with it

CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);

}

Just to get the sample working all columns are strings for the moment, for my needs this is all I needed anyways.

Code Snippet

private bool DoesEachOutputColumnHaveAMetaDataColumnAndDoDatatypesMatch(int outputID)

{

IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(outputID);

IDTSExternalMetadataColumn90 mdc;

bool rtnVal = true;

int cCount = 0;

foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)

{

if (col.ExternalMetadataColumnID == 0)

{

rtnVal = false;

}

else

{

//mdc = output.ExternalMetadataColumnCollection[col.ExternalMetadataColumnID];

mdc = output.ExternalMetadataColumnCollection[cCount];

if (mdc.DataType != col.DataType || mdc.Length != col.Length || mdc.Precision != col.Precision

|| mdc.Scale != col.Scale || mdc.CodePage != col.CodePage)

{

rtnVal = false;

}

cCount++;

}

return rtnVal;

}

This was the other change I needed to make to the example, the collection index doesn't match the column ID.

I'll try to post the full source code online if I get some time so that hopefully it saves someone else the trouble.

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 29, 2012

Create Table with current date as part of the table name

Create Table with current date as part of the table name

Sunday, March 25, 2012

Create table - default for column (sql 2000)

Thursday, March 22, 2012

Create SQL table from Excel or DataTable?

Monday, March 19, 2012

create report incrementing date on left and results based on that date in subsequent

Create query based on a field that wont be the same value in both tables

Sunday, March 11, 2012

Create output columns based on input in custom component

Create output columns based on input in custom component

Create output columns based on input in custom component

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