If statement - query help - 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

General SQL Server Forums

New to SQL Server Programming

If statement - query help

Author

Topic

stewpeanut
Starting Member

1 Post

Posted - 2008-11-03 : 17:44:31

hello...

I'm having problems constructing a select query.
We have a DB that holds Item and pricing data. I use that data to compare against pricing sheets to determine if the pricing feed is successful. My problem is that each Item is represented twice about 98% of the time in a particular table similar to:

Item / Desc / Dept / Price / UPC / ITM_UPC
001 / 1Desc / 1001 / 5.50 / Null / 0002
001 / 1Desc / 1001 / 5.50 / 7999 / 7999

Unfortunately, the 2% that isn't represented twice gives me prpblems.

Ideally, I'd like my select query to compare the two rows and make a determination based on the UPC fields. If 'UPC' is null, return the 'is not null' row. If there is no other row, return the row with the null UPC value.

Anyone have any suggestions?

Thanks.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-03 : 18:15:28

[code]
create table #pricing (
Item varchar(100),
[Desc] varchar(100),
Dept varchar(100),
Price varchar(100),
UPC varchar(100),
ITM_UPC varchar(100)
)

insert into #pricing
select '001', '1Desc', '1001', '5.50', NULL, '0002' union all
select '001', '1Desc', '1001', '5.50', '7999', '7999' union all
select '002', '2Desc', '1001', '5.50', NULL, '6999'

select p1.*
from (select * from #pricing where UPC is not null) p1
left join (select * from #pricing where UPC is NULL) p2
ON p1.item=p2.item and p1.ITM_UPC = p2.ITM_UPC
left join (select Item, count(1) as myCT from #pricing group by Item having count(1) = 2) p3
on p1.item = p3.Item
Union ALL
select p1.*
from (select * from #pricing where UPC is null) p1
join (select Item, count(1) as myCT from #pricing group by Item having count(1) = 1) p3
on p1.item = p3.Item

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 23:22:52

quote:
Originally posted by stewpeanut

hello...

I'm having problems constructing a select query.
We have a DB that holds Item and pricing data. I use that data to compare against pricing sheets to determine if the pricing feed is successful. My problem is that each Item is represented twice about 98% of the time in a particular table similar to:

Item / Desc / Dept / Price / UPC / ITM_UPC
001 / 1Desc / 1001 / 5.50 / Null / 0002
001 / 1Desc / 1001 / 5.50 / 7999 / 7999

Unfortunately, the 2% that isn't represented twice gives me prpblems.

Ideally, I'd like my select query to compare the two rows and make a determination based on the UPC fields. If 'UPC' is null, return the 'is not null' row. If there is no other row, return the row with the null UPC value.

Anyone have any suggestions?

Thanks.

group by Item , Desc, Dept , Price and take max of other two columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources