Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret

Excel to SQL server 2005

I need some pointers to help me import some data from an excel sheet into a SQL Server 2005 DB. Here is the scenario:

Each row in my excel sheet maps to three tables in the DB. Lets say there are 6 columns: Col1, Col2...Col6.

Col1 and Col2 map to DBTableA.

Col3 and Col4 map to DBTableB.

Col4 and Col6 map to DBTableC.

DBTableA's primary key is used as a foreign key in DBTableB and DBTableC.

Now what would be the best way to do this using SSIS?

I have gone through transforms and data flow tasks etc, so I am not exactly a newbie in SSIS. So it will be ok to use any SSIS specific terms in your response.

Thanks.

-Faisal

I guess the post was redundant.

After searching the forums for similar questions, I found the solution.

SSIS Nugget: Splitting order detail and order header information from one file into multiple tables

http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx

Thanks Jamie.

-Faisal