Count from three columns - 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

Analysis Server and Reporting Services (2005)

Count from three columns

Author

Topic

OverIT
Starting Member

3 Posts

Posted - 2009-08-06 : 00:06:27

Hi All,

I am trying to run a report in SSRS 2005, with the following code:

SELECT iqm.inc_incident_system.inc_code, iqm.inc_incident_system.inc_control_1, iqm.inc_control_hierarchy.inc_title, iqm.inc_incident_system.inc_title
FROM iqm.inc_incident_system
INNER JOIN iqm.inc_control_hierarchy ON iqm.inc_incident_system.inc_control_1=iqm.inc_control_hierarchy.inc_code
WHERE (iqm.inc_incident_system.inc_injury = '1')

union

SELECT iqm.inc_incident_system.inc_code, iqm.inc_incident_system.inc_control_2, iqm.inc_control_hierarchy.inc_title, iqm.inc_incident_system.inc_title
FROM iqm.inc_incident_system
INNER JOIN iqm.inc_control_hierarchy ON iqm.inc_incident_system.inc_control_2=iqm.inc_control_hierarchy.inc_code
WHERE (iqm.inc_incident_system.inc_injury = '1')

order by iqm.inc_incident_system.inc_code

-----
I also have two more columns to join once I have this working correctly (iqm.inc_incident_system.inc_control_3, iqm.inc_incident_system.inc_control_4)

My expected results is:

inc_code inc_control_1 inc_title inc_title

2 5 5-PPE Splash
5 4 4-Admin tripped
44 4 4-Admin worker
44 4 4-Admin worker
57 3 3-Engineer fall
57 4 4-Admin fall
--------

However, my actual outcome is this:

inc_code inc_control_1 inc_title inc_title

2 5 5-PPE Splash
5 4 4-Admin tripped
44 4 4-Admin worker
57 3 3-Engineer fall
57 4 4-Admin fall
-----------------

Since iqm.inc_control_hierarchy.inc_title has two '4-Admin' (see inc_code 44) and displays as one only. Is there a way to separate and list both and therefore count 2, not just 1. It works fine when the iqm.inc_control_hierarchy.inc_title is different (see inc_code 57)

I attempted to give them an alias, but since our SSRS needs the full path including the iqm, I could not get it to work without an invalid object error.

Hope you can help!

thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 06:46:36

Use UNION ALL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources