Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting and combining records from a table with another

Status
Not open for further replies.
infinitizon

infinitizon

MIS
Jul 28, 2011
167
NG
Hi all,

I've been working on a seemingly simple stuff for a while but always meeting a road block. Here it is:

Table - [Customer Ledger Entry table]:
Code: 
Cust No | Document No |
-----------------------
 TD101  |  PSIV30122  |
-----------------------
 TDA202 |  PSV002202  |
-----------------------
 TDP100 |  PSV35565   |
-----------------------
Table - [Posted Document Dimension]:

Code: 
Document No_ | Dimension Code | Dimension Value Code	
PSV002202    | CUSTOMER GROUP |   AM PART SALES	
PSV002202    | CUSTOMER GROUP |   AM PART SALES	
PSV002202    | CUSTOMER GROUP |   AM PART SALES	
PSV002202    |  DEPARTMENT    |      GLLAGOS
PSV002202    |  DEPARTMENT    |      GLLAGOS
PSV002202    |    PROJECT     | 	N51
PSIV30122    | CUSTOMER GROUP |   AM FIELD SERVICE 
PSIV30122    |  DEPARTMENT    |   GLLAGOS 
PSIV30122    | CUSTOMER GROUP |   AM FIELD SERVICE 
PSIV30122    | CUSTOMER GROUP |   AM PART SALES
Now what I want is to do a select that gives me something like this
Code: 
Cust No | Dimension Code |  Dimension Value Code
 TDA202 | CUSTOMER GROUP |      AM PART SALES
 TDA101 | CUSTOMER GROUP |    AM FIELD SERVICE 
 TDA101 | CUSTOMER GROUP |     AM PART SALES
 TDP100 |                |
As you can see TDP100 has no corresponding [Document No] in the [Posted Document Dimension] table, thus no [Dimension Code] or [Dimension Value Code].
Basically, the select statement should give distinct [Dimension Code] or [Dimension Value Code] from [Posted Document Dimension] for each [Document no] in [Customer Ledger Entry table]

Your help is much appreciated

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
 
Sort by date Sort by votes
What is the logic that returns only
Code: 
TDA202 | CUSTOMER GROUP |      AM PART SALES
for
Code: 
 TDA202 |  PSV002202
?
Why are
Code: 
DEPARTMENT    |     GLLAGOS
  PROJECT     |     N51
not also in the results?

Otherwise you'd just need to make a left join between [Customer Ledger Entry table] and [Posted Document Dimension] on field [Document No], and perhaps use the coalesce function to turn the NULLs into blank strings.

Code: 
SELECT DISTINCT T1.[Cust No], COALESCE(T2.[Dimension Code],'') as [Dimension Code], COALESCE(T2.[Dimension Value Code],'') as [Dimension Value Code] FROM [Customer Ledger Entry table] T1 LEFT OUTER JOIN [Posted Document Dimension] T2 ON T1.[Document No] = T2.[Document No]



soi là, soi carré
 
Upvote 0 Downvote
Why you filter
[tt]
PSIV30122 | DEPARTMENT | GLLAGOS
[/tt]
?
What is the logic?

If you want only these documents where Dimension Code is "CUSTOMER GROUP"
Code: 
SELECT DISTINCT CustomerTable.CustNo,
                CustomerTable.DocumentNo,
                PostedDocument.DimensionCode,
                PostedDocument.DimensionValueCode    
FROM CustomerTable 
LEFT JOIN  PostedDocument ON CustomerTable.DocumentNo = PostedDocument.DocumentNo AND
                             PostedDocument.DimensionCode = 'CUSTOMER GROUP'

(not tested)


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Ah; bborissov - I think your interpretation of "want only these documents where Dimension Code is "CUSTOMER GROUP" is the answer and hence your solution (minus selection of CustomerTable.DocumentNo) is the answer to the OP's Q!





soi là, soi carré
 
Upvote 0 Downvote
Thanks guys your solutions, your solutions really worked; especially bborissovs' solution. It worked well.
However, when I tried to join with yet another table, [Detailed Cust_ Ledg_ Entry], I get funny figures.

Here is the full gist based on a single customer number "TDA202" (Sorry for writing a whole book on this):
Code: 
[Cust_ Ledger Entry table]
Entry No_  |  Customer No_  |  Posting Date  |  Document No_
----------------------------------------------------------------
1033045    |     TDA202     |    6/20/2008   |    PSIV20128
1034344    |     TDA202     |    6/24/2008   |    CQ004101
1042812    |     TDA202     |    7/7/2008    |    CQ004166
1064599    |     TDA202     |    8/26/2008   |    PSIV20899
1071200    |     TDA202     |    8/29/2008   |    1661
1126634    |     TDA202     |    12/29/2008  |    CQ005148
1219318    |     TDA202     |    5/28/2009   |    3985
1219493    |     TDA202     |    5/29/2009   |    3991
1222527    |     TDA202     |    6/16/2009   |    CQ006373
1228642    |     TDA202     |    6/24/2009   |    PSIV24693
1297953    |     TDA202     |    9/25/2009   |    PSIV26308
1295440    |     TDA202     |    9/29/2009   |    CH002248
1306787    |     TDA202     |    10/19/2009  |    PSIV26547
1306932    |     TDA202     |    10/19/2009  |    PSIV26549
1326155    |     TDA202     |    11/19/2009   |    CQ007732
1326231    |     TDA202     |    11/19/2009   |    CQ007734
1326248    |     TDA202     |    11/19/2009   |    CQ007737
1335283    |     TDA202     |    11/25/2009   |    PSIV27189
1348761    |     TDA202     |    12/29/2009   |    PSIV27568
1361075    |     TDA202     |    12/31/2009   |    7406
1361077    |     TDA202     |    12/31/2009   |    7406
1531969    |     TDA202     |    4/12/2010   |    CQ009048
1531971    |     TDA202     |    4/12/2010   |    CQ009049
1538255    |     TDA202     |    4/20/2010   |    4994
1609608    |     TDA202     |    7/14/2010   |    5192
1678197    |     TDA202     |    10/29/2010   |    PSIV31297
1682714    |     TDA202     |    11/5/2010   |    PSIV31344
1686930    |     TDA202     |    11/11/2010   |    CQ010922
1686932    |     TDA202     |    11/11/2010   |    CQ010923
1919846    |     TDA202     |    9/15/2011   |    CQ013807
1919848    |     TDA202     |    9/15/2011   |    CQ013808
1938985    |     TDA202     |    10/12/2011   |    CQ014025
1938987    |     TDA202     |    10/12/2011   |    CQ014026
1938989    |     TDA202     |    10/12/2011   |    CQ014027
1969397    |     TDA202     |    11/17/2011   |    PSIV35565
1970739    |     TDA202     |    11/21/2011   |    PSV002202
1975024    |     TDA202     |    11/25/2011   |    CQ014419
2009812    |     TDA202     |    12/30/2011   |    JVA0518
--------------------------------------------------------------


[Detailed Cust_ Ledg_ Entry Table]

Cust_ Ledger Entry No_ | Posting Date | Document No_ |  Amount
1033045                |  6/20/2008   |  PSIV20128   | 2514406.21
1033045                |  5/28/2009   |  CQ004166    | -2514406.21
1034344                |  6/24/2008   |  CQ004101    | -225000
1034344                |  8/26/2008   |  PSIV20899   | 225000
1042812                |  7/7/2008    |  CQ004166    | -2514406.37
1042812                |  8/29/2008   |  1661        | 0.16
1042812                |  5/28/2009   |  CQ004166    | 2514406.21
1064599                |  8/26/2008   |  PSIV20899   | 225000
1064599                |  8/26/2008   |  PSIV20899   | -225000
1071200                |  8/29/2008   |  1661        | 0.16
1071200                |  8/29/2008   |  1661	     | -0.16
1126634                |  12/29/2008  |  CQ005148    | -100000.01
1126634                |  5/28/2009   |  3985        | 100000
1126634                |  5/29/2009   |  3991        | 0.01
1219318                |  5/28/2009   |  3985        | 100000
1219318                |  5/28/2009   |  3985	     | -100000
1219493                |  5/29/2009   |  3991	     | 0.01
1219493                |  5/29/2009   |  3991	     | -0.01
1222527                |  6/16/2009   |  CQ006373    | -5378859.19
1222527                |  9/24/2009   |  PSIV24693   | 5378859.19
1228642                |  6/24/2009   |  PSIV24693   | 5378859.19
1228642                |  9/24/2009   |  PSIV24693   | -5378859.19
1295440                |  9/29/2009   |  CH002248    | -200149.53
1295440                |  10/19/2009  |  PSIV26549   | 200149.53
1297953                |  9/25/2009   |  PSIV26308   | 160650
1297953                |  12/31/2009  |  PSIV26308   | -153000
1297953                |  12/31/2009  |  7406	     | -7650
1306787                |  10/19/2009  |  PSIV26547   | 24150
1306787                |  12/31/2009  |  PSIV26547   | -24150
1306932                |  10/19/2009  |  PSIV26549   | 200149.53
1306932                |  10/19/2009  |  PSIV26549   | -200149.53
1326155                |  11/19/2009  |  CQ007732    | -60214.63
1326155                |  12/31/2009  |  PSIV27189   | 60214.63
1326231                |  11/19/2009  |  CQ007734    | -153000
1326231                |  12/31/2009  |  PSIV26308   | 153000
1326248                |  11/19/2009  |  CQ007737    | -24150
1326248                |  12/31/2009  |  PSIV26547   | 24150
1335283                |  11/25/2009  |  PSIV27189   | 63225.36
1335283                |  12/31/2009  |  PSIV27189   | -60214.63
1335283                |  12/31/2009  |  7406	     | -3010.73
1348761                |  12/29/2009  |  PSIV27568   | 38129.85
1348761                |  4/12/2010   |  CQ009049    | -36314
1348761                |  7/14/2010   |  5192	     | -1815.85
1361075                |  12/31/2009  |  7406	     | -7650
1361075                |  12/31/2009  |  7406	     |   7650
1361077                |  12/31/2009  |  7406	     | -3010.73
1361077                |  12/31/2009  |  7406	     | 3010.73
1531969                |  4/12/2010   |  CQ009048    | -614557.76
1531969                |  4/20/2010   |  4994	     | 614557.76
1531971                |  4/12/2010   |  CQ009049    | -36314
1531971                |  4/12/2010   |  CQ009049    | 36314
1538255                |  4/20/2010   |  4994	     | 614557.76
1538255                |  4/20/2010   |  4994	     | -614557.76
1609608                |  7/14/2010   |  5192	     | -1815.85
1609608                |  7/14/2010   |  5192	     | 1815.85
1678197                |  10/29/2010  |  PSIV31297   | 77773.83
1678197                |  11/26/2010  |  CQ010922    | -77773.83
1682714                |  11/5/2010   |  PSIV31344   | 27327.97
1682714                |  9/15/2011   |  CQ013808    | -26026.64
1686930                |  11/11/2010  |  CQ010922    | -62023.83
1686930                |  11/26/2010  |  CQ010922    | 62023.83
1686932                |  11/11/2010  |  CQ010923    | -15750
1686932                |  11/26/2010  |  CQ010922    | 15750
1919846                |  9/15/2011   |  CQ013807    | -30845.68
1919848                |  9/15/2011   |  CQ013808    | -26026.64
1919848                |  9/15/2011   |  CQ013808    | 26026.64
1938985                |  10/12/2011  |  CQ014025    | -157453.94
1938987                |  10/12/2011  |  CQ014026    | -5000
1938989                |  10/12/2011  |  CQ014027    | -65032.97
1938989                |  11/17/2011  |  PSIV35565   | 65032.97
1969397                |  11/17/2011  |  PSIV35565   | 65032.97
1969397                |  11/17/2011  |  PSIV35565   | -65032.97
1970739                |  11/21/2011  |  PSV002202   | 60103.05
1975024                |  11/25/2011  |  CQ014419    | -57241
2009812                |  12/30/2011  |  JVA0518     | 26026.64

When I mine this data thus :

SELECT b.[Entry No_], a.[Customer No_], b.[Posting Date],SUM(a.Amount)[Balance]
FROM [Detailed Cust_ Ledg_ Entry]a
JOIN [Cust_ Ledger Entry]b
ON a.[Customer No_]=b.[Customer No_]
WHERE b.[Entry No_] = a.[Cust_ Ledger Entry No_] and a.[Posting Date] <='2012-04-18' AND b.[Customer No_]='TDA202'
GROUP BY b.[Entry No_],a.[Customer No_],b.[Posting Date]
Code: 
Entry No_ |  Customer No_  |  Posting Date  |  Balance
1033045   |  TDA202	   |  6/20/2008	    |  0
1034344   |  TDA202	   |  6/24/2008	    |  0
1042812   |  TDA202	   |  7/7/2008	    |  0
1064599   |  TDA202	   |  8/26/2008	    |  0
1071200   |  TDA202	   |  8/29/2008	    |  0
1126634   |  TDA202	   |  12/29/2008    |  0
1219318   |  TDA202	   |  5/28/2009	    |  0
1219493   |  TDA202	   |  5/29/2009	    |  0
1222527   |  TDA202	   |  6/16/2009	    |  0
1228642   |  TDA202	   |  6/24/2009	    |  0
1295440   |  TDA202	   |  9/29/2009	    |  0
1297953   |  TDA202	   |  9/25/2009	    |  0
1306787   |  TDA202	   |  10/19/2009    |  0
1306932   |  TDA202	   |  10/19/2009    |  0
1326155   |  TDA202	   |  11/19/2009    |  0
1326231   |  TDA202	   |  11/19/2009    |  0
1326248   |  TDA202	   |  11/19/2009    |  0
1335283   |  TDA202	   |  11/25/2009    |  0
1348761   |  TDA202	   |  12/29/2009    |  0
1361075   |  TDA202	   |  12/31/2009    |  0
1361077   |  TDA202	   |  12/31/2009    |  0
1531969   |  TDA202	   |  4/12/2010	    |  0
1531971   |  TDA202	   |  4/12/2010	    |  0
1538255   |  TDA202	   |  4/20/2010	    |  0
1609608   |  TDA202	   |  7/14/2010	    |  0
1678197   |  TDA202	   |  10/29/2010    |  0
1682714   |  TDA202	   |  11/5/2010	    |  1301.33
1686930   |  TDA202	   |  11/11/2010    |  0
1686932   |  TDA202	   |  11/11/2010    |  0
1919846   |  TDA202	   |  9/15/2011	    |  -30845.68
1919848   |  TDA202	   |  9/15/2011	    |  0
1938985   |  TDA202	   |  10/12/2011    |  -157453.94
1938987   |  TDA202	   |  10/12/2011    |  -5000
1938989   |  TDA202	   |  10/12/2011    |  0
1969397   |  TDA202	   |  11/17/2011    |  0
1970739   |  TDA202	   |  11/21/2011    |  60103.05
1975024   |  TDA202	   |  11/25/2011    |  -57241
2009812   |  TDA202	   |  12/30/2011    |  26026.64

I get this which is correct

The challenge is when I eventually join with the posted document dimension like so:

SELECT b.[Entry No_], b.[Posting Date], b.[Document No_], SUM(a.Amount)[Balance], COALESCE(d.[Dimension Value Code],'')
FROM [Cust_ Ledger Entry]b
LEFT OUTER JOIN [Posted Document Dimension]d
ON b.[Document No_] = d.[Document No_] AND d.[Dimension Code] = 'CUSTOMER GROUP'
JOIN [Detailed Cust_ Ledg_ Entry]a
ON a.[Customer No_]=b.[Customer No_]
WHERE b.[Customer No_]='TDA202' AND b.[Entry No_] = a.[Cust_ Ledger Entry No_] and a.[Posting Date] <= GETDATE()
GROUP BY b.[Entry No_],b.[Posting Date], b.[Document No_],d.[Dimension Value Code]


I get:
Code: 
Entry No_ |  Posting Date |  Document No_ |  Balance     |  Dimension Value Code
1033045   |  6/20/2008	  |  PSIV20128	  |  0	         |  
1034344   |  6/24/2008	  |  CQ004101	  |  0	         |  
1042812   |  7/7/2008	  |  CQ004166	  |  0	         |  
1064599   |  8/26/2008	  |  PSIV20899	  |  0	         |  
1071200   |  8/29/2008	  |  1661	  |  0	         |  
1126634   |  12/29/2008	  |  CQ005148	  |  0	         |  
1219318   |  5/28/2009	  |  3985	  |  0	         |  
1219493   |  5/29/2009	  |  3991	  |  0	         |  
1222527   |  6/16/2009	  |  CQ006373	  |  0	         |  
1228642   |  6/24/2009	  |  PSIV24693	  |  0	         |  
1295440   |  9/29/2009	  |  CH002248	  |  0	         |  
1297953   |  9/25/2009	  |  PSIV26308	  |  0	         |  
1306787   |  10/19/2009	  |  PSIV26547	  |  0	         |  
1306932   |  10/19/2009	  |  PSIV26549	  |  0	         |  
1326155   |  11/19/2009	  |  CQ007732	  |  0	         |  
1326231   |  11/19/2009	  |  CQ007734	  |  0	         |  
1326248   |  11/19/2009	  |  CQ007737	  |  0	         |  
1335283   |  11/25/2009	  |  PSIV27189	  |  0	         |  
1348761   |  12/29/2009	  |  PSIV27568	  |  0	         |  
1361075   |  12/31/2009	  |  7406	  |  0	         |  
1361077   |  12/31/2009	  |  7406	  |  0	         |  
1531969   |  4/12/2010	  |  CQ009048	  |  0	         |  
1531971   |  4/12/2010	  |  CQ009049	  |  0	         |  
1538255   |  4/20/2010	  |  4994	  |  0	         |  
1609608   |  7/14/2010	  |  5192	  |  0	         |   
1678197   |  10/29/2010	  |  PSIV31297	  |  0	         |  
1682714   |  11/5/2010	  |  PSIV31344	  |  1301.33     |  
1686930   |  11/11/2010	  |  CQ010922	  |  0	         |  
1686932   |  11/11/2010	  |  CQ010923	  |  0	         |  
1919846   |  9/15/2011	  |  CQ013807	  |  -30845.68   |  	
1919848   |  9/15/2011	  |  CQ013808	  |  0	         |  
1938985   |  10/12/2011	  |  CQ014025	  |  -157453.94  |  
1938987   |  10/12/2011	  |  CQ014026	  |  -5000	 |  
1938989   |  10/12/2011	  |  CQ014027	  |  0	         |  
1975024   |  11/25/2011	  |  CQ014419	  |  -57241	 |  
2009812   |  12/30/2011	  |  JVA0518	  |  26026.64	 |  
1969397   |  11/17/2011	  |  PSIV35565	  |  0           |  AM FIELD SERVICE
1970739   |  11/21/2011	  |  PSV002202	  |  240412.2    |  AM PART SALES
[/code]

Notice the 240412.2 in the amount when I join with the
[Posted Document Dimension] compared with the
 
Upvote 0 Downvote
Thanks guys your solutions, your solutions really worked; especially bborissovs' solution. It worked well.
However, when I tried to join with yet another table, [Detailed Cust_ Ledg_ Entry], I get funny figures.

Here is the full gist based on a single customer number "TDA202" (Sorry for writing a whole book on this):
Code: 
[Cust_ Ledger Entry table]
Entry No_  |  Customer No_  |  Posting Date  |  Document No_
----------------------------------------------------------------
1033045    |     TDA202     |    6/20/2008   |    PSIV20128
1034344    |     TDA202     |    6/24/2008   |    CQ004101
1042812    |     TDA202     |    7/7/2008    |    CQ004166
1064599    |     TDA202     |    8/26/2008   |    PSIV20899
1071200    |     TDA202     |    8/29/2008   |    1661
1126634    |     TDA202     |    12/29/2008  |    CQ005148
1219318    |     TDA202     |    5/28/2009   |    3985
1219493    |     TDA202     |    5/29/2009   |    3991
1222527    |     TDA202     |    6/16/2009   |    CQ006373
1228642    |     TDA202     |    6/24/2009   |    PSIV24693
1297953    |     TDA202     |    9/25/2009   |    PSIV26308
1295440    |     TDA202     |    9/29/2009   |    CH002248
1306787    |     TDA202     |    10/19/2009  |    PSIV26547
1306932    |     TDA202     |    10/19/2009  |    PSIV26549
1326155    |     TDA202     |    11/19/2009   |    CQ007732
1326231    |     TDA202     |    11/19/2009   |    CQ007734
1326248    |     TDA202     |    11/19/2009   |    CQ007737
1335283    |     TDA202     |    11/25/2009   |    PSIV27189
1348761    |     TDA202     |    12/29/2009   |    PSIV27568
1361075    |     TDA202     |    12/31/2009   |    7406
1361077    |     TDA202     |    12/31/2009   |    7406
1531969    |     TDA202     |    4/12/2010   |    CQ009048
1531971    |     TDA202     |    4/12/2010   |    CQ009049
1538255    |     TDA202     |    4/20/2010   |    4994
1609608    |     TDA202     |    7/14/2010   |    5192
1678197    |     TDA202     |    10/29/2010   |    PSIV31297
1682714    |     TDA202     |    11/5/2010   |    PSIV31344
1686930    |     TDA202     |    11/11/2010   |    CQ010922
1686932    |     TDA202     |    11/11/2010   |    CQ010923
1919846    |     TDA202     |    9/15/2011   |    CQ013807
1919848    |     TDA202     |    9/15/2011   |    CQ013808
1938985    |     TDA202     |    10/12/2011   |    CQ014025
1938987    |     TDA202     |    10/12/2011   |    CQ014026
1938989    |     TDA202     |    10/12/2011   |    CQ014027
1969397    |     TDA202     |    11/17/2011   |    PSIV35565
1970739    |     TDA202     |    11/21/2011   |    PSV002202
1975024    |     TDA202     |    11/25/2011   |    CQ014419
2009812    |     TDA202     |    12/30/2011   |    JVA0518
--------------------------------------------------------------


[Detailed Cust_ Ledg_ Entry Table]

Cust_ Ledger Entry No_ | Posting Date | Document No_ |  Amount
1033045                |  6/20/2008   |  PSIV20128   | 2514406.21
1033045                |  5/28/2009   |  CQ004166    | -2514406.21
1034344                |  6/24/2008   |  CQ004101    | -225000
1034344                |  8/26/2008   |  PSIV20899   | 225000
1042812                |  7/7/2008    |  CQ004166    | -2514406.37
1042812                |  8/29/2008   |  1661        | 0.16
1042812                |  5/28/2009   |  CQ004166    | 2514406.21
1064599                |  8/26/2008   |  PSIV20899   | 225000
1064599                |  8/26/2008   |  PSIV20899   | -225000
1071200                |  8/29/2008   |  1661        | 0.16
1071200                |  8/29/2008   |  1661	     | -0.16
1126634                |  12/29/2008  |  CQ005148    | -100000.01
1126634                |  5/28/2009   |  3985        | 100000
1126634                |  5/29/2009   |  3991        | 0.01
1219318                |  5/28/2009   |  3985        | 100000
1219318                |  5/28/2009   |  3985	     | -100000
1219493                |  5/29/2009   |  3991	     | 0.01
1219493                |  5/29/2009   |  3991	     | -0.01
1222527                |  6/16/2009   |  CQ006373    | -5378859.19
1222527                |  9/24/2009   |  PSIV24693   | 5378859.19
1228642                |  6/24/2009   |  PSIV24693   | 5378859.19
1228642                |  9/24/2009   |  PSIV24693   | -5378859.19
1295440                |  9/29/2009   |  CH002248    | -200149.53
1295440                |  10/19/2009  |  PSIV26549   | 200149.53
1297953                |  9/25/2009   |  PSIV26308   | 160650
1297953                |  12/31/2009  |  PSIV26308   | -153000
1297953                |  12/31/2009  |  7406	     | -7650
1306787                |  10/19/2009  |  PSIV26547   | 24150
1306787                |  12/31/2009  |  PSIV26547   | -24150
1306932                |  10/19/2009  |  PSIV26549   | 200149.53
1306932                |  10/19/2009  |  PSIV26549   | -200149.53
1326155                |  11/19/2009  |  CQ007732    | -60214.63
1326155                |  12/31/2009  |  PSIV27189   | 60214.63
1326231                |  11/19/2009  |  CQ007734    | -153000
1326231                |  12/31/2009  |  PSIV26308   | 153000
1326248                |  11/19/2009  |  CQ007737    | -24150
1326248                |  12/31/2009  |  PSIV26547   | 24150
1335283                |  11/25/2009  |  PSIV27189   | 63225.36
1335283                |  12/31/2009  |  PSIV27189   | -60214.63
1335283                |  12/31/2009  |  7406	     | -3010.73
1348761                |  12/29/2009  |  PSIV27568   | 38129.85
1348761                |  4/12/2010   |  CQ009049    | -36314
1348761                |  7/14/2010   |  5192	     | -1815.85
1361075                |  12/31/2009  |  7406	     | -7650
1361075                |  12/31/2009  |  7406	     |   7650
1361077                |  12/31/2009  |  7406	     | -3010.73
1361077                |  12/31/2009  |  7406	     | 3010.73
1531969                |  4/12/2010   |  CQ009048    | -614557.76
1531969                |  4/20/2010   |  4994	     | 614557.76
1531971                |  4/12/2010   |  CQ009049    | -36314
1531971                |  4/12/2010   |  CQ009049    | 36314
1538255                |  4/20/2010   |  4994	     | 614557.76
1538255                |  4/20/2010   |  4994	     | -614557.76
1609608                |  7/14/2010   |  5192	     | -1815.85
1609608                |  7/14/2010   |  5192	     | 1815.85
1678197                |  10/29/2010  |  PSIV31297   | 77773.83
1678197                |  11/26/2010  |  CQ010922    | -77773.83
1682714                |  11/5/2010   |  PSIV31344   | 27327.97
1682714                |  9/15/2011   |  CQ013808    | -26026.64
1686930                |  11/11/2010  |  CQ010922    | -62023.83
1686930                |  11/26/2010  |  CQ010922    | 62023.83
1686932                |  11/11/2010  |  CQ010923    | -15750
1686932                |  11/26/2010  |  CQ010922    | 15750
1919846                |  9/15/2011   |  CQ013807    | -30845.68
1919848                |  9/15/2011   |  CQ013808    | -26026.64
1919848                |  9/15/2011   |  CQ013808    | 26026.64
1938985                |  10/12/2011  |  CQ014025    | -157453.94
1938987                |  10/12/2011  |  CQ014026    | -5000
1938989                |  10/12/2011  |  CQ014027    | -65032.97
1938989                |  11/17/2011  |  PSIV35565   | 65032.97
1969397                |  11/17/2011  |  PSIV35565   | 65032.97
1969397                |  11/17/2011  |  PSIV35565   | -65032.97
1970739                |  11/21/2011  |  PSV002202   | 60103.05
1975024                |  11/25/2011  |  CQ014419    | -57241
2009812                |  12/30/2011  |  JVA0518     | 26026.64

When I mine this data thus :

SELECT b.[Entry No_], a.[Customer No_], b.[Posting Date],SUM(a.Amount)[Balance]
FROM [Detailed Cust_ Ledg_ Entry]a
JOIN [Cust_ Ledger Entry]b
ON a.[Customer No_]=b.[Customer No_]
WHERE b.[Entry No_] = a.[Cust_ Ledger Entry No_] and a.[Posting Date] <='2012-04-18' AND b.[Customer No_]='TDA202'
GROUP BY b.[Entry No_],a.[Customer No_],b.[Posting Date]
Code: 
Entry No_ |  Customer No_  |  Posting Date  |  Balance
1033045   |  TDA202	   |  6/20/2008	    |  0
1034344   |  TDA202	   |  6/24/2008	    |  0
1042812   |  TDA202	   |  7/7/2008	    |  0
1064599   |  TDA202	   |  8/26/2008	    |  0
1071200   |  TDA202	   |  8/29/2008	    |  0
1126634   |  TDA202	   |  12/29/2008    |  0
1219318   |  TDA202	   |  5/28/2009	    |  0
1219493   |  TDA202	   |  5/29/2009	    |  0
1222527   |  TDA202	   |  6/16/2009	    |  0
1228642   |  TDA202	   |  6/24/2009	    |  0
1295440   |  TDA202	   |  9/29/2009	    |  0
1297953   |  TDA202	   |  9/25/2009	    |  0
1306787   |  TDA202	   |  10/19/2009    |  0
1306932   |  TDA202	   |  10/19/2009    |  0
1326155   |  TDA202	   |  11/19/2009    |  0
1326231   |  TDA202	   |  11/19/2009    |  0
1326248   |  TDA202	   |  11/19/2009    |  0
1335283   |  TDA202	   |  11/25/2009    |  0
1348761   |  TDA202	   |  12/29/2009    |  0
1361075   |  TDA202	   |  12/31/2009    |  0
1361077   |  TDA202	   |  12/31/2009    |  0
1531969   |  TDA202	   |  4/12/2010	    |  0
1531971   |  TDA202	   |  4/12/2010	    |  0
1538255   |  TDA202	   |  4/20/2010	    |  0
1609608   |  TDA202	   |  7/14/2010	    |  0
1678197   |  TDA202	   |  10/29/2010    |  0
1682714   |  TDA202	   |  11/5/2010	    |  1301.33
1686930   |  TDA202	   |  11/11/2010    |  0
1686932   |  TDA202	   |  11/11/2010    |  0
1919846   |  TDA202	   |  9/15/2011	    |  -30845.68
1919848   |  TDA202	   |  9/15/2011	    |  0
1938985   |  TDA202	   |  10/12/2011    |  -157453.94
1938987   |  TDA202	   |  10/12/2011    |  -5000
1938989   |  TDA202	   |  10/12/2011    |  0
1969397   |  TDA202	   |  11/17/2011    |  0
1970739   |  TDA202	   |  11/21/2011    |  60103.05
1975024   |  TDA202	   |  11/25/2011    |  -57241
2009812   |  TDA202	   |  12/30/2011    |  26026.64

I get this which is correct

The challenge is when I eventually join with the posted document dimension like so:

SELECT b.[Entry No_], b.[Posting Date], b.[Document No_], SUM(a.Amount)[Balance], COALESCE(d.[Dimension Value Code],'')
FROM [Cust_ Ledger Entry]b
LEFT OUTER JOIN [Posted Document Dimension]d
ON b.[Document No_] = d.[Document No_] AND d.[Dimension Code] = 'CUSTOMER GROUP'
JOIN [Detailed Cust_ Ledg_ Entry]a
ON a.[Customer No_]=b.[Customer No_]
WHERE b.[Customer No_]='TDA202' AND b.[Entry No_] = a.[Cust_ Ledger Entry No_] and a.[Posting Date] <= GETDATE()
GROUP BY b.[Entry No_],b.[Posting Date], b.[Document No_],d.[Dimension Value Code]


I get:
Code: 
Entry No_ |  Posting Date |  Document No_ |  Balance     |  Dimension Value Code
1033045   |  6/20/2008	  |  PSIV20128	  |  0	         |  
1034344   |  6/24/2008	  |  CQ004101	  |  0	         |  
1042812   |  7/7/2008	  |  CQ004166	  |  0	         |  
1064599   |  8/26/2008	  |  PSIV20899	  |  0	         |  
1071200   |  8/29/2008	  |  1661	  |  0	         |  
1126634   |  12/29/2008	  |  CQ005148	  |  0	         |  
1219318   |  5/28/2009	  |  3985	  |  0	         |  
1219493   |  5/29/2009	  |  3991	  |  0	         |  
1222527   |  6/16/2009	  |  CQ006373	  |  0	         |  
1228642   |  6/24/2009	  |  PSIV24693	  |  0	         |  
1295440   |  9/29/2009	  |  CH002248	  |  0	         |  
1297953   |  9/25/2009	  |  PSIV26308	  |  0	         |  
1306787   |  10/19/2009	  |  PSIV26547	  |  0	         |  
1306932   |  10/19/2009	  |  PSIV26549	  |  0	         |  
1326155   |  11/19/2009	  |  CQ007732	  |  0	         |  
1326231   |  11/19/2009	  |  CQ007734	  |  0	         |  
1326248   |  11/19/2009	  |  CQ007737	  |  0	         |  
1335283   |  11/25/2009	  |  PSIV27189	  |  0	         |  
1348761   |  12/29/2009	  |  PSIV27568	  |  0	         |  
1361075   |  12/31/2009	  |  7406	  |  0	         |  
1361077   |  12/31/2009	  |  7406	  |  0	         |  
1531969   |  4/12/2010	  |  CQ009048	  |  0	         |  
1531971   |  4/12/2010	  |  CQ009049	  |  0	         |  
1538255   |  4/20/2010	  |  4994	  |  0	         |  
1609608   |  7/14/2010	  |  5192	  |  0	         |   
1678197   |  10/29/2010	  |  PSIV31297	  |  0	         |  
1682714   |  11/5/2010	  |  PSIV31344	  |  1301.33     |  
1686930   |  11/11/2010	  |  CQ010922	  |  0	         |  
1686932   |  11/11/2010	  |  CQ010923	  |  0	         |  
1919846   |  9/15/2011	  |  CQ013807	  |  -30845.68   |  	
1919848   |  9/15/2011	  |  CQ013808	  |  0	         |  
1938985   |  10/12/2011	  |  CQ014025	  |  -157453.94  |  
1938987   |  10/12/2011	  |  CQ014026	  |  -5000	 |  
1938989   |  10/12/2011	  |  CQ014027	  |  0	         |  
1975024   |  11/25/2011	  |  CQ014419	  |  -57241	 |  
2009812   |  12/30/2011	  |  JVA0518	  |  26026.64	 |  
1969397   |  11/17/2011	  |  PSIV35565	  |  0           |  AM FIELD SERVICE
1970739   |  11/21/2011	  |  PSV002202	  |  240412.2    |  AM PART SALES
[/code]

Notice the 240412.2 in the amount on entry number 1970739, when I join with the
[Posted Document Dimension] compared with the 60103.05 on thesame entry number.
The report would have been accurate if not for that row.

I have used the right join and left join and dont seem to be getting the right answer.

Note here is the modified [Posted Document Dimension] table:
Code: 
Document No_ | Dimension Code |   Dimension Value Code
PSIV35565    | CUSTOMER GROUP |   AM FIELD SERVICE
PSIV35565    | DEPARTMENT     |   GLLAGOS
PSIV35565    | DEPARTMENT     |   GLLAGOS
PSV002202    | CUSTOMER GROUP |   AM PART SALES
PSV002202    | CUSTOMER GROUP |   AM PART SALES
PSV002202    | CUSTOMER GROUP |   AM PART SALES    
PSV002202    | CUSTOMER GROUP |   AM PART SALES    
PSV002202    | CUSTOMER GROUP |   AM PART SALES    
PSV002202    |  DEPARTMENT    |      GLLAGOS
PSV002202    |  DEPARTMENT    |      GLLAGOS
PSV002202    |    PROJECT     |     N51
PSIV30122    | CUSTOMER GROUP |   AM FIELD SERVICE
PSIV30122    |  DEPARTMENT    |   GLLAGOS
PSIV30122    | CUSTOMER GROUP |   AM FIELD SERVICE
PSIV30122    | CUSTOMER GROUP |   AM PART SALES

etc

Sorry for the disturbance, but I'd appreciate the help (of course with a vote [wink])

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
 
Upvote 0 Downvote
Thank you all guys for the assistance, I eventually created a view that joined the [Cust_ Ledger Entry table] and [Posted Document Dimension]. I called the view [v_Cust_ Ledger Entry], then created a procedure that joined the [Detailed Cust_ Ledg_ Entry Table]. It was able to generate what I was looking for.

Thanks again for the pointer.

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
235
Chris Miller
Chris Miller
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
171
Chris Miller
Chris Miller
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
265
bharons
bharons
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
202
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
365
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top