How to get a three row column out of one using SQL

[Bebe]

I have a table that I am trying to generate a report for. In the table is a customer Id and a vendor Id. In the vendor ID I get two results for the same customer ID. A negative number before the order is processed and a postive number when the item has made full circle. I need to extract every matching customer ID with its corresponding negative and positive number:

right now if i do a search say on customer '10050' i get two vendor numbers on with a negative and one with a postive like this:

10050 -1000
10050 0

I really need it to be:

10050 -1000 0
10051 -1000 0

I need to return all records in a certain time frame not just '10050'

Do I need a temp table. An example would be nice - thank you!!!

[Bebe]

This is written in SQL in an SQL database thanks!!!

[Mongo]

Build a table for your results, with fields: customer id, old vendor number, new vendor number, datetime stamp...

Build a trigger on the table you have, to catch the update portion of each insert. Set the trigger's code to check whether the vendor number is greater than zero. When it's < 0, insert a new record into the results table; else, update the matching customer id field.

Then for yucks, build a update trigger on your results table to delete records that are older than time X. Check your database documentation for the appropriate syntax and examples of triggers.

You should think through how your datetime value and multiple customer id orders are handled. You know me, I exists in an alternate Sybase universe, so I can't give you an MS SQL Server example off the top of my pea brain. Sorry, but hope this helps. ;)

[Bebe]

Is it possible to use a subquery instead? doing a sort of self-join?

[Mongo]

Come on, Bebe. Tweak your learning curve up a couple notches and do the client-server "right" thing.

[Bebe]

I totally appreciate your help and it is the correct method. I was just told not to use triggers so I was trying to find a way to solve this problem.

Im still going to learn and do the trigger method :)

thanks again for all your help!!!

[Mongo]

Okie-dokie. Can you describe the process that puts the data into your table? It sounds as if it is two steps. If that's the case, it may be more efficient to adapt them, rather than creating additional work.

For example, if step one inserts values 10050 & -1000, modify your table to include a third field and keep that step the same. Then make the second step of your process, the one that was inserting 10050 & 0, to instead update the new third field to 0.

I should warn you to carefully consider how you handle your "As Of" time and multiple orders for the same customer/vendor, you haven't described this and it should be a key part of your design.