Getting the record having maximum type of errors.

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)

Getting the record having maximum type of errors.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-08-07 : 10:37:10

Hi guys,

I have a resultset which comes to this

Vehiclename ----Error1 ----Error2 --Error3

WA53AEU ------ No ----- No------ Yes

WA53AEU ------ No ----- Yes------ No

WA53AEU ------ No ----- Yes------ Yes

WJ55DDF------ No ------ No------ Yes

I want to return the vehicle wise the maximum errors a vehicle has.For example vehicle WA53AEU has Error1 as No Error2 as No Error3 as No then it becomes No,Yes,No then again it becomes No,Yes and Yes.So I want the 3rd record to be returned as it has the maximum Yes for the 3 types of errors.For WJ55DDF it should return just the No,No,Yes record.

Thanks for any kind help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 10:41:46

Your explanation does not comply with provided sample data.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-07 : 10:41:52

do have a primary key in the table ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 10:47:04

[code]DECLARE @Sample TABLE (VehicleName VARCHAR(20), Error1 VARCHAR(3), Error2 VARCHAR(3), Error3 VARCHAR(3))

INSERT @Sample
SELECT 'WA53AEU', 'No', 'No', 'Yes' UNION ALL
SELECT 'WA53AEU', 'No', 'Yes', 'No' UNION ALL
SELECT 'WA53AEU', 'No', 'Yes', 'Yes' UNION ALL
SELECT 'WJ55DDF', 'No', 'No', 'Yes'

SELECT VehicleName,
CASE WHEN theValue & 1 = 1 THEN 'Yes' ELSE 'No' END AS Error1,
CASE WHEN theValue & 2 = 2 THEN 'Yes' ELSE 'No' END AS Error2,
CASE WHEN theValue & 4 = 4 THEN 'Yes' ELSE 'No' END AS Error3
FROM (
SELECT VehicleName,
theValue,
ROW_NUMBER() OVER (PARTITION BY VehicleName ORDER BY theValue DESC) AS RecID
FROM (
SELECT VehicleName,
1 * CASE WHEN Error1 = 'Yes' THEN 1 ELSE 0 END
+ 2 * CASE WHEN Error2 = 'Yes' THEN 1 ELSE 0 END
+ 4 * CASE WHEN Error3 = 'Yes' THEN 1 ELSE 0 END AS theValue
FROM @Sample
) AS d
) AS q
WHERE RecID = 1[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 10:50:52

You can rewrite my solution above (which also weights the errors) to a solution which only do counts.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-07 : 11:05:19


select t1.vehiclename,t1.error1,t1.error2,t1.error3 from
(
	select *, 
		CASE WHEN Error1 = 'Yes' THEN 1 ELSE 0 END+
		CASE WHEN Error2 = 'Yes' THEN 1 ELSE 0 END+
		CASE WHEN Error3 = 'Yes' THEN 1 ELSE 0 END as counting
	from @sample
) as t1 inner join
(
	select vehiclename, max(counting) as counting
	from 
	(
	select *, 
		CASE WHEN Error1 = 'Yes' THEN 1 ELSE 0 END+
		CASE WHEN Error2 = 'Yes' THEN 1 ELSE 0 END+
		CASE WHEN Error3 = 'Yes' THEN 1 ELSE 0 END as counting
	from @sample
	) as t
	group by vehiclename
) as t2
on t1.vehiclename=t2.vehiclename and t1.counting=t2.counting

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 11:11:18

quote:
Originally posted by ayamas

Hi guys,

I have a resultset which comes to this

Vehiclename ----Error1 ----Error2 --Error3

WA53AEU ------ No ----- No------ Yes

WA53AEU ------ No ----- Yes------ No

WA53AEU ------ No ----- Yes------ Yes

WJ55DDF------ No ------ No------ Yes

I want to return the vehicle wise the maximum errors a vehicle has.For example vehicle WA53AEU has Error1 as No Error2 as No Error3 as No then it becomes No,Yes,No then again it becomes No,Yes and Yes.So I want the 3rd record to be returned as it has the maximum Yes for the 3 types of errors.For WJ55DDF it should return just the No,No,Yes record.

Thanks for any kind help.

;With Vehicle_CTE(Vehiclename,Error1 ,Error2 ,Error3,ErrorCount) 
AS
(SELECT Vehiclename ,Error1 ,Error2,Error3,
CASE WHEN Error1='Yes' THEN 1 ELSE 0 END +CASE WHEN Error2='Yes' THEN 1 ELSE 0 END+CASE WHEN Error3='Yes' THEN 1 ELSE 0 END 
FROm YourTable)

SELECT v1.Vehiclename ,v1.Error1 ,v1.Error2,v1.Error3
FROM Vehicle_CTE v1
INNER JOIN (SELECT Vehiclename,MAX(ErrorCount) AS MaxErrors
            FROM Vehicle_CTE
            GROUP BY Vehiclename)v2
ON v2.Vehiclename=v1.Vehiclename
AND v2.MaxErrors=v1.ErrorCount

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-08-07 : 12:11:37

Guys you people are great.Thanks for giving such wonderfull solutions.I greatly appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources