I'm needing some major help once again!

Question

question

cdurham asked Apr 18, '12

I'm needing some major help once again!

I have [asked this question][1] but I need to explain it more clearly. My company was using Filemaker before a recent migration to create a Cash and Invoice file. The source data came from SQL and was imported into FM and then transformed. FM is now obsolete and I am having to rewrite the Sales Import process over in SQL Server 2008 R2. I have to create a Quickbooks format for 2 files. The attached pictures will explain it all. I am very thankful for those who answered my questions last time. 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. I had to leave off some of it to attach the pictures). 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). Basically, I am just needing a header, and attached row on the side that will detect a change in CLASS (the stores) and a footer for each change in class. Anyone's knowledge of this matter would be greatly appreciated. My supervisor and I had a talk last 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 and please let me know how to implement the solution if possible. If anyone knows how to do this using SSRS or SSIS also please point me in the right direction. If you need any more information please do not hesitate to ask. Thanks!

Note: I have about 500 records for the cash file and 150 for the invoice file. All of the answers that I recieved included an INSERT INTO.... VALUES statement, which would require me to write over 500 values so please keep that in mind if you have an answer to my problem

This is the source table for the cash file: ![alt text][2]
Here is the result that I am needing: ![alt text][3] [1]: http://ask.sqlservercentral.com/questions/88481/needing-help-in-creating-transactional-type-modifi.html [2]: http://ask.sqlservercentral.com/storage/temp/175-sqltable1.jpg [3]: http://ask.sqlservercentral.com/storage/temp/176-output1.jpg

ssrs insert transformation headers

sqltable1.jpg (75.9 KiB)

output1.jpg (232.5 KiB)

5

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.

JohnM commented · Apr 18, 2012 at 06:32 PM

Just to clarify, William's solution that he provided works, you just need to add the header row as shown above?

1 ·

Shawn_Melton commented · Apr 18, 2012 at 08:34 PM

I'm in agreement with @JohnM. It appears William's solution only required you to replace the INSERT statement around to be a SELECT INTO from your source table, as he stated in the comments.

1 ·

cdurham commented · Apr 18, 2012 at 07:40 PM

I tried Williams code, but it would not work because I would have to write about 500 INSERT INTO's and 500 VALUES statements. I believe that I confused everyone with the picture that I posted, which asklsqlentral will only let me upload 1mb, so I had to crop the picture. The actual table(s) that I need to transform will include 600 something rows in all.

0 ·

Shawn_Melton commented · Apr 18, 2012 at 08:25 PM

Added link to previous question...

0 ·

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

@Shawn_Melton and @JohnM I went in and took out the @MyTable out of Williams Code and he said that he only put that in as an example. He told me just to replace the @Mytable with the table that I currently have. You are both correct because William's query does work!! I am just needing to add the TRNS line to every starting transaction when there is a change in class, regardless of what the first line item is. Thank you both for pointing this out to me!

0 ·

Answer 1 · 2012-04-19T07:18:33Z

WilliamD answered Apr 19, '12

@CDurham - having looked at what you have written here and as a comment to the original question, I think I have a solution for you. I had previously made the `linetype` column by interrogating the `accnt` column to decide if it was a 'TRNS' or 'SPL' entry. Now that you say I should just apply 'TRNS' to the first entry per class it is easy enough to fix. Taking my original solution and modifying it should supply you with the desired output. In the final CTE called 'prepdata' I have a CASE statement to decide how to fill the `linetype` column. This can be removed completely from the CTE. You would then move this logic into the final select so that the entire final select like like this: SELECT CASE WHEN ord = 1 THEN 'TRNS' WHEN invitem IS NULL THEN 'ENDTRNS' ELSE 'SPL' END linetype, transtype, bookdate, invitem, '' memo, accnt, amount, class FROM prepdata ORDER BY grp, ord As you can see, the `linetype` is now calculated here. The logic is derived from the column `ord` in the `prepdata` CTE. Here I am numbering all entries for each class found, sorted by `bookdate` and then `accnt`. The CASE statement then gives the first entry per class the linetype of 'TRNS', all subsequent entries 'SPL' and adds the final 'ENDTRNS' per class. Give this a try and let us know if it works (please don't forget to mark answers so that we know when a question has been answered).

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.

question