I have the following 2 tables:

(BATCHES)
BatchID [int] KEY
ID [int]
OrderID [int]
Action1DateTime [datetime]
Action2DateTime [datetime]
Action3DateTime [datetime]
Action4DateTime [datetime]
Action5DateTime [datetime]
Action6DateTime [datetime]
Action7DateTime [datetime]
Action8DateTime [datetime]

(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.

I need the data to draw a graph for each ActionXDateTime as a function of
date.

Any help appreciated.

/Henrik

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

Thanx very much Ed.

I do believe you really fixed my problem here.

Regards
Henrik

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Next, get **any** book on RDBMS. Read the chapter about getting rid
of repeated groups in a table -- it is called First Normal Form (1NF)
and it is the foundation of RDBMS.

There is no such thng as a magical universal "id" -- it hs to be the
identifer of a particular kind of entity in your data model.