Alt.Net Open Spaces Houston Conference

April 3rd - April 5th, 2009

Grassroots in our area have planned the first ever Open Spaces conference in Houston.  This is a very affordable ($25) and flexible conference for people to be able to attend since it starts on Friday and runs through the weekend.

What is Open Spaces?

Open space is guided by a few simple but powerful principles:

Whoever shows up is the right group. Whatever happens is the only thing that could have. Whenever it starts is the right time. When it's over, it's over.

An Open Space conference's agenda is decided upon by the conference participants during the opening of the event.

You can identify an issue relating to the theme of the conference for which you have a genuine passion and for which you will take real responsibility.

Think of powerful ideas that really grab you to the point that you will take personal responsibility to make sure that something gets done at the conference.

You may lead sessions, you may participate as an attendee, you may take responsibility for capturing proceedings, or you may just hang out in the halls and talk with interesting people.

Registration is now Open

http://houston.altnetconf.com/home

Concatenating Date and Time in T-SQL

Sometimes you just want to concatenate the date section from a DateTime and the time section from another DateTime.  These could even be the new SQL Server Date or Time data types.  The easiest way I've found to do this is by combining DATEPART with DATEADD.  You get rid of the parts of each DateTime that you don't want and then add based on the lowest common denominator this being millisecond.

'ProcessedDate' = DATEADD(ms, DATEDIFF(ms, 0, [ProcessingTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [ProcessingDate])))

 

Generating Seed Data Through Select Statements

Often times you will need to generate seed data for a table such as a cross reference table when the data for this already exists in the database already.  Sometimes this is done when you decide to refactor your table design to include a cross reference table (many to many) where a one to many relationship existed previously.  If you have the seed data in your table already you can generate a host of SQL statements (usually INSERTS) for your database.

By issuing a SQL Statement like below, you need to understand the SQL Statement that you desire.  In this instance if you have a cross reference table that you need to generate between Customers and Orders you would have a desired INSERTS like below:

INSERT INTO customer_orders_xref VALUES (1, 1)

INSERT INTO customer_orders_xref VALUES (1, 2) 

INSERT INTO customer_orders_xref VALUES (3, 1)

In instances where you can have dozens or hundreds of desired SQL statements this can be tedious and error prone.  Instead if the data exists in some form in the database you can have it outputted with a simple SQL statement like the one below:

SELECT 'INSERT INTO customer_orders_xref VALUES (' + CAST(c.order_id AS VARCHAR(10)) + CAST(c.customer_id AS VARCHAR(10)) + ')'

FROM orders o

INNER JOIN customer c ON o.customer_id = c.customer_id

WHERE c.name LIKE '%Williams%'

If the first parameter was a fixed value you would just hard code it in your SQL.

SELECT 'INSERT INTO customer_orders_xref VALUES (2, ' + CAST(c.customer_id AS VARCHAR(10)) + ')'

FROM orders o

INNER JOIN customer c ON o.customer_id = c.customer_id

WHERE c.name LIKE '%Williams%'

 You could use techniques like this to generate all sorts of SQL Statements.  Let's say we added an Account Alias column (acct_alias) to the Account table and now we need to change them all to some sort of intelligible name.

SELECT 'UPDATE account SET acct_alias = ''Account# ' + c.name + ''') WHERE account_id = ' + c.account_id FROM customer 

For more complicated SQL generation you can use a code generation tool such as MyGeneration (free Open Source).