SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server 2005] Between syntax

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

    I need this output:
    Code:
    MANO	32
    MANE	17
    MACO	69
    MASO	45
    Tot	163
    Instead i have this other and wrong output:
    Code:
    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.
    Code:
    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;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,127
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Try this
    Code:
    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.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot for your help!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •