1
votes

The sample table T0 represents the GL master (General Ledger master)

T1 represents the opening balances for GL + Department combination (the real data will have the balances by GL, Department, Project, Business Segment, etc)

T2 represents the net balances for current period for GL + Department combination (again the real data will have the grouping by many more columns)

T3 represents the Department master

The real data will have master tables for Department, Project, Business Segment, etc.

I want the output to have GL Code, GL Name, Department Code, Department Name, (and in the real data, the Project Code & Name, Business Segment Code & Name, etc) the Opening Amt for the combination and the Net amount for the combination.

Below is the query with sample hardcoded data:

WITH 
T0 AS (
SELECT 'GL001' AS GLCode, 'Name001' AS GLName
UNION 
SELECT 'GL002' AS GLCode, 'Name002' AS GLName
UNION 
SELECT 'GL003' AS GLCode, 'Name003' AS GLName
UNION 
SELECT 'GL004' AS GLCode, 'Name004' AS GLName
),
T1 AS (
SELECT 'GL001' AS GLCode, 'D001' AS DeptCode, 'OB001-1' AS OpAmt
UNION 
SELECT 'GL001' AS GLCode, 'D004' AS DeptCode, 'OB001-2' AS OpAmt
UNION 
SELECT 'GL003' AS GLCode, 'D005' AS DeptCode, 'OB003-1' AS OpAmt
UNION 
SELECT 'GL003' AS GLCode, NULL AS DeptCode, 'OB003-2' AS OpAmt
UNION 
SELECT 'GL003' AS GLCode, '' AS DeptCode, 'OB003-3' AS OpAmt
),
T2 AS (
SELECT 'GL002' AS GLCode, 'D007' AS DeptCode, 'NET002' AS NetAmt
UNION 
SELECT 'GL003' AS GLCode, 'D008' AS DeptCode, 'NET003-1' AS NetAmt
UNION 
SELECT 'GL003' AS GLCode, 'D005' AS DeptCode, 'NET003-2' AS NetAmt
),
T3 AS (
SELECT 'D001' AS DeptCode, 'DName001' AS DeptName
UNION
SELECT 'D002' AS DeptCode, 'DName002' AS DeptName
UNION
SELECT 'D004' AS DeptCode, 'DName004' AS DeptName
UNION
SELECT 'D005' AS DeptCode, 'DName005' AS DeptName
UNION
SELECT 'D007' AS DeptCode, 'DName007' AS DeptName
UNION
SELECT 'D008' AS DeptCode, 'DName008' AS DeptName
)
SELECT T0.GLCode, T0.GLName, ISNULL(T1.DeptCode, T2.DeptCode) AS DeptCode, 
T3.Deptname, 
T1.OpAmt, T2.NetAmt
FROM 
T0 LEFT OUTER JOIN T1 ON T0.GLCode = T1.GLCode
LEFT OUTER JOIN T2 ON T0.GLCode = T2.GLCode
LEFT OUTER JOIN T3 ON ISNULL(T1.DeptCode, T2.DeptCode) = T3.DeptCode
WHERE T1.OpAmt IS NOT NULL OR T2.NetAmt IS NOT NULL
ORDER BY GLCode, DeptCode

The above query's output has a few issues:

  1. The combination GLCode = GL003 and DeptCode = NULL is not captured in the output
  2. All the rows for GL003 have the same value NET003 for the NetAmt, whereas only the combination GL003, D008 should have NET003.
  3. The combination GL003, D005 is coming 3 times, whereas it should come only once.

How can I fix this query and how can I scale it so that it works even when I include other grouping columns for Project, Business Segment, etc.? The query should correctly handle NULL value and blank value in any grouping column.

Wherever the key columns are same (GLCode, DeptCode in this example), the OpAmt from T1 and NetAmt from T2 should be shown in a single row. Wherever the key columns are different, only one of OpAmt or NetAmt should have the value and the other should be NULL.

I want the result to have 1 row for each GLCode, DeptCode combination, and it should have OpAmt (if available, else NULL) and NetAmt (if available, else NULL)

For example, the result should have the OpAmt and NetAmt for the following combinations:

GL001, Name001, D001, DName001, OB001-1, NULL
GL001, Name001, D004, DName004, OB001-2, NULL
GL002, Name002, D007, DName007, NULL, NET002
GL003, Name003, D005, DName005, OB003-1, NET003-2
GL003, Name003, D008, DName008, NULL, NET003-1
GL003, Name003, NULL, NULL, OB003-2, NULL, 
GL003, Name003, '', NULL, OB003-3, NULL, 

Each result row should have the GLName and DeptName as well

In the real query, I will have to repeat the full query for 5 databases, as there are 5 business entities and each business entity is in a separate database, so I will have to prefix each database using [DB_NAME].[dbo].[table_name] syntax. So I will need to combine more than 20 CTE queries, say 4 for each database. (actually more than 4 for each database, as the real data will have columns and tables for Projects and Business Segments). How should I improve the query structure, readability and maintainability?

What are the expected results here, exactly?Larnu
Wherever the key columns are same (GLCode, DeptCode in this example), the OpAmt from T1 and NetAmt from T2 should be shown in a single row. Wherever the key columns are different, only one of OpAmt or NetAmt should have the value and the other should be NULL.AllSolutions
I want the result to have 1 row for each GLCode, DeptCode combination, and it should have OpAmt (if available, else NULL) and NetAmt (if available, else NULL)AllSolutions
Don't repeat your description, actually show us the expected results in your question with an edit.Larnu
@Larnu I guess I have been doing that for a long time. :-)Aaron Bertrand