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
