Adding to outputs together to retrieve the top(10) - Is it possible?

Hi There,

I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.

I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.

In simple terms I'm trying to do the following:-

For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.

I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....

I'm currently using to seperate queries as follows :--

--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types

SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname

-- I then use the following to get the total of all action scores, again in the 'Volume' column

SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname

So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?

Is it possible to output these 2 result to a new table and then join the new tables?

Thanks for any assistance, this is really draining me at the moment..... :-/

here it is,

Select TOP (@.Number)

incident_Data.personnel_code

, incident_Data.personnel_forename

, incident_Data.personnel_Surname

, incident_Data.Volume

, incident_Data.Average

, actions_data.Volume

, actions_data.Average

, incident_Data.Volume + actions_data.Volume as Total_Volume

, incident_Data.Average + actions_data.Average as Total_Average

from

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(incident_types.type_score) as 'Volume'

, avg(incident_types.type_score) as 'Average'

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as incident_Data

Inner Join

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(actions.action_score) as 'Volume'

, AVG(actions.action_score) as 'Average'

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as actions_data

On actions_data.personnel_code = incident_Data.personnel_code

|||

Thank you so much for your reply this has really made my day!

I forgot to mention that the action record is not always present but the incident record is. Therefore I made the actions_data join = 'Left Outer Join' (see code below)... This gives me a problem in that when the action_data record contains 'NULL' the Volume is also 'NULL' - is it possible to make NULL = 0 [zero] when there is no action record?

Thanks again (see code and output).....

[code I've removed some of the selection criteria from previous post for testing purposes]

Select TOP (1000)

incident_Data.personnel_code

, incident_Data.personnel_forename

, incident_Data.personnel_Surname

, incident_Data.Volume

, incident_Data.Average

, actions_data.Volume

, actions_data.Average

, incident_Data.Volume + actions_data.Volume as Total_Volume

, incident_Data.Average + actions_data.Average as Total_Average

from

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(incident_types.type_score) as 'Volume'

, avg(incident_types.type_score) as 'Average'

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

report_basedon.personnel_code <> 0

AND report_header.record_status='1'

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as incident_Data

Left Outer Join

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(actions.action_score) as 'Volume'

, AVG(actions.action_score) as 'Average'

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

personnel_details.personnel_code <> 0

AND report_header.record_status='1'

AND profile.record_status='1'

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as actions_data

On actions_data.personnel_code = incident_Data.personnel_code

Order by incident_data.personnel_code

[example of results]

15 Oscar Freeman 0 0 NULL NULL NULL NULL
16 Sofia Daniels 0 0 NULL NULL NULL NULL
19 Megan Hardy 25 12 120 40 145 52
20 Finley Randall -25 -25 NULL NULL NULL NULL|||

observations

1. since actions_data subquery may be missing [i.e. OUTER JOIN] you should use this line instead

, incident_Data.Volume + ISNULL(actions_data.Volume,0) as Total_Volume

, incident_Data.Average + ISNULL(actions_data.Average,0) as Total_Average

2. the Total_Average calculation is flawed, e.g.
4 rows of incident_Data with average of 50 and 1 row of actions_data with average of 20 would yield a Total_Average of 70

when what you probably wanted was 44 (or 35 for simplicity if you don't care about weighting)

Dick

|||

Thank you Dick, for both points, your absolutly correct. I was so concerned with not being ale to extract the data that I hadn't thought this though. You've propably saved me a lot more pain!

Just for completeness, I had to go on and resolve a divide by zero error when calculating the final averages etc etc and I found a very useful link http://www.sql-server-helper.com/error-messages/msg-8134.aspx which shows 3 ways of dealing with this error.

Thanks again for everyones help - I can now get on with developing the program!!!!

|||

Yes. ISNULL will fix the issue... If the average is your issue the following query might be a best one..

Not sure why we need a TOP here.. It wont give any performance gain.

Select TOP (@.Number)

personnel_code

, personnel_forename

, personnel_Surname

, sum(case when type='incident' then Score end) as incident_volume

, avg(case when type='incident' then Score end) as incident_average

, sum(case when type='actions' then Score end) as actions_volume

, avg(case when type='actions' then Score end) as actions_average

, sum(Score) as Total_Volume

, avg(Score) as Total_Average

From

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, (incident_types.type_score) as 'Score'

, 'incident' Type

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

UNION ALL

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, (actions.action_score) as 'Score'

, 'actions' Type

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

) as data

Group By

personnel_code

, personnel_forename

, personnel_Surname

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob

Adding Fuzzy Lookup Programmatically

I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:

IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();

FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";

FuzzyLookupDF.Name = "FuzzyLookup";

I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.

Thanks!

amber

Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx

There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||

I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:

instance.SetComponentProperty("JoinType", 2);

I get the following error:

System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282

I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".

Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.

|||

For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||

You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.

"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.

HTH,

Bob