[SQL Server 2005] Between syntax

[cms9651]

Hi all, I return after time because I have problem with this sql query in my DB Sql Server 2005.

I need this output:

MANO	32
MANE	17
MACO	69
MASO	45
Tot	163

Instead i have this other and wrong output:

MANO	29
MANO	3
Tot	32
MANE	15
MANE	2
Tot	17
MACO	15
MACO	2
MACO	52
Tot	69
MASO	33
MASO	12
Tot	45

I don't understand in the output I have one row for each date range included in the between syntax.
Can you help me?
Thank you.

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[A]
FROM
	(
		SELECT DISTINCT
			[myDate],
			CASE
		WHEN LEFT (TZZ, 2) = 'XD'
		OR LEFT (TZZ, 2) = 'XF' THEN
			'MANO'
		WHEN LEFT (TZZ, 2) = 'XG'
		OR LEFT (TZZ, 2) = 'XH' THEN
			'MANE'
		WHEN LEFT (TZZ, 2) = 'XI'
		OR LEFT (TZZ, 2) = 'XM'
		OR LEFT (TZZ, 2) = 'XS'
		OR LEFT (TZZ, 2) = 'XO' THEN
			'MACO'
		WHEN LEFT (TZZ, 2) = 'XP'
		OR LEFT (TZZ, 2) = 'XQ'
		OR LEFT (TZZ, 2) = 'XR' THEN
			'MASO'
		ELSE
			'Tot'
		END AS [MAT],
		SUM (
			CASE
			WHEN (
				[R-M] = '1'
				OR [R-M] = '2'
				OR [R-M] = '3'
				OR [R-M] = '4'
			)
			AND [oldDate] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [A]
	FROM
		dbo_40
	WHERE
        [TZZ] NOT LIKE 'LG%'
	GROUP BY
		[myDate],
		CASE
	WHEN LEFT (TZZ, 2) = 'XD'
	OR LEFT (TZZ, 2) = 'XF' THEN
		'MANO'
	WHEN LEFT (TZZ, 2) = 'XG'
	OR LEFT (TZZ, 2) = 'XH' THEN
		'MANE'
	WHEN LEFT (TZZ, 2) = 'XI'
	OR LEFT (TZZ, 2) = 'XM'
	OR LEFT (TZZ, 2) = 'XS'
	OR LEFT (TZZ, 2) = 'XO' THEN
		'MACO'
	WHEN LEFT (TZZ, 2) = 'XP'
	OR LEFT (TZZ, 2) = 'XQ'
	OR LEFT (TZZ, 2) = 'XR' THEN
		'MASO'
	ELSE
		'Tot'
	END WITH ROLLUP
	) AS SubQs
WHERE
[myDate] BETWEEN '2012-07-01'
AND '2012-10-08'
ORDER BY
	CASE MAT
WHEN 'MANO' THEN
	1
WHEN 'MANE' THEN
	2
WHEN 'MACO' THEN
	3
WHEN 'MASO' THEN
	4
ELSE
	5
END;

[cpradio]

Try this

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[A]
FROM
	(
		SELECT DISTINCT
			CASE
		WHEN LEFT (TZZ, 2) = 'XD'
		OR LEFT (TZZ, 2) = 'XF' THEN
			'MANO'
		WHEN LEFT (TZZ, 2) = 'XG'
		OR LEFT (TZZ, 2) = 'XH' THEN
			'MANE'
		WHEN LEFT (TZZ, 2) = 'XI'
		OR LEFT (TZZ, 2) = 'XM'
		OR LEFT (TZZ, 2) = 'XS'
		OR LEFT (TZZ, 2) = 'XO' THEN
			'MACO'
		WHEN LEFT (TZZ, 2) = 'XP'
		OR LEFT (TZZ, 2) = 'XQ'
		OR LEFT (TZZ, 2) = 'XR' THEN
			'MASO'
		ELSE
			'Tot'
		END AS [MAT],
		SUM (
			CASE
			WHEN (
				[R-M] = '1'
				OR [R-M] = '2'
				OR [R-M] = '3'
				OR [R-M] = '4'
			)
			AND [oldDate] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [A]
	FROM
		dbo_40
	WHERE
        [TZZ] NOT LIKE 'LG%' AND [myDate] BETWEEN '2012-07-01' AND '2012-10-08'
	GROUP BY
		CASE
	WHEN LEFT (TZZ, 2) = 'XD'
	OR LEFT (TZZ, 2) = 'XF' THEN
		'MANO'
	WHEN LEFT (TZZ, 2) = 'XG'
	OR LEFT (TZZ, 2) = 'XH' THEN
		'MANE'
	WHEN LEFT (TZZ, 2) = 'XI'
	OR LEFT (TZZ, 2) = 'XM'
	OR LEFT (TZZ, 2) = 'XS'
	OR LEFT (TZZ, 2) = 'XO' THEN
		'MACO'
	WHEN LEFT (TZZ, 2) = 'XP'
	OR LEFT (TZZ, 2) = 'XQ'
	OR LEFT (TZZ, 2) = 'XR' THEN
		'MASO'
	ELSE
		'Tot'
	END WITH ROLLUP
	) AS SubQs
ORDER BY
	CASE MAT
WHEN 'MANO' THEN
	1
WHEN 'MANE' THEN
	2
WHEN 'MACO' THEN
	3
WHEN 'MASO' THEN
	4
ELSE
	5
END;

I moved your WHERE clause into your derived table, so that [myDate] would not be part of the DISTINCT set, once that was done, it groups as expected.

[cms9651]

thanks a lot for your help!