Needing help in creating transactional type modifications in SQL Server 2008 R2 for Quickbooks

Question

question

cdurham asked Apr 14, '12

Needing help in creating transactional type modifications in SQL Server 2008 R2 for Quickbooks

Hello all, I am a DBA of about 7 months. The company that I work for has put me in charge of changing our sales import process. Up until 2 months ago, I use to import files from SQL Server 2005, perform ETL with FileMaker Pro 11.2, then export 2 files, a Quickbooks "cash" file and a Quickbooks "invoice" file. These files were exported in .iif format (Intuit Interchange Format). We have a new point of sales system and FileMaker Pro is now obsolete. I have created a rough draft of the cash and invoice files. I must now find a way to add the quickbooks format **using** SQL Server 2008 R2. I have attached 2 pictures, kind of like a before and after picture. The Filemaker process is somewhat broken, so you need not pay any attention to the amounts. You can ignore the memo field also in the after picture.
Quickbooks requires that you have a header row (the first 3 rows in the 2nd picture), then a TRNS for the first transactionline, and an SPL for any additional transaction line per line item. At the end of the trasnaction line item, an ENDTRNS is inserted. When a new line item appears, the TRNS is put at the beginning of the transaction and the process repeats. If there is only one transaction line for a line item, you will only have a TRNS and an ENDTRNS. When the transactions end of records is reached, a final ENDTRNS is inserted, and thats it. I need to know if it is possible to create the TRNS, SPL, and ENDTRNS in SQL Server 2008 R2. You will see that in the formatted file, when there is a change in store (DWF03, DFW04, etc) you will see a change in the transactions. adding the cash sale to the rows is no problem. I just need to add the first two header rows, an !endtrns row, then the actual line item formats (the trns, spl(if there is more than one line item, in my example it is) and an endtrns). Anyone's knowledge of this matter would be greatly appreciated. My supervisor and I had a talk this week, and if anyone is familiar with this type of process, or has an idea of how to make this process easier or better please let me know. Thanks! ![alt text][1]
![alt text][2] [1]: http://ask.sqlservercentral.com/storage/temp/165-source.jpg [2]: http://ask.sqlservercentral.com/storage/temp/166-finalinfo.jpg

insert transaction iif

source.jpg (68.2 KiB)

finalinfo.jpg (172.3 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-04-15T20:57:08Z

WilliamD answered Apr 15, '12

Hi there, the details seem straight forward and I think that reporting services/report builder or SSIS might be the best way to get this done. Report Builder will allow you to build up the expected results in an easy enough way. You would define groupings by the class column and then give the groups a header and a footer to give you the split and allowing you to add the "ENDTRNS" line along with the entire header lines. Be that as it may, I have neither system at hand and thought that this can also be done in T-SQL (it is sunday evening and nothing is on TV!). Give the following a try to see how it works for you: /* Create the test data according to the expected output */ DECLARE @MyTable AS TABLE (accnt int, invitem varchar(50), amount decimal(10, 2), bookdate date, class char(5), PRIMARY KEY CLUSTERED (class, bookdate, accnt)) ; INSERT INTO @MyTable (accnt, invitem, amount, bookdate, class) VALUES (1104,'VEND_REBATE',2171.94,'20120227','DW03'), (4105,'COMP_UPG',-1687,'20120227','DW03'), (4205,'COMP_FTR',-449.91,'20120227','DW03'), (4505,'COMP_PPD_ACT',-35,'20120227','DW03'), (1104,'VEND_REBATE',1609,'20120227','DW04'), (4105,'COMP_UPG',-749,'20120227','DW04'), (4205,'COMP_FTR',-860,'20120227','DW04'), (1104,'VEND_REBATE',320,'20120227','DW06'), (4105,'COMP_UPG',-320,'20120227','DW06'), (1104,'VEND_REBATE',470,'20120227','DW08'), (4105,'COMP_UPG',-155,'20120227','DW08'), (4205,'COMP_FTR',-280,'20120227','DW08'), (4505,'COMP_PPD_ACT',-35,'20120227','DW08') /* Start the work */ ; WITH helpdata /* helpdata constucts the ENDTRNS lines and ensures they appear at the end of each "CLASS" set */ AS (SELECT 'ENDTRNS' trntype, class, MAX(accnt) + 1 AS accnt, MAX(bookdate) AS bookdate FROM @MyTable AS MT GROUP BY class), basedata /* basedata merges the helpdata with the original data */ AS (SELECT accnt, invitem, amount, bookdate, class FROM @MyTable AS MT UNION SELECT accnt, NULL AS invitem, NULL AS amount, bookdate AS bookdate, class FROM helpdata), prepdata /* prepares all the data for output */ AS (SELECT CASE WHEN invitem IS NULL THEN NULL ELSE accnt END accnt, invitem, amount, CASE WHEN invitem IS NULL THEN NULL ELSE bookdate END bookdate, CASE WHEN invitem IS NULL THEN NULL ELSE class END class, -1 * (ROW_NUMBER() OVER (PARTITION BY class ORDER BY bookdate, accnt) - ROW_NUMBER() OVER (ORDER BY class, bookdate, accnt)) grp, /* this identifies the data into their respective groups */ CASE WHEN accnt = 1104 THEN 'TRNS' WHEN invitem IS NULL THEN 'ENDTRNS' ELSE 'SPL' END AS linetype, CASE WHEN invitem IS NOT NULL THEN 'CASH SALE' END transtype, ROW_NUMBER() OVER (PARTITION BY class ORDER BY bookdate, accnt) ord FROM basedata AS MT) SELECT linetype, transtype, bookdate, invitem, '' memo, accnt, amount, class FROM prepdata ORDER BY grp, ord

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cdurham commented · Apr 16, 2012 at 01:33 PM

@William Durkin, Thank you so much for your answer! I have one question. In the example pictures that I attached, these are only a few of the records. The cash file deals with about 300 rows and the invoice file (as i have used in my example) has about 110 rows. In your code, is there a way of doing an insert of all of the records into @MyTable without inserting the data individually? Also, is Report Builder the same thing as SSRS? Once again, thanks!

0 ·

WilliamD commented · Apr 17, 2012 at 07:00 AM

@cdurham - The example table @MyTable can be swapped for any other table, I was only using it as an example to prove the point. You can replace @MyTable with any other select statement or table, just change the first 2 CTEs in my example to reflect that change. As to Report Builder - that is the design tool to create reports for SSRS that ships with 2008R2. It would allow you to design a report with the output similar to the picture you supplied.

0 ·

cdurham commented · Apr 18, 2012 at 08:36 PM

William, Thanks for your help. With your code, I got the endtrns where it should be. How would I put the "trns" for each beginning transaction, regardless of what the beginning transaction is when there is a change in "class". Your help yould be greatly appreciated once again. ![alt text][1][link text][2] [1]: http://ask.sqlservercentral.com/storage/temp/178-output1.jpg [2]: http://ask.sqlservercentral.com/storage/temp/177-modifiedcode.txt

0 ·

output1.jpg (232.5 KiB)

modifiedcode.txt (1.3 KiB)

question