WHERE .. CASE statement? Need some advice.

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 2005 Forums

Transact-SQL (2005)

WHERE .. CASE statement? Need some advice.

Author

Topic

richardf76
Starting Member

2 Posts

Posted - 2014-05-15 : 22:42:06

Hi all, just need some help with a query I'm working on, sample data below.

I need to return the "upc_number" based on the following conditions.
- each sku_id may have several different upc_numbers attached to it, the upc_id is a primary key field and unique.
- I need to check the upc_type first, it may have a 1 or 2 or both types.
- for each sku_id, I need to return just 1 x upc_number
- if there's a upc_type=2 then check the MAX(upc_id) to give me the upc_number. I need to find upc_type=2. If upc_type=2 exists, then ignore upc_type=1 in the results.
- if there's no upc_type=2, then I need to use upc_type=1 and then check the MAX(upc_id) to give me the upc_number

I believe this may need a WHERE .. CASE statement, not sure, but hope someone can give me some pointers on where to start looking so I can test a few queries on a larger data table.

Thanks if anyone can assist, Rich.

upc_id sku_id upc_type upc_number
60002838800016 2838800015 1 9312352527366
60002838800017 2838800015 2 2000000086927
60002838800018 2838800015 2 2000000087245
60002838800019 2838800015 2 2000000090863
60002838800020 2838800015 2 2000000093345
60002838800021 2838800016 2 2000000093346
60002838800022 2838800016 2 2000000093347
60002838800023 2838800016 2 2000000093348
60002838800024 2838800017 1 2000000093341
60002838800025 2838800017 1 2000000093342
60002838800026 2838800017 1 2000000093343

richardf76
Starting Member

2 Posts

Posted - 2014-05-15 : 22:52:31

Apologies for the formatting on the data, the web page condensed the tabs.
upc_id___________sku_id_______upc_type_upc_number
60002838800016 . 2838800015 . 1 . 9312352527366
60002838800017 . 2838800015 . 2 . 2000000086927
60002838800018 . 2838800015 . 2 . 2000000087245
60002838800019 . 2838800015 . 2 . 2000000090863
60002838800020 . 2838800015 . 2 . 2000000093345
60002838800021 . 2838800016 . 2 . 2000000093346
60002838800022 . 2838800016 . 2 . 2000000093347
60002838800023 . 2838800016 . 2 . 2000000093348
60002838800024 . 2838800017 . 1 . 2000000093341
60002838800025 . 2838800017 . 1 . 2000000093342
60002838800026 . 2838800017 . 1 . 2000000093343

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-19 : 02:53:04

Hi richard ,

May be this is what you want


----------------------Table Creation-------------------------------------------------------------------
CREATE TABLE Temp(upc_id BIGINT,sku_id BIGINT,upc_type INT,upc_number BIGINT)
INSERT INTO Temp
SELECT 60002838800016,2838800015,1,9312352527366 UNION ALL
SELECT 60002838800017,2838800015 ,2, 2000000086927 UNION ALL
SELECT 60002838800018 ,2838800015 ,2, 2000000087245 UNION ALL
SELECT 60002838800019 ,2838800015 ,2, 2000000090863 UNION ALL
SELECT 60002838800020 ,2838800015 ,2, 2000000093345 UNION ALL
SELECT 60002838800021 ,2838800016 ,2, 2000000093346 UNION ALL
SELECT 60002838800022 ,2838800016 ,2, 2000000093347 UNION ALL
SELECT 60002838800023 ,2838800016 ,2, 2000000093348 UNION ALL
SELECT 60002838800024 ,2838800017 ,1, 2000000093341 UNION ALL
SELECT 60002838800025 ,2838800017 ,1, 2000000093342 UNION ALL
SELECT 60002838800026 ,2838800017 ,1, 2000000093343

----------------------Query ----------------------------------------------------------------------------
SELECT	         upc_id
		,sku_id
		,upc_type
		,CASE WHEN MAX(upc_type) OVER (PARTITION BY sku_id) = upc_type  THEN upc_number END ASupc_number 
FROM	 Temp 
WHERE	 upc_id IN (SELECT MAX(upc_id) OVER (PARTITION BY sku_id) FROM temp)
--------------------------------------------------------------------------------------------------------

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources