Query by Combining 2 tables.

what is the data type of Month1...Month12 on the table? is it a datetime field?

Hope this helps:
Kindly fill in the gaps for the rest of the months.

SELECT t1.AccNo, t1.Month08 as Month01, t1.Month09 as Month02, ..., t1.Month12 as Month05, t2.Month1 as Month06, ..., t2.Month07 as Month12
FROM Table2008 t1, Table2009 t2
WHERE t1.AccNo = t2.AccNo

Select allOpen in new window

Hi rrjegan17.

Month01 to Month12 are Money Type.

Thank you.

One more.

Data on Table2008 may not be the same with Table2009.

Thank you.

hi emi,

is rrjegan17's query satisfactory? If not can you give us an example with the data in the table?

Can you provide some sample records for existing table and your desired result set so that it would be helpful to understand better.

Hi All,

I am sorry, just back again.

Please see my tables, I export it into excel.

Thank you.

TABLE2007.xls
TABLE2008.xls

Just saw your sample records.

Kindly provide the expected result set to help you out better.

Ok.

Please the screen shot. It just could server the same year.
That's why I ask for help here.

Forget about those fields in excel Debet01, Kredit01 ---> Month01, Debet02, Kredit02 ---> Month02, to make it easy to make a query sample.

Table2007 of Data (Key AccNo) may not be the same as in Table2008.
Some AccNo Data exist in Table2007 but not in Table2008, and vice versa.

Thank you.
SAMPLE-RESULT.PNG

If you need all records from both tables, then use full outer join
SELECT (t1.kredit08 - t1.debet08) as month01, ...
FROM Table2007 t1 FULL OUTER JOIN Table2008 t2
ON t1.AccNo = t2.AccNo;

My query as per comment 24783224 would provide you exactly what you need.
have you tried that one out.

Hi awking00,

Could the AccNo has the unique rows with all the month required?

Thank you.

rrjegan17,
Your query would only provide records where the acctno existed in both tables, which is why I said a full outer join is needed. Other than that, it also looks like some math needs to be done on the debet and kredit fields in the two tables to combine them.

Hi rrjegan17,

I doubt below WHERE t1.AccNo = t2.AccNo, why using "where", since they may not have the same data.

Thank you.

emi_sastra,
I'm not sure I understand your last question. The AccNo will be unique assuming it is unique in the two source tables. If the AccNo exists in both tables, then there will be a value for all of the months. If it exists in one table and not the other, then the values will be null for the periods in which it doesn't exist.

But as per the sample records provided and the results shown, I dont think it as a Full outer join.
Kindly refer the attached sample records.

From your attached sample records, in table2008, there is AccNo 156001 that doesn't exist in table2007, and in table2007 there is AccNo 610203 that doesn't exist in table2008. In order to include these records in the results table, you need to do the full outer join.

Hi All,

Please see my code below.
It just using one  year table.

How to use two year table?
If could make it simpler is appreciated.

Thank you.

WITH CTE AS (SELECT     A.AccNo, 
                                                      A.Debet01 + A.Debet02 + A.Debet03 + A.Debet04 + A.Debet05 + A.Debet06 + A.Debet07 + A.Debet08 + A.Debet09 + A.Debet10 + A.Debet11
                                                       + A.Debet12 AS TotalDebet, 
                                                      A.Kredit01 + A.Kredit02 + A.Kredit03 + A.Kredit04 + A.Kredit05 + A.Kredit06 + A.Kredit07 + A.Kredit08 + A.Kredit09 + A.Kredit10 + A.Kredit11
                                                       + A.Kredit12 AS TotalKredit, CASE WHEN Saldo = 'D' THEN COALESCE (Debet09, 0) - COALESCE (Kredit09, 0) ELSE COALESCE (Kredit09,
                                                       0) - COALESCE (Debet09, 0) END AS Bln01, CASE WHEN Saldo = 'D' THEN COALESCE (Debet10, 0) - COALESCE (Kredit10, 0) 
                                                      ELSE COALESCE (Kredit10, 0) - COALESCE (Debet10, 0) END AS Bln02, CASE WHEN Saldo = 'D' THEN COALESCE (Debet11, 0) 
                                                      - COALESCE (Kredit11, 0) ELSE COALESCE (Kredit11, 0) - COALESCE (Debet11, 0) END AS Bln03, 
                                                      CASE WHEN Saldo = 'D' THEN COALESCE (Debet12, 0) - COALESCE (Kredit12, 0) ELSE COALESCE (Kredit12, 0) - COALESCE (Debet12, 0) 
                                                      END AS Bln04, B.GlobalAcc
                               FROM          THLEDGER2007 AS A LEFT OUTER JOIN
                                                      TMGLACCNO AS B ON A.AccNo = B.AccNo LEFT OUTER JOIN
                                                      TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
                               WHERE      (B.Global <> 'Y') AND (C.Tipe = 'L'))
    SELECT     A.GlobalAcc AS AccNo, B.Name, CASE WHEN Saldo = 'D' THEN SUM(A.TotalDebet) - SUM(A.TotalKredit) ELSE SUM(A.TotalKredit) - SUM(A.TotalDebet) 
                            END AS Total, SUM(A.Bln01) AS Bln01, SUM(A.Bln02) AS Bln02, SUM(A.Bln03) AS Bln03, SUM(A.Bln04) AS Bln04, B.GlobalAcc, C.Saldo
     FROM         CTE AS A LEFT OUTER JOIN
                            TMGLACCNO AS B ON A.GlobalAcc = B.AccNo LEFT OUTER JOIN
                            TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
     WHERE     (A.GlobalAcc <> '') AND (A.TotalDebet <> 0) OR
                            (A.GlobalAcc <> '') AND (A.TotalKredit <> 0)
     GROUP BY A.GlobalAcc, B.Name, B.GlobalAcc, C.Saldo

Select allOpen in new window

awking00,
   Oops.. Yes it needs to be a FULL OUTER JOIN instead of INNER JOIN.

Hi awking00,

Any suggestion from my code ?

Thank you.

Please my code and the result.

Why it produces so many null?

Thank you.

SELECT     t1.AccNo, t1.Debet08, t1.Kredit08
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
ORDER BY t1.AccNo

Select allOpen in new window

COMBINE-RESULT.xls

Please also see the comparison.

The query below has missing some AccNo.

SELECT     t1.AccNo, t1.Debet08, t1.Kredit08
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
ORDER BY t1.AccNo

Thank you.
COMPARISON-RESULT.xls

Sorry, should be:

SELECT     t1.AccNo
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
WHERE     (t1.AccNo IS NOT NULL)

Thank you.

That's how full outer join works..

Full outer join brings the following records in your result set

1. Common records between both tables.
2. Records present in table1 and not in table2 will have Null values for the second table columns.
3. Records present in table2 and not in table1 will have Null values for the first table columns.

Instead of this,

SELECT     t1.AccNo
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
WHERE     (t1.AccNo IS NOT NULL)

you can use

SELECT     t1.AccNo
FROM         THLEDGER2007 AS t1 LEFT OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo

Hope this helps

Hi rrjegan17,

Using LEFT JOIN will not solve the problem.

Thank you.

Please see the "COMPARISON RESULT".

Thank you.

See attached.
comments.txt

emi_sastra,
     Can you confirm what are the records you require:

1. Records present in both 2007 and 2008.
2. Records present in 2007 and not in 2008. (If required kindly tell me how those records to be presented from query).
3. Records present in 2008 and not in 2007. (If required kindly tell me how those records to be presented from query).

Based on your confirmation, I can provide you the appropriate query.

I just realized in my comments that I gave you Oracle syntax, but I'm not sure which dbms you're actually using. Most dbms have a function like nvl that says if the first expression equates to null then take the second expression.

Hi rrjegan17,

Point 1 base on user selection  :

From Month, To Month, From Year, To Year.

Thank you.

HI awking00,

I use Ms SQL, if you notice the tags.

Thank you.

>> Point 1 base on user selection  :

Then INNER JOIN in my earlier comment 24783224 should help you out.
Kindly fill in for the rest of the columns.

Hi rrjegan17,

I have tried it, miss below AccNo.

1311010105
1311010105
1311010106
1311010106

610203     
610412

Thank you.

Kindly confirm whether you have records are in proper manner for these values. I suspect there's something wrong with data like additional space like that in these values.

1311010105
1311010105
1311010106
1311010106

and the below records are not found in both table as in your attached sheet.

610203    
610412

1311010105 (ROWS 14)  AND 1311010106  (ROWS 15) ON TABLE2008 ONLY.

610203 (ROWS 24)  AND 610412 (ROWS 34) ON TABLE2007 ONLY.

Please see it again on the sheet.

Thank you.

As we use INNER JOIN , it will fetch records that are present in both tables only.
And that's why I asked this option earlier.

>> 1. Records present in both 2007 and 2008.

Hence it is correct right.

I think I miss understood your questioned.

I want all of them appear on the query result.

For example :

Table Structure for Both Table:

1. AccNo.
2. Month01
3. Month02
4. ..
5. Month12

TableA :

AccNo   Month01 to Month10    Month11   Month12
A                          0                   1               2

TableB:

AccNo   Month01   Month02
A          3               4
B          1               2

Result Query:

I just want to query AccNo, Month11, Month12 from TableA and
AccNo, Month02, Month02 From TableB.

AccNo  Month01   Month02   Month03   Month04
A         1               2               3               4
B         0               0               1               2

Hope this is clear enough.

As I mentioned in my question description, this is a very challenging query if not possible.

Thank you.

Hi awking00,

It is so simple.
I never thought it could be so simple.

I have not tried it yet on my real table, but I am sure it will work.

Thank you very much for your help.

emi_sastra,
    Saw your comment no 24816507 just now.
If this was provided earlier, I would have been more clear in my understanding about your requirement and glad to have your problem resolved by now.

Yes, it is hard enough to solve the problem even though the solution could be simple.
How many attempt to solve this problem.
The question is quite obvious, that's why awking00 provide FULL JOIN NOT LEFT JOIN.

The first try produce null. You didn't notice that the null actually should have value on it.
But awking00 noticed it, then the last code awking00 provided using another select to get the result.

select isnull(t7acc,t8acc) as AccNo --> Avoid null just get null value from one of those tables.

t7acc is AccNo From THLEDGER2007
t8acc is AccNo From THLEDGER2008

select t7.accno as t7acc
       ,t8.accno as t8acc

The above code solve the null problem.

Anyway, I should thank you for your kindly help trying to solve my problem.

Thank you.