How To Get this done
-
Hi,
I'd like to get "best practices" methodology. I have an invoice and line items from two tables in one database. I now have to merge the invoices and line items into a temp table. My question what would be the best approach in creating this new temp table:
My output would be something like the following:
InvoiceInvoice AmountDate
1A258/23/2007
1AItem158/24/2007
1AItem258/25/2007
1AItem358/26/2007
1AItem458/27/2007
1AItem558/28/2007
1B308/23/2007
1BItem1158/24/2007
1BItem2158/25/2007
Thanks
-
Not sure what Date represents as it looks like when no item the amount is the sum of the related invocies.
Invoice Line Amount Date
1A 25 8/23/2007
1A Item1 5 8/24/2007
1A Item2 5 8/25/2007
1A Item3 5 8/26/2007
1A Item4 5 8/27/2007
1A Item5 5 8/28/2007
1B 30 8/23/2007
1B Item1 15 8/24/2007
1B Item2 15 8/25/2007
Also what is this temp table supposed to do for you that you have to have it????
-
Sorry about that, my session drop. The purpose of this to export to a flat file, separated by comma delimited. However this data is coming from two data tables and I want to be able to group invoices with their line items where I'm putting the invoice data in the first row and the item data and the next rows once there are no more line items for that invoice start with the next invoice, etc. data should look something like the following:
Invoice Line Amount Date
1A 0 10 SumofItems Invoice date
1 5 item1 date
2 5 item2 date
Thanks,
-
I would most likley use a query with GROUP BY and as I recall GROUPING (sorry not BOL right here) to help setup something like this
Invoice Description Amount Date
1A Total 10 (Date)
1A Item1 5 (Date)
1A Item2 5 (Date)
-
What I really don't understand is why the date keeps changing on one invoice. Our invoices always have one date (well, actually there is date of issue, due date etc., but all of them are valid for the whole invoice), while your invoices seem to have a different date for each line.
Maybe every line is for work done (or goods ordered) at a certain date, but then what date do you use for the "header"? In your example it is a date smaller than any of the dates of lines, which seems strange. I would understand, if that would be MAX(line.date)...
Or is the first line ("header") read from one table, and the other lines of that invoice from another ("line")?
-
The dates represent invoice date for the invoice and line item date(entry date for a line item).
I have two tables an Invoice table that looks something like this:
Invoice Table
InvoiceNo. InvoiceAmount InvoiceDate
1A 10 8/24/2007
Item Table
ItemNo. ItemAmount EntryDate
1 5 8/22/2007
2 5 8/24/2007
I want to be able to merge the two tables not really using a UNION statement (because of other constraints) my result in the new temp table is
InvoiceNo InvoiceAmount InvoiceDate
1A 10 8/24/2007
1 5 Date is not necessary
2 5 Date is not necessary
1B 15 8/24/2007
1 5
2 5
3 5
ETC
Please note that I have two invoices in the above scenario 1A,1B. I need to have the related line items in the same column.
I hope this is clearer.
Thanks,
-
OK, thanks, I think it's clear now. This is pretty ugly format of flat file you have to create, I hate it when the lines are either head or detail and each has different length and different contents. I smell a row-by-row (cursor-like) solution (especially if UNION is not available), but maybe there is some better way how to do it.
I'm afraid you'll have to wait for other people to help you, because so far I've been lucky and managed to avoid this - that is, persuaded the receiving side that I will create either two files (head, detail) or will repeat head information in every line. Maybe you could try that, too? Or is it something you (or your boss) really can't influence? Sometimes it seems impossible, but it's worth a try anyway...
-
This smells like an SAP import file.... It's ugly.
XML should be great for this kind of stuff though
-
Actually you're partially right. This is an import to a financial system call Solomon (which is a MS product). I can't use XML because the import function doesn't recognize XML. Funny how I was brought in to migrate from this financial system to a more robust system, but that has been the last thing on my plate.
My initial thought was to build a cursor or just a while loop but I wanted to check with the board first to see if my thinking was on the right track. I was also looking at Data Shaping but my understanding is you need ADO.NET which begs to ask the question how do I load this SQL Data Shaping into an arraylist?? Can I get away with using Data Shaping in SQL Server 2000.
Thanks,
Joe
-
Well, I have to say that it is probably very similar to the SAP import files I have had to make. Which incidentaly is the only time in my life I have written a VB program.... This was back in 1999/2000.
But being a DBA person, and not a VB person.... I kind of used RBAR on this and put all the data I was going to have to export into a table that looked excatly like the file they wanted (i.e. one record per line, all data concatenated together) then exported that to a file when complete.
This process is obviously not overly optimized.... but it worked, and didn't take too long. If I remember correctly it took about 30 minutes to run. This took every single shipment from one of the top 3 oil companies for a day and put it in a file to upload to their SAP servers.
The process to gather the data was much worse as there was no standard for how to store this data at each production site. So I had everything from SQL output, to Excel files to some stuff I have no idea where it came from, and I am sure I do not want to know. All in all a fun project that I am really glad I was a consultant on and not a full time.....(hint, 50% over time on an annual basis for 2 years! 
-
What tells me what Items go with what Invoice? Can you post the DDL for the tables involved and tell us how the relate to each other.
-
I'm still trying to figure out the table structure myself. This is what I have so far: using the NWIND as a model
I would get the new data for the day; and loop through the data and everytime there is a match insert the different level records(haven't figured out the exact SQL)
BEGIN
/************************************
STEP 1: Identify the new scholarships
************************************/
DECLARE @ARId INT
DECLARE ar_cursor CURSOR
FOR
-- Get the necessary ids
SELECT categoryid FROM categories ORDER BY categoryid
OPEN ar_cursor
FETCH NEXT FROM
ar_cursor
INTO
@ARId
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ARId
INSERT INTO ARInvoiceBatch([A],,[C])
SELECT @ARId,categoryname,[description] FROM categories WHERE categoryid = @ARId
INSERT INTO ARInvoiceBatch([A],,[C])
SELECT @ARId,productname,quantityperunit FROM products WHERE categoryid = @ARId
FETCH NEXT FROM
ar_cursor
INTO
@ARId
END
CLOSE ar_cursor
DEALLOCATE ar_cursor
/******************************
STEP END
*******************************/
END
RETURN
-
The code you have seems very different from what you're describing as wanting to achieve. That being said - if you hope to be able to export the scholarships/invoices out and be sure to have the total at the top - you probably want to preserve the inv# and add something like a "line type" field in your temp table, where linetype=0 if it's the total line, and 1 is a detail record.
At that point - you don't need no stinking cursors:
insert into ARInvoiceBatch (invID, linetype,a,b,c)
select invid, 0, '',invtotal,invdate FROM invoices
insert into ARInvoiceBatch (invID, linetype,a,b,c)
select invid, 1, itemid,itemqty,[description] FROM items
That way you can now export this at will , using invid and linetype as the sort order (just don't include them during export). Of course - by doing this - you've done the same thing as a UNION SELECT statement, just using 2 separate statements.
Finally - depending on where you want to go with this - you might STILL want to go with building an XML construct. It will have several distinct advantages, in that it will preserve its "natural order" (so items don't accidentally float into another invoice, etc...), won't force you to have the same fields in every "row" or data, etc... That way - if your invoice line has 4 fields, and the detail have 12, you don't have to worry about that (XML will allow you to do that). Your export process would then simply be an XSLT transform, turning the XML document you made into whatever delimited file you wish.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I'm using a MacBook Pro so I don't have all the formatting tools. Anyway the purpose of the cursor function in this case is to:
1. Get the new Invoices AKA Scholarships based on criteria provided by the business. I now have a list of Ids to iterate
2. Within the cursor I create and build Insert functions to load new invoices/new items base on the Id - Pesudo code would
While @Id
-- Level 1
Insert into ARInvoiceBatch(....)
SELECT .... FROM mainscholarshiptable WHERE id = @Id
-- Level 2
SELECT ... FROM studentscholarship WHERE scholarshid(fk of mainscholarship table) = @Id
-- Level 3
Another SQL for level 3
Next ID
I really can't use XML in this case because the Financial System does not handle XML (older system). As I mentioned I'm still figuring out the SQL from the existing system.
Thanks,
-
I need to understand more about what you have (the table strucutre aka the DDL)
There are many approaches for example here is a set based potential using Order Details in Northwind to get the OrderID, ProductID and the quantity for each product and sum of quantity for each order in a similar fashion to what you state.
SELECT
*
FROM
(
SELECT
OrderID,
ProductID,
CASE WHEN Grouping(ProductID) = 1 THEN SUM(Quantity) ELSE Quantity END Quantity,
CASE WHEN Grouping(ProductID) = 1 THEN 'Order Total' ELSE 'Line Item' END Descrip
FROM
dbo.[Order Details]
GROUP BY
OrderID,
ProductID,
Quantity
WITH ROLLUP
) A
WHERE
OrderID IS NOT NULL AND
Quantity IS NOT NULL
ORDER BY
OrderID,
Descrip DESC,
ProductID
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply