I have a table (SQL Server) (actually, it is really a few tables that are joined together) that I need to read through and create another table (SQL Server) with some summed up information. Here is a slimmed down example of the EVENTS table...

  
Company # | Customer # | Event In  | Event In Date | Event Out | Event Out Date
----------------------------------------------------------------------------------------
A           001          MAIN_IN_1   09/02/14        SUB_OUT_1   09/04/14
A           001          SUB_IN_1    09/07/14        MAIN_OUT_3  09/12/14
A           002          MAIN_IN_3   09/02/14        MAIN_OUT_2  09/10/14
A           002          MAIN_IN_1   09/15/14        SUB_OUT_2   09/16/14
B           001          MAIN_IN_1   09/02/14        MAIN_OUT_5  09/15/14

Based on the events above, I want to be able to create the following output table (FULL_EVENT)...

  
Company # | Customer # | Event In  | Event In Date | Event Out | Event Out Date | Days In | Nbr of Times to Sub | Total Days
-----------------------------------------------------------------------------------------------------------------------------
A           001          MAIN_IN_1   09/02/14        MAIN_OUT_3  09/12/14         9         1                     11
A           002          MAIN_IN_3   09/02/14        MAIN_OUT_2  09/10/14         9         0                     9
A           002          MAIN_IN_1   09/15/14                                     2         1                               
B           001          MAIN_IN_1   09/02/14        MAIN_OUT_5  09/15/14         14        0                     14

So, I want to read through the first database table (EVENTS) and create the FULL_EVENT table. Any time that the company or customer or a new MAIN event occurs, I would be creating a new row in the FULL_EVENT table. The FULL event ends when a there is an ending MAIN event. Also, whenever this process runs, the FULL_EVENT table will be treated as a new target. So, we would only be doing inserts into this table.

If I can figure out how to read one or more records from one SQL table and then write to another, I can get this to work. I know what I need to do to combine records and I can do all the calculations. I am assuming I can do this with a procedure in iDM. I just have never done something like this before, and I can't find examples. If this is possible, and if someone can share an example, that would be greatly appreciated.

This message has been edited. Last edited by: <Kathryn Henning>,

You can use GROUP BY to combine rows but it seems that you want to do somewhat more than that. But it's a little hard to figure out from your examples because the spaces are compressed. Could you please edit your post using the CODE markup surrounding the tables which you can do with red angle brackets button. Thanks.

This message has been edited. Last edited by: Clif,

I have thrown the tables in CODE markup. Luckily, when I clicked edit, the spacing was still there.

I don't think just group bys will help. Sometimes the Event In is needed to start a new event, and other times it is used to figure out Days In and Nbr of Times to Sub. Same thing with the Event Out. Sometimes it is to end an event, and other times it is used for other pieces of information.

What I really need to do is to read a single line, evaluate it, possibly set some fields or do some calculations in the new table, then get the next line. Basically, I want to be able to control the one table like using a cursor. For my output table, I want to write out to it when I need to.

Thanks

Thanks, now I see where you want to go, but I can't figure out your logic to get there.

You can get from 5 rows down to 4 by doing a group by on Company, Customer and source transform field with a value:
IF EVENTIN LIKE 'MAIN%' THEN EVENTIN ELSE LAST EVENTIN

I hope that helps.