Dupplicate Records - 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 2000 Forums

Transact-SQL (2000)

Dupplicate Records

Author

Topic

markfairfield
Starting Member

2 Posts

Posted - 2007-08-09 : 11:39:11

Hi,

I have been rattling my brains on this for some time now, and close to admitting defeat.

I am trying to compare the Sales against the Purchases (3rd Party Report).

The join is the ‘Order Number’

The problem arises when the sales order has multiple lines and the purchase order is only a single line (or vise-verser) it will duplicate the row on the side with the least lines. In the ideal world I would like this value to be null.

This is the result I am getting now:

Example

(Apologies for the dots)

Sales..............................Purchase

Order No.¦Product..¦Value¦Description.¦Value
1234......¦Pen........¦23....¦Stationary..¦40
1234......¦Pencil.....¦25....¦Stationary..¦40
1134......¦Ruler......¦10....¦Stationary..¦40

I would like the data to be displayed as below

Example

Sales..............................Purchase

Order No.¦Product..¦Value¦Description.¦Value
1234......¦Pen........¦23....¦Stationary..¦40
1234......¦Pencil.....¦25....¦................¦
1134......¦Ruler......¦10....¦................¦

Is this possible?????

Thanking you in advance!

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-08-09 : 14:15:35

If you dont want to display "Stationary" on the subsequent rows, then it is more of display issue and this should be handled in the front end.

Thanks
Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-10 : 01:59:38

Where do you want to show the data?
Thats the classic example of Suppress if duplicated which should be done in your front end application

Madhivanan

Failing to plan is Planning to fail

markfairfield
Starting Member

2 Posts

Posted - 2007-08-10 : 03:55:20

I can quite easily suppress it in Crystal Reports,

I was just wondering whether it is possible in SQL..

as the VB developer has created an app for grid reports and this would be ideal for this type of data

But if not gues its back to Crystal!

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-10 : 04:17:06

quote:
Originally posted by markfairfield

I can quite easily suppress it in Crystal Reports,

I was just wondering whether it is possible in SQL..

as the VB developer has created an app for grid reports and this would be ideal for this type of data

But if not gues its back to Crystal!

Cheers

Yes. Doing this in crystal reports is the way to go
If you want to show the suppressed data in VB Grid, then refer my first reply at http://sqlteam.com/forums/topic.asp?TOPIC_ID=76862&whichpage=2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources