HELP WITH REVERSE T-SQL QUERY

Last Post 23 Oct 2008 10:58 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
parlati
New Member
New Member

--
22 Oct 2008 01:32 PM
Hello Folks,

I'm not an expert in T-SQL and help from senior/expert in the field. Here is my challange, please comment this message is something is not understood. My english might not be perfect.

======================================================
Create a query that I can use in a report: Journal Entry with VAT code
======================================================

There's 3-tables to be used to find out what VAT code is used for every relevant general ledger
rows in journal entry table.

1. Tablename = INV1
INV1 is a table that include every line for accounts receivable invoices ~ Invoice_lines
2. Tablename = PCH1
PCH1 is a table that include every line for accounts payable invoices ~ Invoice_lines
3. Tablename = JDT1
JDT1 is a table that include every journal entry lines ~ JournalEntry_lines



RELEVANT COLUMN NAMES:

Tables INV1, PCH1 (these two tables have same schema) have:
TransId | Dscription | AcctCode | LineTotal | VatGroup | VatPrcnt | VatSum | ObjType | DocEntry

Table JDT1 have:
TransId | Account | Debit | Credit | VatGroup | VatRate | VatLine | BaseSum | TransType | CreatedBy


*******************************
EXAMPLE FOR MY CHALLANGE/ISSUE
*******************************
I create an invoice with 3-lines that have different VAT group.
One row have 12% and two rows have 25% VAT percent.

Here is my invoice line table:
TransId | Dscription | AcctCode | LineTotal | VatGroup | VatPrcnt | VatSum | ObjType | DocEntry
198 | Item 1 | 3000 | 2 200 | U1 | 25 | 550 | 13 | 45
198 | Item 2 | 3000 | 1 200 | U2 | 12 | 144 | 13 | 45
198 | Item 3 | 3000 | 500 | U1 | 25 | 125 | 13 | 45


This invoice generate a journal entry in JDT1 automatically as this:
TransId | Account | Debit | Credit | VatGroup | VatRate | VatLine | BaseSum | TransType | CreatedBy
198 | 1500 | 4 719 | - | - | - | N | - | 13 | 45
198 | 2700 | - | 675 | U1 | 25 | Y | 2 700 | 13 | 45
198 | 2705 | - | 144 | U2 | 12 | Y | 1 200 | 13 | 45
198 | 3000 | - | 3 900| - | - | N | - | 13 | 45


This make it difficult for me to create a account statement report with specified VAT-Group.
My wish is to create a query table VIEW that organize the JDT1 as source table manner.


How can i create a table VIEW to look like this:
TransId | Account | Debit | Credit | "custom" | VatRate | VatLine | BaseSum | TransType | CreatedBy
198 | 1500 | 4 719 | - | - | - | N | - | 13 | 45
198 | 2700 | - | 675 | U1 | 25 | Y | 2 700 | 13 | 45
198 | 2705 | - | 144 | U2 | 12 | Y | 1 200 | 13 | 45
"custom"| 3000 | - | 2 700 | U1 | 25 | N | - | 13 | 45
"custom"| 3000 | - | 1 200 | U2 | 12 | N | - | 13 | 45

Please help...
I appreciate any comment... my email geir AT completeone.no

Thank you,
Kind regards,
Geir Parlati
Consultant





nosepicker
New Member
New Member

--
23 Oct 2008 10:58 AM
I don't know all your business rules, so this is what I've come up with so far, based on the information you have given. This may not cover all the different cases you have, but I think you can at least start with this:

SELECT
CASE WHEN JDT1.TransId = INV1Sum.TransId AND JDT1.Account = INV1Sum.AcctCode THEN '"Custom"' ELSE CONVERT(varchar, JDT1.TransId) END AS TransId,
JDT1.Account,
CASE WHEN JDT1.TransId = INV1Sum.TransId AND JDT1.Account = INV1Sum.AcctCode AND JDT1.Debit IS NOT NULL THEN INV1Sum.LineTotal ELSE JDT1.Debit END AS Debit,
CASE WHEN JDT1.TransId = INV1Sum.TransId AND JDT1.Account = INV1Sum.AcctCode AND JDT1.Credit IS NOT NULL THEN INV1Sum.LineTotal ELSE JDT1.Credit END AS Credit,
CASE WHEN JDT1.TransId = INV1Sum.TransId AND JDT1.Account = INV1Sum.AcctCode THEN INV1Sum.VatGroup ELSE JDT1.VatGroup END AS Custom,
CASE WHEN JDT1.TransId = INV1Sum.TransId AND JDT1.Account = INV1Sum.AcctCode THEN INV1Sum.VatPrcnt ELSE JDT1.VatRate END AS VatRate,
JDT1.Vatline,
JDT1.BaseSum,
JDT1.TransType,
JDT1.CreatedBy
FROM JDT1
LEFT JOIN
(SELECT TransId, AcctCode, VatGroup, VatPrcnt, SUM(LineTotal) AS LineTotal
FROM INV1
GROUP BY TransId, AcctCode, VatGroup, VatPrcnt) AS INV1Sum
ON JDT1.TransId = INV1Sum.TransId
AND JDT1.Account = INV1Sum.AcctCode
You are not authorized to post a reply.

Acceptable Use Policy