Another query... - 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)

Another query...

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-23 : 09:59:45

Hi,

I have a table as follows:

TableID GenID Desc PackageID
------------------------------------------
TEST01 Test No Change 01
TEST01 Test No Change 02
TEST01 null Deleted 01
TEST01 null Deleted 04
TEST02 Test2 No Change 03

I need to output my data as follows:

TableID GenID Desc PackageID
------------------------------------------
TEST01 Test No Change 02
TEST01 null Deleted 01
TEST02 Test2 No Change 03

In other words, in instances where duplicate TableID and PackageID's exist I get rid of the one with "No change".

To do this I would do something like:

select
TableID
,max(GenID)
,max(desc)
,PackageID
from
myTable
group by
TableID
,PackageID

In addition to the above I need to remove records that have a "Deleted" desc value and at the same time do not have a duplicate PackageID for the same TableID.

e.g. In the above table within Test01 I have a PackageID of "04". This needs to be removed as there is no instance of a duplicate "04" within Test01 and its desc value is "Deleted".

On the other hand we have TEST02 which has a Package "03". This should remain as the desc value is "No change".

Thanks in advance,
Kabir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 10:07:46

Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96002

E 12°55'05.25"
N 56°04'39.16"

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-23 : 11:22:18

Thanks, but the bit that wasn't covered is the following:

<<<<<
In addition to the above I need to remove records that have a "Deleted" desc value and at the same time do not have a duplicate PackageID for the same TableID.

e.g. In the above table within Test01 I have a PackageID of "04". This needs to be removed as there is no instance of a duplicate "04" within Test01 and its desc value is "Deleted".

On the other hand we have TEST02 which has a Package "03". This should remain as the desc value is "No change".
>>>>>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 11:44:01

I think you will be better served IF you provide some accurate and proper sample data.
In this sample data provide enough samples to cover all situations.

Then ask the question!

If in doubt, read this blog http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 11:51:04

Try this:-

SELECT t.*
FROM Table t
INNER JOIN (SELECT TableID,
            PackageID,
            CASE WHEN MIN(Desc)='Deleted' THEN MIN(GenID)
            ELSE MAX(GenID)
            END AS Value
            GROUP BY TableID,PackageID)t1
ON t1.TableID=t.TableID
AND t1.PackageID=t.PackageID
AND ISNULL(t1.Value,'')=ISNULL(t.GenID,'')

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-23 : 13:11:35

Apologies for the lack of clarity guys.

I think I have sorted this out now.

Cheers,
Kabir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources