Systax problem - 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 2000 Forums

Transact-SQL (2000)

Systax problem

Author

Topic

jeusdi
Starting Member

27 Posts

Posted - 2007-11-29 : 04:17:31

I have a sentence as follow but the prblem is when I want to group the result-->

SELECT	s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description,
		SUM([time]) = (SELECT DATEDIFF(second, s.[date], e.[date]) - CASE COUNT(*)
				WHEN 0 THEN 0
				ELSE (SELECT SUM(DATEDIFF(second, ts.[date], te.[date])) FROM @sample ts, @sample te
					WHERE ts.order_id = s.order_id
						AND	ts.worker = s.worker
						AND	ts.machine = s.machine
						AND 	ts.order_id = te.order_id
					 	AND 	ts.worker = te.worker
						AND 	ts.machine = te.machine
						AND 	ts.[date] > s.[date]
						AND 	te.[date] < e.[date]
						AND 	ts.state_id = 10
						AND 	ts.state_id = te.state_id - 1
						AND 	te.[date] = (SELECT MIN(te2.[date]) FROM @sample te2 
									WHERE te2.state_id = 11 AND te2.[date] > ts.[date] AND te2.[date] < e.[date]))
				END
			  FROM @sample t WHERE t.state_id IN (10, 11) AND t.[date] > s.[date] AND t.[date] < e.[date])
	FROM	@sample s, @sample e
	WHERE	s.order_id	= e.order_id
		AND	s.worker	= e.worker
		AND	s.machine	= e.machine
		AND	s.state_id + 1	= e.state_id
		AND	s.state_id	IN (2,3,4)
		AND	s.[date]	= (SELECT MIN(s3.[date]) FROM @sample s3 WHERE s3.order_id = s.order_id AND s3.worker = s.worker AND s3.machine = s.machine AND s3.[date] <= s.[date] AND s3.state_id = s.state_id AND s3.[date] >= (SELECT MAX(s2.[date]) FROM @sample s2 WHERE s2.order_id = s.order_id AND s2.worker = s.worker AND s2.machine = s.machine AND s2.state_id = 1 AND s2.[date] <= s.[date]))
		AND	e.[date]	= (SELECT MIN(e3.[date]) FROM @sample e3 WHERE e3.order_id = e.order_id AND e3.worker = e.worker AND e3.machine = e.machine AND e3.[date] <= e.[date] AND e3.[date] >= s.[date] AND e3.state_id = e.state_id)
	GROUP BY s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description

The result without apply the grouping is -->

quote:

305 mari 2007-06-29 15:13:30.590 2007-06-29 15:45:26.123 2 3 PREPARATION 1916
305 mari 2007-06-29 15:45:26.123 2007-06-29 17:46:25.077 3 4 PRODUCTION 7099
305 mari 2007-06-29 17:46:25.077 2007-06-29 18:23:39.653 4 5 CLEANING 574
305 mari 2007-06-30 15:13:30.590 2007-06-30 15:45:26.123 2 3 PREPARATION 1916
305 mari 2007-06-30 15:45:26.123 2007-06-30 17:46:25.077 3 4 PRODUCTION 7099
305 mari 2007-06-30 17:46:25.077 2007-06-30 18:23:39.653 4 5 CLEANING 574

The result I hope is -->

quote:

305 mari 2007-06-29 15:13:30.590 2007-06-29 15:45:26.123 2 3 PREPARATION 3832
305 mari 2007-06-29 15:45:26.123 2007-06-29 17:46:25.077 3 4 PRODUCTION 14198
305 mari 2007-06-29 17:46:25.077 2007-06-29 18:23:39.653 4 5 CLEANING 1148

Servidor: mensaje 170, nivel 15, estado 1, línea 2
Línea 53: syntax problem near '='.
Servidor: mensaje 156, nivel 15, estado 1, línea 19
Sintaxis syntax problem near 'FROM'.
Servidor: mensaje 156, nivel 15, estado 1, línea 26
Sintaxis syntax problem near 'AND'.
Servidor: mensaje 156, nivel 15, estado 1, línea 27
Sintaxis syntax problem near 'GROUP'.

Can you help me please?
Thanks for all in advance

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-29 : 04:32:06

Hi u need to specify

SUM (
SELECT DATEDIFF(second, s.[date], e.[date]) - CASE COUNT(*)
WHEN 0 THEN 0
ELSE (SELECT SUM(DATEDIFF(second, ts.[date], te.[date])) FROM @sample ts, @sample te
WHERE ts.order_id = s.order_id
AND ts.worker = s.worker
AND ts.machine = s.machine
AND ts.order_id = te.order_id
AND ts.worker = te.worker
AND ts.machine = te.machine
AND ts.[date] > s.[date]
AND te.[date] < e.[date]
AND ts.state_id = 10
AND ts.state_id = te.state_id - 1
AND te.[date] = (SELECT MIN(te2.[date]) FROM @sample te2
WHERE te2.state_id = 11 AND te2.[date] > ts.[date] AND te2.[date] < e.[date]))
END
FROM @sample t WHERE t.state_id IN (10, 11) AND t.[date] > s.[date] AND t.[date] < e.[date])

jeusdi
Starting Member

27 Posts

Posted - 2007-11-29 : 05:06:48

[code] SELECT s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description,
[time] = SUM(SELECT DATEDIFF(second, s.[date], e.[date]) - CASE COUNT(*)
WHEN 0 THEN 0
ELSE (SELECT SUM(DATEDIFF(second, ts.[date], te.[date])) FROM @sample ts, @sample te
WHERE ts.order_id = s.order_id
AND ts.worker = s.worker
AND ts.machine = s.machine
AND ts.order_id = te.order_id
AND ts.worker = te.worker
AND ts.machine = te.machine
AND ts.[date] > s.[date]
AND te.[date] < e.[date]
AND ts.state_id = 10
AND ts.state_id = te.state_id - 1
AND te.[date] = (SELECT MIN(te2.[date]) FROM @sample te2
WHERE te2.state_id = 11 AND te2.[date] > ts.[date] AND te2.[date] < e.[date]))
END
FROM @sample t WHERE t.state_id IN (10, 11) AND t.[date] > s.[date] AND t.[date] < e.[date])
FROM @sample s, @sample e
WHERE s.order_id = e.order_id
AND s.worker = e.worker
AND s.machine = e.machine
AND s.state_id + 1 = e.state_id
AND s.state_id IN (2,3,4)
AND s.[date] = (SELECT MIN(s3.[date]) FROM @sample s3 WHERE s3.order_id = s.order_id AND s3.worker = s.worker AND s3.machine = s.machine AND s3.[date] <= s.[date] AND s3.state_id = s.state_id AND s3.[date] >= (SELECT MAX(s2.[date]) FROM @sample s2 WHERE s2.order_id = s.order_id AND s2.worker = s.worker AND s2.machine = s.machine AND s2.state_id = 1 AND s2.[date] <= s.[date]))
AND e.[date] = (SELECT MIN(e3.[date]) FROM @sample e3 WHERE e3.order_id = e.order_id AND e3.worker = e.worker AND e3.machine = e.machine AND e3.[date] <= e.[date] AND e3.[date] >= s.[date] AND e3.state_id = e.state_id)
GROUP BY s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description[/code]

I've tested it, but it doesn't run -->

Errors -->

quote:

Servidor: mensaje 156, nivel 15, estado 1, línea 2
Sintaxis syntax error near 'SELECT'.
Servidor: mensaje 170, nivel 15, estado 1, línea 18
syntax error near ')'.
Servidor: mensaje 156, nivel 15, estado 1, línea 26
syntax error near 'AND'.
Servidor: mensaje 156, nivel 15, estado 1, línea 27
syntax error near 'GROUP'.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-29 : 05:34:00

try this

SELECT s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description,
[time] = SUM(SELECT DATEDIFF(second, s.[date], e.[date]) -
(CASE COUNT(*) WHEN 0 THEN 0
ELSE ( SELECT SUM(DATEDIFF(second, ts.[date], te.[date]))
FROM @sample ts, @sample te
WHERE ts.order_id = s.order_id
AND ts.worker = s.worker
AND ts.machine = s.machine
AND ts.order_id = te.order_id
AND ts.worker = te.worker
AND ts.machine = te.machine
AND ts.[date] > s.[date]
AND te.[date] < e.[date]
AND ts.state_id = 10
AND ts.state_id = te.state_id - 1
AND te.[date] = (SELECT MIN(te2.[date]) FROM @sample te2
WHERE te2.state_id = 11 AND te2.[date] > ts.[date] AND te2.[date] < e.[date]))
END)
FROM @sample t
WHERE t.state_id IN (10, 11) AND t.[date] > s.[date] AND t.[date] < e.[date]
GROUP BY s.[date], e.[date]
)
FROM @sample s, @sample e
WHERE s.order_id = e.order_id
AND s.worker = e.worker
AND s.machine = e.machine
AND s.state_id + 1 = e.state_id
AND s.state_id IN (2,3,4)
AND s.[date] = (SELECT MIN(s3.[date]) FROM @sample s3 WHERE s3.order_id = s.order_id AND s3.worker = s.worker AND s3.machine = s.machine AND s3.[date] <= s.[date] AND s3.state_id = s.state_id AND s3.[date] >= (SELECT MAX(s2.[date]) FROM @sample s2 WHERE s2.order_id = s.order_id AND s2.worker = s.worker AND s2.machine = s.machine AND s2.state_id = 1 AND s2.[date] <= s.[date]))
AND e.[date] = (SELECT MIN(e3.[date]) FROM @sample e3 WHERE e3.order_id = e.order_id AND e3.worker = e.worker AND e3.machine = e.machine AND e3.[date] <= e.[date] AND e3.[date] >= s.[date] AND e3.state_id = e.state_id)
GROUP BY s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description

jeusdi
Starting Member

27 Posts

Posted - 2007-11-29 : 06:07:20

It' doesn't run. Appering the same problems.

Mmm, I write you the complete SQL statement in order you can test it -->

DECLARE @sample TABLE
(
	order_id		int,
	nothing			int,
	worker 			varchar(4),
	[date]			datetime,
	state_id		int, 
	state_description	varchar(15),
	machine			int
)

INSERT INTO @sample
SELECT 305, 0, 'mari', '2007-29-06 15:13:30.560', 1, 'BEGIN', 0  UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 15:13:30.590', 2, 'PREPARATION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 15:14:50.521', 2, 'PREPARATION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 15:45:26.123', 3, 'PRODUCTION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:34:56.145', 3, 'PRODUCTION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:35:12.967', 10, 'PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:36:32.651', 11, 'END-PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:37:12.967', 10, 'PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:38:32.651', 11, 'END-PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:39:45.765', 3, 'PRODUCTION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 17:46:25.077', 4, 'CLEANING', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 17:47:11.451', 4, 'CLEANING', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 17:48:05.763', 10, 'PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 18:15:45.233', 11, 'END-PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 18:23:39.653', 5, 'END', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 15:13:30.560', 1, 'BEGIN', 0  UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 15:13:30.590', 2, 'PREPARATION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 15:14:50.521', 2, 'PREPARATION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 15:45:26.123', 3, 'PRODUCTION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 16:34:56.145', 3, 'PRODUCTION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 16:35:12.967', 10, 'PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 16:36:32.651', 11, 'END-PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 16:37:12.967', 10, 'PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 16:38:32.651', 11, 'END-PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 16:39:45.765', 3, 'PRODUCTION', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 17:46:25.077', 4, 'CLEANING', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 17:47:11.451', 4, 'CLEANING', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 17:48:05.763', 10, 'PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 18:15:45.233', 11, 'END-PAUSE', 0 UNION ALL
SELECT 305, 0, 'mari', '2007-30-06 18:23:39.653', 5, 'END', 0

	SELECT	s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description,
		[time] = SUM(SELECT DATEDIFF(second, s.[date], e.[date]) - CASE COUNT(*)
				WHEN 0 THEN 0
				ELSE (SELECT SUM(DATEDIFF(second, ts.[date], te.[date])) FROM @sample ts, @sample te
					WHERE ts.order_id = s.order_id
						AND	ts.worker = s.worker
						AND	ts.machine = s.machine
						AND 	ts.order_id = te.order_id
					 	AND 	ts.worker = te.worker
						AND 	ts.machine = te.machine
						AND 	ts.[date] > s.[date]
						AND 	te.[date] < e.[date]
						AND 	ts.state_id = 10
						AND 	ts.state_id = te.state_id - 1
						AND 	te.[date] = (SELECT MIN(te2.[date]) FROM @sample te2 
									WHERE te2.state_id = 11 AND te2.[date] > ts.[date] AND te2.[date] < e.[date]))
				END
			  FROM @sample t WHERE t.state_id IN (10, 11) AND t.[date] > s.[date] AND t.[date] < e.[date])
			GROUP BY s.[date], e.[date]
	FROM	@sample s, @sample e
	WHERE	s.order_id	= e.order_id
		AND	s.worker	= e.worker
		AND	s.machine	= e.machine
		AND	s.state_id + 1	= e.state_id
		AND	s.state_id	IN (2,3,4)
		AND	s.[date]	= (SELECT MIN(s3.[date]) FROM @sample s3 WHERE s3.order_id = s.order_id AND s3.worker = s.worker AND s3.machine = s.machine AND s3.[date] <= s.[date] AND s3.state_id = s.state_id AND s3.[date] >= (SELECT MAX(s2.[date]) FROM @sample s2 WHERE s2.order_id = s.order_id AND s2.worker = s.worker AND s2.machine = s.machine AND s2.state_id = 1 AND s2.[date] <= s.[date]))
		AND	e.[date]	= (SELECT MIN(e3.[date]) FROM @sample e3 WHERE e3.order_id = e.order_id AND e3.worker = e.worker AND e3.machine = e.machine AND e3.[date] <= e.[date] AND e3.[date] >= s.[date] AND e3.state_id = e.state_id)
	GROUP BY s.order_id, s.worker, s.[date], e.[date], s.state_id, e.state_id, s.state_description

jeusdi
Starting Member

27 Posts

Posted - 2007-11-30 : 03:10:42

SELECT	s.order_id, s.worker, s.machine,
		[time] = SUM(DATEDIFF(second, s.[date], e.[date])) - (SELECT CASE COUNT(*)
				WHEN 0 THEN 0
				ELSE (SELECT SUM(DATEDIFF(second, ts.[date], te.[date])) FROM @sample ts, @sample te
					WHERE ts.order_id = s.order_id
						AND	ts.worker = s.worker
						AND	ts.machine = s.machine
						AND 	ts.order_id = te.order_id
					 	AND 	ts.worker = te.worker
						AND 	ts.machine = te.machine
						AND 	ts.[date] > s.[date]
						AND 	te.[date] < e.[date]
						AND 	ts.state_id = 10
						AND 	ts.state_id = te.state_id - 1
						AND 	te.[date] = (SELECT MIN(te2.[date]) FROM @sample te2 
									WHERE te2.state_id = 11 AND te2.[date] > ts.[date] AND te2.[date] < e.[date]))
				END
			  FROM @sample t WHERE t.state_id IN (10, 11) AND t.[date] > s.[date] AND t.[date] < e.[date])
	FROM	@sample s, @sample e
	WHERE	s.order_id	= e.order_id
		AND	s.worker	= e.worker
		AND	s.machine	= e.machine
		AND	s.state_id + 1	= e.state_id
		AND	s.state_id	IN (2,3,4)
		AND	s.[date]	= (SELECT MIN(s3.[date]) FROM @sample s3 WHERE s3.order_id = s.order_id AND s3.worker = s.worker AND s3.machine = s.machine AND s3.[date] <= s.[date] AND s3.state_id = s.state_id AND s3.[date] >= (SELECT MAX(s2.[date]) FROM @sample s2 WHERE s2.order_id = s.order_id AND s2.worker = s.worker AND s2.machine = s.machine AND s2.state_id = 1 AND s2.[date] <= s.[date]))
		AND	e.[date]	= (SELECT MIN(e3.[date]) FROM @sample e3 WHERE e3.order_id = e.order_id AND e3.worker = e.worker AND e3.machine = e.machine AND e3.[date] <= e.[date] AND e3.[date] >= s.[date] AND e3.state_id = e.state_id)
	GROUP BY s.order_id, s.worker, s.machine

Now, The error message is -->

quote:

Servidor: mensaje 8120, nivel 16, estado 1, línea 52
's.date' column of sellection list is invalid, because is not in agregate function neither into a GROUP BY cluase.
Servidor: mensaje 8120, nivel 16, estado 1, línea 52
'e.date' column of sellection list is invalid, because is not in agregate function neither into a GROUP BY cluase.
Servidor: mensaje 8120, nivel 16, estado 1, línea 52
'e.date' column of sellection list is invalid, because is not in agregate function neither into a GROUP BY cluase.
Servidor: mensaje 8120, nivel 16, estado 1, línea 52
'w.date' column of sellection list is invalid, because is not in agregate function neither into a GROUP BY cluase.
Servidor: mensaje 8120, nivel 16, estado 1, línea 52
'e.date' column of sellection list is invalid, because is not in agregate function neither into a GROUP BY cluase.

If I add s.date/e.date into GROUP BY clause the result will not be added or grouped!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources