You are here: Re: Sub-Query / Cross Join / or something else? « MsSQL Server « IT news, forums, messages
Re: Sub-Query / Cross Join / or something else?

Posted by Ed Murphy on 03/21/07 16:25

Henrik Juul wrote:

> I have the following 2 tables:
>
> (BATCHES)
> BatchID [int] KEY
> ID [int]

The 'ID' column should be renamed to indicate what it's an ID for.

> OrderID [int]
> Action1DateTime [datetime]
> Action2DateTime [datetime]
> Action3DateTime [datetime]
> Action4DateTime [datetime]
> Action5DateTime [datetime]
> Action6DateTime [datetime]
> Action7DateTime [datetime]
> Action8DateTime [datetime]

This is a classic case of bad design. Here's how to fix the design:

create view BatchesNormalized as
select BatchID, ID, OrderID,
Action1DateTime as ActionDateTime,
1 as ActionNumber -- if order is important
from Batches
where Action1DateTime is not null
union
select BatchID, ID, OrderID,
Action2DateTime as ActionDateTime,
2 as ActionNumber
from Batches
where Action2DateTime is not null
-- similar for 3 through 8

Ideally, you should fix the original table:

1) Create the view shown above
2) Copy its contents to a second table
3) Drop the view
4) Drop the Batches table and re-create it with the same columns
as the view
5) Copy the contents of the second table to the new Batches table

If you already have a lot of code referencing the non-normalized table:

1) Create the view
2) Change SELECTs one at a time to use the view
3) Create stored procedures that wrap around INSERT, UPDATE, and DELETE
4) Change INSERTs/UPDATEs/DELETEs one at a time to use the stored
procedures
5) Fix the table as described above, and at the same time, change the
stored procedure wrappers to use the fixed table

If you can't get rid of the non-normalized table (e.g. you're working
with a third-party software package), then at least create the view and
use it in your own stuff.

> (ORDERS)
> OrderID [int] KEY
> ProductionLineID [int]
> RecipeID [int]
> OrderAmount [int]
>
> Batches.Action1DateTime to Batches.Action8DateTime can have several entries
> each day.
> I need a query to count all Batches.Action1DateTime to all
> Batches.Action8DateTime for each day in a specified period.
> I also need to specifically use where clauses for Orders.OrderID and/or
> Orders.RecipeID.

Once the data is normalized, it becomes simple:

select b.ActionDateTime, count(*)
from BatchesNormalized b
join Orders o on b.OrderID = o.OrderID
where b.OrderID = @OrderID and o.RecipeID = @RecipeID
group by b.ActionDateTime

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация