Complex Inner Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex Inner Join

Author

Topic

lueylump
Starting Member

25 Posts

Posted - 2010-10-18 : 16:23:08

I am working with a less than optimally designed db and have to use inner joins to navigate to the data I need to extract (which is an amount field). The challenge is I need to tabulate that amount field six different ways based upon the process date (which is stored in a different table), and the Fund_ID which is stored on the same table as the amount field. I put together the following SQL but am not sure it will even work (a scaled down version of this SQL has been executing for about 40 minutes without returning an answer set).

I have two questions (see SQL below):

1) Will this SQL procedure work? and
2) Is there a better way to code this?

select distinct A.Group_Formal_Name, A.Group_Code, E.LUV_Desc, G.Org_Postal_Code, sum(F.Allocation_Amt) as Cum_Total,
sum(F2.Allocation_Amt) as Three_Year_Total, sum(F3.Allocation_Amt) as Two_Year_Total,
sum(F4.Allocation_Amt) as One_Year_Total, sum(F5.Allocation_Amt) as YTD_Total,
sum(F6.Allocation_Amt) as Capital_Campaign

FROM LookUpValues E, Org_Addresses G,
Per_Group_Loc A INNER JOIN Payment_Device B
ON B.Payor_Code = A.Group_Code JOIN Payment_Device_History C
ON C.Pay_Device_ID = B.Pay_Device_ID JOIN Payment D
ON D.Payment_ID = C.Payment_ID JOIN Payment_Allocation F
ON F.Payment_ID = D.Payment_ID JOIN Payment_Batch H
ON H.Batch_ID = D.Batch_ID,

Per_Group_Loc A2 INNER JOIN Payment_Device B2
ON B2.Payor_Code = A2.Group_Code JOIN Payment_Device_History C2
ON C2.Pay_Device_ID = B2.Pay_Device_ID JOIN Payment D2
ON D2.Payment_ID = C2.Payment_ID JOIN Payment_Allocation F2
ON F2.Payment_ID = D2.Payment_ID JOIN Payment_Batch H2
ON H2.Batch_ID = D2.Batch_ID WHERE F2.Allocation_Amt is NOT NULL

Per_Group_Loc A3 INNER JOIN Payment_Device B3
ON B3.Payor_Code = A3.Group_Code JOIN Payment_Device_History C3
ON C3.Pay_Device_ID = B3.Pay_Device_ID JOIN Payment D3
ON D3.Payment_ID = C3.Payment_ID JOIN Payment_Allocation F3
ON F3.Payment_ID = D3.Payment_ID JOIN Payment_Batch H3
ON H3.Batch_ID = D3.Batch_ID WHERE F3.Allocation_Amt is NOT NULL

Per_Group_Loc A4 INNER JOIN Payment_Device B4
ON B4.Payor_Code = A4.Group_Code JOIN Payment_Device_History C4
ON C4.Pay_Device_ID = B4.Pay_Device_ID JOIN Payment D4
ON D4.Payment_ID = C4.Payment_ID JOIN Payment_Allocation F4
ON F4.Payment_ID = D4.Payment_ID JOIN Payment_Batch H4
ON H4.Batch_ID = D4.Batch_ID WHERE F4.Allocation_Amt is NOT NULL

Per_Group_Loc A5 INNER JOIN Payment_Device B5
ON B5.Payor_Code = A5.Group_Code JOIN Payment_Device_History C5
ON C5.Pay_Device_ID = B5.Pay_Device_ID JOIN Payment D5
ON D5.Payment_ID = C5.Payment_ID JOIN Payment_Allocation F5
ON F5.Payment_ID = D5.Payment_ID JOIN Payment_Batch H5
ON H5.Batch_ID = D5.Batch_ID WHERE F5.Allocation_Amt is NOT NULL

Per_Group_Loc A6 INNER JOIN Payment_Device B6
ON B6.Payor_Code = A6.Group_Code JOIN Payment_Device_History C6
ON C6.Pay_Device_ID = B6.Pay_Device_ID JOIN Payment D6
ON D6.Payment_ID = C6.Payment_ID JOIN Payment_Allocation F6
ON F6.Payment_ID = D6.Payment_ID JOIN Payment_Batch H6
ON H2.Batch_ID = D6.Batch_ID WHERE F6.Allocation_Amt is NOT NULL

and (F.Allocation_Amt > 0
and F.Fund_ID <> '92F34AC0-C5CE-454D-A553-3BB6C6AA8E65'
and F.Fund_ID <> 'A2222598-D018-4F58-A68B-604E4D292A57'
and F.Fund_ID <> '9D91E0EF-6773-4E47-9573-6901136B2FFF'
and F.Fund_ID <> '84F2C1BF-2ACE-4959-8FB1-76BE75518FE7'
and F.Fund_ID <> '8DD4BAFF-FF3A-420C-933A-7810F2EDC296'
and F.Fund_ID <> 'D1393F1C-4A44-4E3D-9820-7A57EF2C0771'
and F.Fund_ID <> 'F51C37E6-927E-4F1F-B900-87EBC4417209'
and F.Fund_ID <> 'CBF486D6-D184-4890-8F2F-DB8AEDF8DA81')

and ((F2.Allocation_Amt > 0
and F2.Fund_ID <> '92F34AC0-C5CE-454D-A553-3BB6C6AA8E65'
and F2.Fund_ID <> 'A2222598-D018-4F58-A68B-604E4D292A57'
and F2.Fund_ID <> '9D91E0EF-6773-4E47-9573-6901136B2FFF'
and F2.Fund_ID <> '84F2C1BF-2ACE-4959-8FB1-76BE75518FE7'
and F2.Fund_ID <> '8DD4BAFF-FF3A-420C-933A-7810F2EDC296'
and F2.Fund_ID <> 'D1393F1C-4A44-4E3D-9820-7A57EF2C0771'
and F2.Fund_ID <> 'F51C37E6-927E-4F1F-B900-87EBC4417209'
and F2.Fund_ID <> 'CBF486D6-D184-4890-8F2F-DB8AEDF8DA81')
and (H2.Process_Date > '20061231 23:59:59' and H2.Process_Date < '20080101 00:00:00'))

and ((F3.Allocation_Amt > 0
and F3.Fund_ID <> '92F34AC0-C5CE-454D-A553-3BB6C6AA8E65'
and F3.Fund_ID <> 'A2222598-D018-4F58-A68B-604E4D292A57'
and F3.Fund_ID <> '9D91E0EF-6773-4E47-9573-6901136B2FFF'
and F3.Fund_ID <> '84F2C1BF-2ACE-4959-8FB1-76BE75518FE7'
and F3.Fund_ID <> '8DD4BAFF-FF3A-420C-933A-7810F2EDC296'
and F3.Fund_ID <> 'D1393F1C-4A44-4E3D-9820-7A57EF2C0771'
and F3.Fund_ID <> 'F51C37E6-927E-4F1F-B900-87EBC4417209'
and F3.Fund_ID <> 'CBF486D6-D184-4890-8F2F-DB8AEDF8DA81')
and (H3.Process_Date > '20071231 23:59:59' and H3.Process_Date < '20090101 00:00:00'))

and ((F4.Allocation_Amt > 0
and F4.Fund_ID <> '92F34AC0-C5CE-454D-A553-3BB6C6AA8E65'
and F4.Fund_ID <> 'A2222598-D018-4F58-A68B-604E4D292A57'
and F4.Fund_ID <> '9D91E0EF-6773-4E47-9573-6901136B2FFF'
and F4.Fund_ID <> '84F2C1BF-2ACE-4959-8FB1-76BE75518FE7'
and F4.Fund_ID <> '8DD4BAFF-FF3A-420C-933A-7810F2EDC296'
and F4.Fund_ID <> 'D1393F1C-4A44-4E3D-9820-7A57EF2C0771'
and F4.Fund_ID <> 'F51C37E6-927E-4F1F-B900-87EBC4417209'
and F4.Fund_ID <> 'CBF486D6-D184-4890-8F2F-DB8AEDF8DA81')
and (H4.Process_Date > '20081231 23:59:59' and H4.Process_Date < '20100101 00:00:00'))

and ((F5.Allocation_Amt > 0
and F5.Fund_ID <> '92F34AC0-C5CE-454D-A553-3BB6C6AA8E65'
and F5.Fund_ID <> 'A2222598-D018-4F58-A68B-604E4D292A57'
and F5.Fund_ID <> '9D91E0EF-6773-4E47-9573-6901136B2FFF'
and F5.Fund_ID <> '84F2C1BF-2ACE-4959-8FB1-76BE75518FE7'
and F5.Fund_ID <> '8DD4BAFF-FF3A-420C-933A-7810F2EDC296'
and F5.Fund_ID <> 'D1393F1C-4A44-4E3D-9820-7A57EF2C0771'
and F5.Fund_ID <> 'F51C37E6-927E-4F1F-B900-87EBC4417209'
and F5.Fund_ID <> 'CBF486D6-D184-4890-8F2F-DB8AEDF8DA81'
and H5.Process_Date > '20091231 23:59:59'))

and (F6.Allocation_Amt > 0
and F6.Fund_ID = '75FED8FF-E146-4B0C-B7EC-516D38AADE74')

and E.LUV_ID = A.Group_Type_Code
and G.Org_Addr_ID = A.Primary_Mail_Addr_ID

Group by A.Group_Formal_Name, A.Group_Code, E.LUV_Desc, G.Org_Postal_Code
Order by A.Group_Formal_Name

Thanks for any and all feedback!

Phil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-18 : 17:18:06

1) Unknown, query is too complex to dissect without having knowledge of your data, table design, business requirements, etc...
2) Unknown, we need more info like sample data and table DDL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-18 : 17:19:03

This should give you the results you need:

SELECT A.Group_Formal_Name, A.Group_Code, E.LUV_Desc, G.Org_Postal_Code, 
SUM(F.Allocation_Amt) AS Cum_Total,
SUM(CASE WHEN H.ProcessDate BETWEEN '20070101' AND '20071231 23:59:59' THEN F.Allocation_Amt END) AS Three_Year_Total, 
SUM(CASE WHEN H.ProcessDate BETWEEN '20080101' AND '20081231 23:59:59' THEN F.Allocation_Amt END) AS Two_Year_Total, 
SUM(CASE WHEN H.ProcessDate BETWEEN '20090101' AND '20091231 23:59:59' THEN F.Allocation_Amt END) AS One_Year_Total, 
SUM(CASE WHEN H.ProcessDate>='20100101' THEN F.Allocation_Amt END) AS YTD_Total, 
SUM(CASE WHEN F.Fund_ID = '75FED8FF-E146-4B0C-B7EC-516D38AADE74' THEN F.Allocation_Amt END) AS Capital_Campaign
FROM Per_Group_Loc A 
INNER JOIN Payment_Device B ON B.Payor_Code = A.Group_Code 
INNER JOIN Payment_Device_History C ON C.Pay_Device_ID = B.Pay_Device_ID 
INNER JOIN Payment D ON D.Payment_ID = C.Payment_ID 
INNER JOIN Payment_Allocation F ON F.Payment_ID = D.Payment_ID 
INNER JOIN Payment_Batch H ON H.Batch_ID = D.Batch_ID
INNER JOIN LookUpValues E ON E.LUV_ID = A.Group_Type_Code
INNER JOIN Org_Addresses G ON G.Org_Addr_ID = A.Primary_Mail_Addr_ID
WHERE F.Allocation_Amt > 0 AND
F.Fund_ID NOT IN('92F34AC0-C5CE-454D-A553-3BB6C6AA8E65','A2222598-D018-4F58-A68B-604E4D292A57',
'9D91E0EF-6773-4E47-9573-6901136B2FFF','84F2C1BF-2ACE-4959-8FB1-76BE75518FE7','8DD4BAFF-FF3A-420C-933A-7810F2EDC296',
'D1393F1C-4A44-4E3D-9820-7A57EF2C0771','F51C37E6-927E-4F1F-B900-87EBC4417209','CBF486D6-D184-4890-8F2F-DB8AEDF8DA81')
GROUP BY A.Group_Formal_Name, A.Group_Code, E.LUV_Desc, G.Org_Postal_Code
ORDER BY A.Group_Formal_Name

You have several cross joins in there, that's probably why it didn't return. There was also a bug in one of the JOINs:

Per_Group_Loc A6
...
JOIN Payment_Batch H6 ON H2.Batch_ID = D6.Batch_ID

edit: fixed a stray comma

lueylump
Starting Member

25 Posts

Posted - 2010-10-19 : 12:53:35

robvolk YOU ROCK!!!

I was pulling my hair out with this one and your solution was so elegant and it worked like a charm!!!!!

Thank you! Thank you! Thank you!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources