x

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


![alt text][2]

[2]: http://ask.sqlservercentral.com/storage/temp/166-finalinfo.jpg
source.jpg (69.9 kB)
finalinfo.jpg (176.5 kB)
more ▼

asked Apr 14, 2012 at 09:05 PM in Default

cdurham gravatar image

cdurham
230 22 22 24

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Apr 15, 2012 at 08:57 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@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!
Apr 16, 2012 at 01:33 PM cdurham

@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.
Apr 17, 2012 at 07:00 AM WilliamD

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[link text][2]

[2]: http://ask.sqlservercentral.com/storage/temp/177-modifiedcode.txt
output1.jpg (238.1 kB)
modifiedcode.txt (1.3 kB)
Apr 18, 2012 at 08:36 PM cdurham
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x107
x60
x2

asked: Apr 14, 2012 at 09:05 PM

Seen: 995 times

Last Updated: Apr 19, 2012 at 07:08 AM