Qry 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

SQL Server 2005 Forums

Transact-SQL (2005)

Qry Help.

Author

Topic

sheridanbman
Starting Member

10 Posts

Posted - 2010-03-23 : 00:53:30

Hello,

I have an audit table where people peform audits on orders.

In this example we have 4 orders. Each order contains multiple item audits with a result of good or fail. An order fails if one item is marked as a fail.

Order#|Item|Line|Result

110 Toy 1 Good
110 Movie 1 Good
111 Toy 1 Good
111 Movie 1 Fail
112 DVD 2 Good
112 Toy 2 Fail
113 Movie 3 Fail
113 DVD 3 Good

Basically and order is a Pass (1) or a Fail (0)
Im looking at the desired about.

Line | Pass | Fail
1 1 1
2 0 1
3 0 1

Any help with this would be greatly appreciated.

Thanks,
Brian

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-23 : 01:13:59

quote:
Originally posted by sheridanbman

Line | Pass | Fail
1 1 1
2 0 1
3 0 1

Your requirement is not clear atleast for me.
For line 1 you have value for both pass and fail
For line 2 you have value only for fail but not for pass but in your sample data table you find values for both pass and fail.

Regards,
Pramod

sheridanbman
Starting Member

10 Posts

Posted - 2010-03-23 : 01:20:45

Hi Pramod,
Thanks for your response. The reason why line 2 doesnt show a pass in the results is; 1 or more items in that order failed. Therefore it will count the order as 1 fail. If all items passed for an order it would mark it as a pass. Does that make sense?

quote:
Originally posted by pk_bohra

quote:
Originally posted by sheridanbman

Line | Pass | Fail
1 1 1
2 0 1
3 0 1

Your requirement is not clear atleast for me.
For line 1 you have value for both pass and fail
For line 2 you have value only for fail but not for pass but in your sample data table you find values for both pass and fail.

Regards,
Pramod

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 01:49:43

the 1 and 0 in "Pass (1) or a Fail (0)" is a count or just an indicator ?

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

sheridanbman
Starting Member

10 Posts

Posted - 2010-03-23 : 02:19:14

The 1 or 0 is an indicator by "item". so if I had 5 orders that had no failures it would show 5 pass. I need the count by line.

quote:
Originally posted by khtan

the 1 and 0 in "Pass (1) or a Fail (0)" is a count or just an indicator ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 04:49:50

[code]
DECLARE @sample TABLE
(
[Order#] int,
Item varchar(5),
Line int,
Result varchar(5)
)
INSERT INTO @sample
SELECT 110, 'Toy', 1, 'Good' UNION ALL
SELECT 110, 'Movie', 1, 'Good' UNION ALL
SELECT 111, 'Toy', 1, 'Good' UNION ALL
SELECT 111, 'Movie', 1, 'Fail' UNION ALL
SELECT 112, 'DVD', 2, 'Good' UNION ALL
SELECT 112, 'Toy', 2, 'Fail' UNION ALL
SELECT 113, 'Movie', 3, 'Fail' UNION ALL
SELECT 113, 'DVD', 3, 'Good'

SELECT Line, Pass = SUM(Pass), Fail = SUM(Fail)
FROM
(
SELECT Line, [Order#],
Pass = CASE WHEN MIN(Result) = 'Fail' THEN 0 ELSE 1 END,
Fail = CASE WHEN MIN(Result) = 'Fail' THEN 1 ELSE 0 END
FROM @sample
GROUP BY Line, [Order#]
) s
GROUP BY Line
ORDER BY Line

/*

Line Pass Fail
----------- ----------- -----------
1 1 1
2 0 1
3 0 1

(3 row(s) affected)

*/
[/code]

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-23 : 08:23:25

quote:

Originally posted by khtan


DECLARE @sample TABLE
(
	[Order#]	int,
	Item		varchar(5),
	Line		int,
	Result		varchar(5)
)

I know this is only a sample, but in reality, I wouldn't design a table like that. Item should be an INT FK linked to an items table, and result should be a BIT, with 1 indicating a pass, and 0 a failure.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources