Select Statement.

[bhanab]

Hello,

Please can someone help me with this select statement? How do I get the statement to keep the week number static? ie if there are no values then it should show up as NULL, zero or just stay blank.

This is the statment :--

Select a.[Weeknum], b.[No Severity], c.[Severity 3], d.[Severity 2], e.[Severity 1]
From (SELECT Weeknum
FROM [BE].[dbo].[BES$]
Group by Weeknum)
A,
(SELECT Weeknum, Count (*) AS [No Severity]
FROM [BE].[dbo].[BES$]
Where Severity = 'No Severity'
And Team = 'AS400'
Group by Weeknum)
b,
(SELECT Weeknum, Count (*) AS [Severity 3]
FROM [BE].[dbo].[BES$]
Where Severity = '3'
And Team = 'AS400'
Group by Weeknum)
c,
(SELECT Weeknum, Count (*) AS [Severity 2]
FROM [BE].[dbo].[BES$]
Where Severity = '2'
And Team = 'AS400'
Group by Weeknum)
d,
(SELECT Weeknum, Count (*) AS [Severity 1]
FROM [BE].[dbo].[BES$]
Where Severity = '1'
And Team = 'AS400'
Group by Weeknum)
e
Where a.[Weeknum]= b.[Weeknum]
and a.[Weeknum]= c.[Weeknum]
and a.[Weeknum]= d.[Weeknum]
and a.[Weeknum]= e.[Weeknum]

Order by weeknum

The result

32-2006 88 29 2 1
37-2006 77 30 6 1

See the result if I change the last column to be the same as column 4 - (to help demonstrate the problem)

31-2006 65 17 5 5
32-2006 88 29 2 2
33-2006 84 27 3 3
34-2006 66 19 6 6
35-2006 90 19 4 4
36-2006 86 17 3 3
37-2006 77 30 6 6
38-2006 89 26 9 9
39-2006 96 20 3 3

The desired result

31-2006 65 17 5 0
32-2006 88 29 2 1
33-2006 84 27 3 0
34-2006 66 19 6 0
35-2006 90 19 4 0
36-2006 86 17 3 0
37-2006 77 30 6 1
38-2006 89 26 9 0
39-2006 96 20 3 0

[galexyus]

You have to use LEFT JOIN instead of WHERE:

Select a.[Weeknum], b.[No Severity], c.[Severity 3], d.[Severity 2], e.[Severity 1]
From (SELECT Weeknum
FROM [BES$]
Group by Weeknum)
A
LEFT JOIN
(SELECT Weeknum, Count (*) AS [No Severity]
FROM [BES$]
Where Severity = 'No Severity'
And Team = 'AS400'
Group by Weeknum)
b ON a.Weeknum = b.Weeknum
LEFT JOIN
(SELECT Weeknum, Count (*) AS [Severity 3]
FROM [BES$]
Where Severity = '3'
And Team = 'AS400'
Group by Weeknum)
c ON a.Weeknum = c.Weeknum
LEFT JOIN
(SELECT Weeknum, Count (*) AS [Severity 2]
FROM [BES$]
Where Severity = '2'
And Team = 'AS400'
Group by Weeknum)
d  ON a.Weeknum = d.Weeknum
LEFT JOIN
(SELECT Weeknum, Count (*) AS [Severity 1]
FROM [BES$]
Where Severity = '1'
And Team = 'AS400'
Group by Weeknum)
e ON  a.Weeknum = e.Weeknum
Order by a.weeknum

This will produce the desired result showing NULL. If you want zeros instead of NULL, replace the first line with:

Select a.[Weeknum], ISNULL(b.[No Severity], 0) AS [No Severity], ISNULL(c.[Severity 3], 0) AS [Severity 3], ISNULL(d.[Severity 2], 0) AS [Severity 2], ISNULL(e.[Severity 1], 0) AS [Severity 1]

[bhanab]

Thank you so much - Galexyus. It works 100%