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
    4,810
    Mentioned
    141 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.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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
  •