SELECT DISTINCT 
                      TOP (100) PERCENT dbo.vIntervals.WorDescription, ISNULL(SUM(dbo.vIntervals.d14), N'') AS d14, ISNULL(SUM(dbo.vIntervals.d29), N'') AS d29, 
                      ISNULL(SUM(dbo.vIntervals.d44), N'') AS d44, ISNULL(SUM(dbo.vIntervals.d59), N'') AS d59, ISNULL(SUM(dbo.vIntervals.d74), N'') AS d74, 
                      ISNULL(SUM(dbo.vIntervals.d89), N'') AS d89, ISNULL(SUM(dbo.vIntervals.d90), N'') AS d90
FROM         dbo.vIntervals RIGHT OUTER JOIN
                      dbo.vIntervals AS vIntervals_1 ON dbo.vIntervals.CaseWorkerID = vIntervals_1.CaseWorkerID AND 
                      dbo.vIntervals.WorDescription = vIntervals_1.WorDescription
WHERE     (vIntervals_1.OpenDate BETWEEN CONVERT(DATETIME, '2008-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
GROUP BY dbo.vIntervals.WorDescription, ISNULL(dbo.vIntervals.d14, N''), ISNULL(dbo.vIntervals.d29, N''), ISNULL(dbo.vIntervals.d44, N''), 
                      ISNULL(dbo.vIntervals.d59, N''), ISNULL(dbo.vIntervals.d74, N''), ISNULL(dbo.vIntervals.d89, N''), ISNULL(dbo.vIntervals.d90, N'')
ORDER BY dbo.vIntervals.WorDescription

quote:

---

Originally posted by Sep410

Hi,

Here is the query that I have :

Select dbo.vIntervals.WorDescription,sum(d14) as d14,Sum(d29)as d29,Sum(d44) as d44,sum(d59)as d59,sum(d74)as d74,sum(d89) as d89,sum(d90) as d90
from

(SELECT DISTINCT 
                      TOP (100) PERCENT dbo.vIntervals.WorDescription, ISNULL(SUM(dbo.vIntervals.d14), N'') AS d14, ISNULL(SUM(dbo.vIntervals.d29), N'') AS d29, 
                      ISNULL(SUM(dbo.vIntervals.d44), N'') AS d44, ISNULL(SUM(dbo.vIntervals.d59), N'') AS d59, ISNULL(SUM(dbo.vIntervals.d74), N'') AS d74, 
                      ISNULL(SUM(dbo.vIntervals.d89), N'') AS d89, ISNULL(SUM(dbo.vIntervals.d90), N'') AS d90
FROM         dbo.vIntervals RIGHT OUTER JOIN
                      dbo.vIntervals AS vIntervals_1 ON dbo.vIntervals.CaseWorkerID = vIntervals_1.CaseWorkerID AND 
                      dbo.vIntervals.WorDescription = vIntervals_1.WorDescription
WHERE     (vIntervals_1.OpenDate BETWEEN CONVERT(DATETIME, '2008-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
GROUP BY dbo.vIntervals.WorDescription, ISNULL(dbo.vIntervals.d14, N''), ISNULL(dbo.vIntervals.d29, N''), ISNULL(dbo.vIntervals.d44, N''), 
                      ISNULL(dbo.vIntervals.d59, N''), ISNULL(dbo.vIntervals.d74, N''), ISNULL(dbo.vIntervals.d89, N''), ISNULL(dbo.vIntervals.d90, N'')
ORDER BY dbo.vIntervals.WorDescription
)t 
group by dbo.vIntervals.WorDescription

And here is it outcome:
Alison 0 0 0 0 0 0 2
Alison 0 0 0 0 1 0 0
John 0 0 0 4 0 0 0
John 0 0 1 1 0 0 0
John 0 0 2 0 0 0 0
John 0 0 10 0 0 0 0
John 0 1 0 0 0 1 0
Mark 0 16 0 0 0 0 0
Mark 1 0 0 0 0 0 1
Mark 1 0 0 1 0 0 0

I need to have

Alison 0 0 0 0 1 0 2

John 0 1 13 4 5 0 1

Mark 2 16 0 1 0 0 1

What should I do?


Sep

---

quote:

---

Originally posted by visakh16

i think you need only dbo.vIntervals.WorDescription in GROUP BY. why are you including other fields on group by and again applying sum() over them. remove them from group by and try.

---

quote:

---

Originally posted by Sep410

quote:

---

Originally posted by visakh16

i think you need only dbo.vIntervals.WorDescription in GROUP BY. why are you including other fields on group by and again applying sum() over them. remove them from group by and try.

---

As you see in mark's data I need to have sum for all columns of information.

Sep

---

quote:

---

Originally posted by Sep410

Thanks visakh16,You were right. It is working well When I removed them from group by.

Sep

---

SELECT 
 	vi.WorDescription
	,Sum(isnull(vi.d14,0)) as d14
	,Sum(isnull(vi.d29,0)) as d29
	,Sum(isnull(vi.d44,0)) as d44
	,Sum(isnull(vi.d59,0)) as d59
	,Sum(isnull(vi.d74,0)) as d74
	,Sum(isnull(vi.d89,0)) as d89
	,Sum(isnull(vi.d90,0)) as d90
FROM         
	dbo.vIntervals vi
WHERE     
vi.OpenDate BETWEEN '2008-01-01' AND '2009-01-01'
GROUP BY 
	vi.WorDescription
ORDER BY vi.WorDescription

SELECT 
 	aa.WorDescription
	,Sum(isnull(vi.d14,0)) as d14
	,Sum(isnull(vi.d29,0)) as d29
	,Sum(isnull(vi.d44,0)) as d44
	,Sum(isnull(vi.d59,0)) as d59
	,Sum(isnull(vi.d74,0)) as d74
	,Sum(isnull(vi.d89,0)) as d89
	,Sum(isnull(vi.d90,0)) as d90
FROM         
(	
	Select distinct a.WorDescription
	from
	dbo.vIntervals a
) aa 
Left Join
	dbo.vIntervals vi
on aa.WorDescription = vi.WorDescription
WHERE     
vi.OpenDate BETWEEN '2008-01-01' AND '2009-01-01'
GROUP BY 
	vi.WorDescription
ORDER BY vi.WorDescription