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;