I have a SQL query that uses sub query's to calculate some results from tables not used in the main query. I have an issue where the results for the individual results (2.1 & 2.2) are being put on two lines, so the 2.1 result is there and 2.2 is 0 on one line and vice versa. I want it to be like the Expected results below.
SELECT DISTINCT
DATEPART(week, SS.timearrived)
AS [Week No],
SS.timearrived
AS [Date],
RTRIM(SS.vname)
AS [V Name],
SS.vyid
AS [Vy ID],
((SELECT CASE WHEN
(select COUNT(*)
FROM IME
WHERE IME.topos = SS.visid and XCHE.sname IN ('2.1','2.2')
AND movek != 'Y' AND movek != 'S'
GROUP BY IME.topos) IS NULL
THEN '0'
else (select COUNT(*)
FROM IME
WHERE IME.topos = SS.visid and XCHE.sname IN ('2.1','2.2')
AND movek != 'Y' AND movek != 'S'
GROUP BY IME.topos)
END )
+ (SELECT CASE WHEN
(select COUNT(*)
FROM IME
WHERE IME.fmpos = SS.visid and XCHE.sname IN ('2.1','2.2')
GROUP BY IME.fmpos) IS NULL
THEN '0'
else (select COUNT(*)
FROM IME
WHERE IME.fmpos = SS.visid and XCHE.sname IN ('2.1','2.2')
GROUP BY IME.fmpos)
END)
)/(
convert(float,datediff(mi, swork, ework))/60 )
AS [GBP T2],
((SELECT CASE WHEN
(select COUNT(*)
FROM IME
WHERE IME.topos = SS.visid and XCHE.sname IN ('2.1')
AND movek != 'Y' AND movek != 'S'
GROUP BY IME.topos) IS NULL
THEN '0'
else (select COUNT(*)
FROM IME
WHERE IME.topos = SS.visid and XCHE.sname IN ('2.1')
AND movek != 'Y' AND movek != 'S'
GROUP BY IME.topos)
END )
+ (SELECT CASE WHEN
(select COUNT(*)
FROM IME
WHERE IME.fmpos = SS.visid and XCHE.sname IN ('2.1')
GROUP BY IME.fmpos) IS NULL
THEN '0'
else (select COUNT(*)
FROM IME
WHERE IME.fmpos = SS.visid and XCHE.sname IN ('2.1')
GROUP BY IME.fmpos)
END)
)/(
convert(float,datediff(mi, swork, ework))/60 )
AS [GCP 2.1],
((SELECT CASE WHEN
(select COUNT(*)
FROM IME
WHERE IME.topos = SS.visid and XCHE.sname IN ('2.2')
AND movek != 'Y' AND movek != 'S'
GROUP BY IME.topos) IS NULL
THEN '0'
else (select COUNT(*)
FROM IME
WHERE IME.topos = SS.visid and XCHE.sname IN ('2.2')
AND movek != 'Y' AND movek != 'S'
GROUP BY IME.topos)
END )
+ (SELECT CASE WHEN
(select COUNT(*)
FROM IME
WHERE IME.fmpos = SS.visid and XCHE.sname IN ('2.2')
GROUP BY IME.fmpos) IS NULL
THEN '0'
else (select COUNT(*)
FROM IME
WHERE IME.fmpos = SS.visid and XCHE.sname IN ('2.2')
GROUP BY IME.fmpos)
END)
)/(
convert(float,datediff(mi, swork, ework))/60 )
AS [GCP 2.2]
FROM SS
LEFT OUTER JOIN VCME ON SS.visid = vgk
LEFT OUTER JOIN VVVD ON SS.visid = fs01
LEFT OUTER JOIN VCS ON VVVD.vvdgk = VCS.vvdgk
LEFT OUTER JOIN VCSD ON VCS.gk = VCSD.csgk
INNER JOIN XCHE on VCS.cgk = XCHE.gk
Current Results
Week No Date V Name Vy ID GBP T2 GCP 2.1 GCP 2.2
14 2013-03-31 00:01:00.000 FORD 30580 18.72 0 18.72
14 2013-03-31 00:01:00.000 FORD 30580 18.72 18.72 0
Expected Results
Week No Date V Name Vy ID GBP T2 GCP 2.1 GCP 2.2
14 2013-03-31 00:01:00.000 FORD 30580 18.72 18.72 18.72
In your final query, use this as the outermost query to get the result what you want.
SELECT `Week No` , Date , `V Name`, `Vy ID`, `GBP T2`,
MAX(`GCP 2.1`), MAX(`GCP 2.2`)
FROM
( ------------------- REST OF THE INNER QUERY HERE --------------)
GROUP BY `Week No` , Date , `V Name`, `Vy ID`, `GBP T2`;