quote:

---

Originally posted by bsheridan

I'm trying to run Audit data for our network. Here is the way the data is stored.

ID DATE Order Site Wrong Overpick Damage Missing
1 1/1/2008 7456345 NY 0 0 0 0
2 1/1/2008 8234645 NV 1 1 1 1
3 1/1/2008 1657768 PA 0 0 4 0
4 1/1/2008 3452345 CA 1 2 0 0
5 1/1/2008 2456524 KS 0 0 3 0
6 1/2/2008 4345345 NY 0 0 0 0
7 1/2/2008 8554345 NV 4 1 1 4
8 1/2/2008 3242343 PA 0 0 0 0
9 1/2/2008 2340505 CA 0 0 0 0
10 1/2/2008 3214234 KS 2 1 1 1

I'm trying to output the results like this

Total Orders Failed Orders Total Errors Wrong OverPick Damage Missing
1/1/2008 5 4 14 2 3 8 1
1/2/2008 5 2 15 6 2 2 5


Total Orders: Count of ID's grouped in the Date

Failed Orders: Should check to see if "wrong, overpick, damage, missing are > 0. that marks it as a failed order.

Total Errors: Sum of wrong, overpick, damage, missing

If anyone can help me with this query I would much appreciate it. I'm at a stand still on this.

Thanks,

---

quote:

---

Originally posted by webfred

quote:

---

Originally posted by bsheridan

I'm trying to run Audit data for our network. Here is the way the data is stored.

ID DATE Order Site Wrong Overpick Damage Missing
1 1/1/2008 7456345 NY 0 0 0 0
2 1/1/2008 8234645 NV 1 1 1 1
3 1/1/2008 1657768 PA 0 0 4 0
4 1/1/2008 3452345 CA 1 2 0 0
5 1/1/2008 2456524 KS 0 0 3 0
6 1/2/2008 4345345 NY 0 0 0 0
7 1/2/2008 8554345 NV 4 1 1 4
8 1/2/2008 3242343 PA 0 0 0 0
9 1/2/2008 2340505 CA 0 0 0 0
10 1/2/2008 3214234 KS 2 1 1 1

I'm trying to output the results like this

Total Orders Failed Orders Total Errors Wrong OverPick Damage Missing
1/1/2008 5 4 14 2 3 8 1
1/2/2008 5 2 15 6 2 2 5


Total Orders: Count of ID's grouped in the Date

Failed Orders: Should check to see if "wrong, overpick, damage, missing are > 0. that marks it as a failed order.

Total Errors: Sum of wrong, overpick, damage, missing

If anyone can help me with this query I would much appreciate it. I'm at a stand still on this.

Thanks,

---

That is not fitting.
Please provide some code for take data in a tmp table and describe exactly your desired result.

Webfred

---

quote:

---

Originally posted by khtan

DECLARE @TABLE TABLE
(
	[ID] 		int,
	[DATE] 		datetime,
	[Order] 	int,
	Site 		varchar(2),
	Wrong 		int,
	Overpick 	int,
	Damage 		int,
	Missing		int
)
INSERT INTO @TABLE
SELECT	 1, '1/1/2008', 7456345, 'NY', 0, 0, 0, 0 UNION ALL
SELECT	 2, '1/1/2008', 8234645, 'NV', 1, 1, 1, 1 UNION ALL
SELECT	 3, '1/1/2008', 1657768, 'PA', 0, 0, 4, 0 UNION ALL
SELECT	 4, '1/1/2008', 3452345, 'CA', 1, 2, 0, 0 UNION ALL
SELECT	 5, '1/1/2008', 2456524, 'KS', 0, 0, 3, 0 UNION ALL
SELECT	 6, '1/2/2008', 4345345, 'NY', 0, 0, 0, 0 UNION ALL
SELECT	 7, '1/2/2008', 8554345, 'NV', 4, 1, 1, 4 UNION ALL
SELECT	 8, '1/2/2008', 3242343, 'PA', 0, 0, 0, 0 UNION ALL
SELECT	 9, '1/2/2008', 2340505, 'CA', 0, 0, 0, 0 UNION ALL
SELECT	10, '1/2/2008', 3214234, 'KS', 2, 1, 1, 1

SELECT	[DATE], 
	[Total Orders]  = COUNT([ID]),
	[Total Failed]	= SUM(CASE WHEN Wrong > 0 OR Overpick > 0 OR Damage > 0 OR Missing > 0 THEN 1 ELSE 0 END),
	[Total Errors]	= SUM(Wrong + Overpick + Damage + Missing),
	Wrong		= SUM(Wrong),
	Overpick	= SUM(Overpick),
	Damage		= SUM(Damage),
	Missing		= SUM(Missing)
FROM	@TABLE
GROUP BY [DATE]

/*
DATE         Total Orders Total Failed Total Errors Wrong       Overpick    Damage      Missing     
------------ ------------ ------------ ------------ ----------- ----------- ----------- ----------- 
2008-01-01              5            4           14           2           3           8           1 
2008-01-02              5            2           15           6           2           2           5 

(2 row(s) affected)
*/

---

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

---