Good Day Experts!
I am working on a project that I need a some assistance/direction if you can help.
I have a staging table that contains Invoice Numbers and Order Numbers. There are multiple lines with the same Invoice Number as multiple Order Numbers can be invoiced on the same Invoice Number. It looks like the following:
I have to now go get the "detail" for each Order on the Invoice and output a text file for each Invoice. So, the above data would produce 3 separate files(1 for each Invoice).
I could do a select * on the table and then when the InvoiceNumber changes start a new file. However, instead I would like to have the InvoiceNumber as the "OuteLoop" trigger to create the file. But the trouble I am having is figuring how to get those unique InvoiceNumbers while getting a list of the OrderNumbers related to each InvoiceNumber.
Do you think this is possible all in one query? Or do I need to do Select distinct InvoiceNumbers for the outer loop then once inside the loop query the table again for the related OrderNumbers so I can go get the detail information?
Thanks for the suggestions and insight,