...

FROM (SELECT DISTINCT...)

Solved

Posted on 2011-10-13

I'm creating a report using the following sql. The purpose of the report is to display which department has taken the survey for any week of any given year. The problem is that if a department takes the survey more than once in a current year it shows up as multiple records instead of just 1 record. Can anyone help me with this?

BTW - I'm using SQL Server 2000 as a backend and Coldfusion MX 7

SELECT organization, responsedate,

CASE WHEN weekNumber = 1 THEN surveyCount ELSE 0 END AS P01,

CASE WHEN weekNumber = 2 THEN surveyCount ELSE 0 END AS P02,

CASE WHEN weekNumber = 3 THEN surveyCount ELSE 0 END AS P03,

CASE WHEN weekNumber = 4 THEN surveyCount ELSE 0 END AS P04,

CASE WHEN weekNumber = 5 THEN surveyCount ELSE 0 END AS P05,

CASE WHEN weekNumber = 6 THEN surveyCount ELSE 0 END AS P06,

CASE WHEN weekNumber = 7 THEN surveyCount ELSE 0 END AS P07,

CASE WHEN weekNumber = 8 THEN surveyCount ELSE 0 END AS P08,

CASE WHEN weekNumber = 9 THEN surveyCount ELSE 0 END AS P09,

CASE WHEN weekNumber = 10 THEN surveyCount ELSE 0 END AS P10,

CASE WHEN weekNumber = 11 THEN surveyCount ELSE 0 END AS P11,

CASE WHEN weekNumber = 12 THEN surveyCount ELSE 0 END AS P12,

CASE WHEN weekNumber = 13 THEN surveyCount ELSE 0 END AS P13,

CASE WHEN weekNumber = 14 THEN surveyCount ELSE 0 END AS P14,

CASE WHEN weekNumber = 15 THEN surveyCount ELSE 0 END AS P15,

CASE WHEN weekNumber = 16 THEN surveyCount ELSE 0 END AS P16,

CASE WHEN weekNumber = 17 THEN surveyCount ELSE 0 END AS P17,

CASE WHEN weekNumber = 18 THEN surveyCount ELSE 0 END AS P18,

CASE WHEN weekNumber = 19 THEN surveyCount ELSE 0 END AS P19,

CASE WHEN weekNumber = 20 THEN surveyCount ELSE 0 END AS P20,

CASE WHEN weekNumber = 21 THEN surveyCount ELSE 0 END AS P21,

CASE WHEN weekNumber = 22 THEN surveyCount ELSE 0 END AS P22,

CASE WHEN weekNumber = 23 THEN surveyCount ELSE 0 END AS P23,

CASE WHEN weekNumber = 24 THEN surveyCount ELSE 0 END AS P24,

CASE WHEN weekNumber = 25 THEN surveyCount ELSE 0 END AS P25,

CASE WHEN weekNumber = 26 THEN surveyCount ELSE 0 END AS P26,

CASE WHEN weekNumber = 27 THEN surveyCount ELSE 0 END AS P27,

CASE WHEN weekNumber = 28 THEN surveyCount ELSE 0 END AS P28,

CASE WHEN weekNumber = 29 THEN surveyCount ELSE 0 END AS P29,

CASE WHEN weekNumber = 30 THEN surveyCount ELSE 0 END AS P30,

CASE WHEN weekNumber = 31 THEN surveyCount ELSE 0 END AS P31,

CASE WHEN weekNumber = 32 THEN surveyCount ELSE 0 END AS P32,

CASE WHEN weekNumber = 33 THEN surveyCount ELSE 0 END AS P33,

CASE WHEN weekNumber = 34 THEN surveyCount ELSE 0 END AS P34,

CASE WHEN weekNumber = 35 THEN surveyCount ELSE 0 END AS P35,

CASE WHEN weekNumber = 36 THEN surveyCount ELSE 0 END AS P36,

CASE WHEN weekNumber = 37 THEN surveyCount ELSE 0 END AS P37,

CASE WHEN weekNumber = 38 THEN surveyCount ELSE 0 END AS P38,

CASE WHEN weekNumber = 39 THEN surveyCount ELSE 0 END AS P39,

CASE WHEN weekNumber = 40 THEN surveyCount ELSE 0 END AS P40,

CASE WHEN weekNumber = 41 THEN surveyCount ELSE 0 END AS P41,

CASE WHEN weekNumber = 42 THEN surveyCount ELSE 0 END AS P42,

CASE WHEN weekNumber = 43 THEN surveyCount ELSE 0 END AS P43,

CASE WHEN weekNumber = 44 THEN surveyCount ELSE 0 END AS P44,

CASE WHEN weekNumber = 45 THEN surveyCount ELSE 0 END AS P45,

CASE WHEN weekNumber = 46 THEN surveyCount ELSE 0 END AS P46,

CASE WHEN weekNumber = 47 THEN surveyCount ELSE 0 END AS P47,

CASE WHEN weekNumber = 48 THEN surveyCount ELSE 0 END AS P48,

CASE WHEN weekNumber = 49 THEN surveyCount ELSE 0 END AS P49,

CASE WHEN weekNumber = 50 THEN surveyCount ELSE 0 END AS P50,

CASE WHEN weekNumber = 51 THEN surveyCount ELSE 0 END AS P51,

CASE WHEN weekNumber = 52 THEN surveyCount ELSE 0 END AS P52

FROM (

SELECT o.orgname as organization, o.Display as Display, datePart(ww, r.ResponseDate) AS WeekNumber, COUNT(r.orgid) AS SurveyCount, convert(varchar,r.responseDate,110) as responseDate

FROM responses r Right Outer Join org o on r.orgID = o.orgID AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#">

GROUP BY o.orgname, datePart(ww, r.ResponseDate), Display, responseDate

) r

where Display = 'Y'

ORDER BY organization

BTW - I'm using SQL Server 2000 as a backend and Coldfusion MX 7

SELECT organization, responsedate,

CASE WHEN weekNumber = 1 THEN surveyCount ELSE 0 END AS P01,

CASE WHEN weekNumber = 2 THEN surveyCount ELSE 0 END AS P02,

CASE WHEN weekNumber = 3 THEN surveyCount ELSE 0 END AS P03,

CASE WHEN weekNumber = 4 THEN surveyCount ELSE 0 END AS P04,

CASE WHEN weekNumber = 5 THEN surveyCount ELSE 0 END AS P05,

CASE WHEN weekNumber = 6 THEN surveyCount ELSE 0 END AS P06,

CASE WHEN weekNumber = 7 THEN surveyCount ELSE 0 END AS P07,

CASE WHEN weekNumber = 8 THEN surveyCount ELSE 0 END AS P08,

CASE WHEN weekNumber = 9 THEN surveyCount ELSE 0 END AS P09,

CASE WHEN weekNumber = 10 THEN surveyCount ELSE 0 END AS P10,

CASE WHEN weekNumber = 11 THEN surveyCount ELSE 0 END AS P11,

CASE WHEN weekNumber = 12 THEN surveyCount ELSE 0 END AS P12,

CASE WHEN weekNumber = 13 THEN surveyCount ELSE 0 END AS P13,

CASE WHEN weekNumber = 14 THEN surveyCount ELSE 0 END AS P14,

CASE WHEN weekNumber = 15 THEN surveyCount ELSE 0 END AS P15,

CASE WHEN weekNumber = 16 THEN surveyCount ELSE 0 END AS P16,

CASE WHEN weekNumber = 17 THEN surveyCount ELSE 0 END AS P17,

CASE WHEN weekNumber = 18 THEN surveyCount ELSE 0 END AS P18,

CASE WHEN weekNumber = 19 THEN surveyCount ELSE 0 END AS P19,

CASE WHEN weekNumber = 20 THEN surveyCount ELSE 0 END AS P20,

CASE WHEN weekNumber = 21 THEN surveyCount ELSE 0 END AS P21,

CASE WHEN weekNumber = 22 THEN surveyCount ELSE 0 END AS P22,

CASE WHEN weekNumber = 23 THEN surveyCount ELSE 0 END AS P23,

CASE WHEN weekNumber = 24 THEN surveyCount ELSE 0 END AS P24,

CASE WHEN weekNumber = 25 THEN surveyCount ELSE 0 END AS P25,

CASE WHEN weekNumber = 26 THEN surveyCount ELSE 0 END AS P26,

CASE WHEN weekNumber = 27 THEN surveyCount ELSE 0 END AS P27,

CASE WHEN weekNumber = 28 THEN surveyCount ELSE 0 END AS P28,

CASE WHEN weekNumber = 29 THEN surveyCount ELSE 0 END AS P29,

CASE WHEN weekNumber = 30 THEN surveyCount ELSE 0 END AS P30,

CASE WHEN weekNumber = 31 THEN surveyCount ELSE 0 END AS P31,

CASE WHEN weekNumber = 32 THEN surveyCount ELSE 0 END AS P32,

CASE WHEN weekNumber = 33 THEN surveyCount ELSE 0 END AS P33,

CASE WHEN weekNumber = 34 THEN surveyCount ELSE 0 END AS P34,

CASE WHEN weekNumber = 35 THEN surveyCount ELSE 0 END AS P35,

CASE WHEN weekNumber = 36 THEN surveyCount ELSE 0 END AS P36,

CASE WHEN weekNumber = 37 THEN surveyCount ELSE 0 END AS P37,

CASE WHEN weekNumber = 38 THEN surveyCount ELSE 0 END AS P38,

CASE WHEN weekNumber = 39 THEN surveyCount ELSE 0 END AS P39,

CASE WHEN weekNumber = 40 THEN surveyCount ELSE 0 END AS P40,

CASE WHEN weekNumber = 41 THEN surveyCount ELSE 0 END AS P41,

CASE WHEN weekNumber = 42 THEN surveyCount ELSE 0 END AS P42,

CASE WHEN weekNumber = 43 THEN surveyCount ELSE 0 END AS P43,

CASE WHEN weekNumber = 44 THEN surveyCount ELSE 0 END AS P44,

CASE WHEN weekNumber = 45 THEN surveyCount ELSE 0 END AS P45,

CASE WHEN weekNumber = 46 THEN surveyCount ELSE 0 END AS P46,

CASE WHEN weekNumber = 47 THEN surveyCount ELSE 0 END AS P47,

CASE WHEN weekNumber = 48 THEN surveyCount ELSE 0 END AS P48,

CASE WHEN weekNumber = 49 THEN surveyCount ELSE 0 END AS P49,

CASE WHEN weekNumber = 50 THEN surveyCount ELSE 0 END AS P50,

CASE WHEN weekNumber = 51 THEN surveyCount ELSE 0 END AS P51,

CASE WHEN weekNumber = 52 THEN surveyCount ELSE 0 END AS P52

FROM (

SELECT o.orgname as organization, o.Display as Display, datePart(ww, r.ResponseDate) AS WeekNumber, COUNT(r.orgid) AS SurveyCount, convert(varchar,r.response

FROM responses r Right Outer Join org o on r.orgID = o.orgID AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar"

GROUP BY o.orgname, datePart(ww, r.ResponseDate), Display, responseDate

) r

where Display = 'Y'

ORDER BY organization

25 Comments

If this is indeed the problem, it can be solved by not selecting the response date, but selecting the MIN or MAX responsedate of those records in question.

Try selecting MAX or MIN response date in your inner select, both in the part for the weekpart and in the responsedate itself.

```
SELECT organization,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 1 THEN surveyCount ELSE 0 END) AS P01,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 2 THEN surveyCount ELSE 0 END) AS P02,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 3 THEN surveyCount ELSE 0 END) AS P03,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 4 THEN surveyCount ELSE 0 END) AS P04,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 5 THEN surveyCount ELSE 0 END) AS P05,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 6 THEN surveyCount ELSE 0 END) AS P06,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 7 THEN surveyCount ELSE 0 END) AS P07,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 8 THEN surveyCount ELSE 0 END) AS P08,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 9 THEN surveyCount ELSE 0 END) AS P09,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 10 THEN surveyCount ELSE 0 END) AS P10,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 11 THEN surveyCount ELSE 0 END) AS P11,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 12 THEN surveyCount ELSE 0 END) AS P12,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 13 THEN surveyCount ELSE 0 END) AS P13,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 14 THEN surveyCount ELSE 0 END) AS P14,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 15 THEN surveyCount ELSE 0 END) AS P15,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 16 THEN surveyCount ELSE 0 END) AS P16,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 17 THEN surveyCount ELSE 0 END) AS P17,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 18 THEN surveyCount ELSE 0 END) AS P18,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 19 THEN surveyCount ELSE 0 END) AS P19,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 20 THEN surveyCount ELSE 0 END) AS P20,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 21 THEN surveyCount ELSE 0 END) AS P21,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 22 THEN surveyCount ELSE 0 END) AS P22,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 23 THEN surveyCount ELSE 0 END) AS P23,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 24 THEN surveyCount ELSE 0 END) AS P24,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 25 THEN surveyCount ELSE 0 END) AS P25,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 26 THEN surveyCount ELSE 0 END) AS P26,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 27 THEN surveyCount ELSE 0 END) AS P27,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 28 THEN surveyCount ELSE 0 END) AS P28,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 29 THEN surveyCount ELSE 0 END) AS P29,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 30 THEN surveyCount ELSE 0 END) AS P30,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 31 THEN surveyCount ELSE 0 END) AS P31,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 32 THEN surveyCount ELSE 0 END) AS P32,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 33 THEN surveyCount ELSE 0 END) AS P33,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 34 THEN surveyCount ELSE 0 END) AS P34,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 35 THEN surveyCount ELSE 0 END) AS P35,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 36 THEN surveyCount ELSE 0 END) AS P36,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 37 THEN surveyCount ELSE 0 END) AS P37,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 38 THEN surveyCount ELSE 0 END) AS P38,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 39 THEN surveyCount ELSE 0 END) AS P39,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 40 THEN surveyCount ELSE 0 END) AS P40,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 41 THEN surveyCount ELSE 0 END) AS P41,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 42 THEN surveyCount ELSE 0 END) AS P42,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 43 THEN surveyCount ELSE 0 END) AS P43,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 44 THEN surveyCount ELSE 0 END) AS P44,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 45 THEN surveyCount ELSE 0 END) AS P45,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 46 THEN surveyCount ELSE 0 END) AS P46,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 47 THEN surveyCount ELSE 0 END) AS P47,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 48 THEN surveyCount ELSE 0 END) AS P48,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 49 THEN surveyCount ELSE 0 END) AS P49,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 50 THEN surveyCount ELSE 0 END) AS P50,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 51 THEN surveyCount ELSE 0 END) AS P51,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 52 THEN surveyCount ELSE 0 END) AS P52
From Org o
outer join responses r on r.orgID = o.orgID
AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#">
group by o.organization
order by o.organization
```

```
SELECT organization, responsedate,
CASE WHEN weekNumber = 1 THEN surveyCount ELSE 0 END AS P01,
CASE WHEN weekNumber = 2 THEN surveyCount ELSE 0 END AS P02,
CASE WHEN weekNumber = 3 THEN surveyCount ELSE 0 END AS P03,
CASE WHEN weekNumber = 4 THEN surveyCount ELSE 0 END AS P04,
CASE WHEN weekNumber = 5 THEN surveyCount ELSE 0 END AS P05,
CASE WHEN weekNumber = 6 THEN surveyCount ELSE 0 END AS P06,
CASE WHEN weekNumber = 7 THEN surveyCount ELSE 0 END AS P07,
CASE WHEN weekNumber = 8 THEN surveyCount ELSE 0 END AS P08,
CASE WHEN weekNumber = 9 THEN surveyCount ELSE 0 END AS P09,
CASE WHEN weekNumber = 10 THEN surveyCount ELSE 0 END AS P10,
CASE WHEN weekNumber = 11 THEN surveyCount ELSE 0 END AS P11,
CASE WHEN weekNumber = 12 THEN surveyCount ELSE 0 END AS P12,
CASE WHEN weekNumber = 13 THEN surveyCount ELSE 0 END AS P13,
CASE WHEN weekNumber = 14 THEN surveyCount ELSE 0 END AS P14,
CASE WHEN weekNumber = 15 THEN surveyCount ELSE 0 END AS P15,
CASE WHEN weekNumber = 16 THEN surveyCount ELSE 0 END AS P16,
CASE WHEN weekNumber = 17 THEN surveyCount ELSE 0 END AS P17,
CASE WHEN weekNumber = 18 THEN surveyCount ELSE 0 END AS P18,
CASE WHEN weekNumber = 19 THEN surveyCount ELSE 0 END AS P19,
CASE WHEN weekNumber = 20 THEN surveyCount ELSE 0 END AS P20,
CASE WHEN weekNumber = 21 THEN surveyCount ELSE 0 END AS P21,
CASE WHEN weekNumber = 22 THEN surveyCount ELSE 0 END AS P22,
CASE WHEN weekNumber = 23 THEN surveyCount ELSE 0 END AS P23,
CASE WHEN weekNumber = 24 THEN surveyCount ELSE 0 END AS P24,
CASE WHEN weekNumber = 25 THEN surveyCount ELSE 0 END AS P25,
CASE WHEN weekNumber = 26 THEN surveyCount ELSE 0 END AS P26,
CASE WHEN weekNumber = 27 THEN surveyCount ELSE 0 END AS P27,
CASE WHEN weekNumber = 28 THEN surveyCount ELSE 0 END AS P28,
CASE WHEN weekNumber = 29 THEN surveyCount ELSE 0 END AS P29,
CASE WHEN weekNumber = 30 THEN surveyCount ELSE 0 END AS P30,
CASE WHEN weekNumber = 31 THEN surveyCount ELSE 0 END AS P31,
CASE WHEN weekNumber = 32 THEN surveyCount ELSE 0 END AS P32,
CASE WHEN weekNumber = 33 THEN surveyCount ELSE 0 END AS P33,
CASE WHEN weekNumber = 34 THEN surveyCount ELSE 0 END AS P34,
CASE WHEN weekNumber = 35 THEN surveyCount ELSE 0 END AS P35,
CASE WHEN weekNumber = 36 THEN surveyCount ELSE 0 END AS P36,
CASE WHEN weekNumber = 37 THEN surveyCount ELSE 0 END AS P37,
CASE WHEN weekNumber = 38 THEN surveyCount ELSE 0 END AS P38,
CASE WHEN weekNumber = 39 THEN surveyCount ELSE 0 END AS P39,
CASE WHEN weekNumber = 40 THEN surveyCount ELSE 0 END AS P40,
CASE WHEN weekNumber = 41 THEN surveyCount ELSE 0 END AS P41,
CASE WHEN weekNumber = 42 THEN surveyCount ELSE 0 END AS P42,
CASE WHEN weekNumber = 43 THEN surveyCount ELSE 0 END AS P43,
CASE WHEN weekNumber = 44 THEN surveyCount ELSE 0 END AS P44,
CASE WHEN weekNumber = 45 THEN surveyCount ELSE 0 END AS P45,
CASE WHEN weekNumber = 46 THEN surveyCount ELSE 0 END AS P46,
CASE WHEN weekNumber = 47 THEN surveyCount ELSE 0 END AS P47,
CASE WHEN weekNumber = 48 THEN surveyCount ELSE 0 END AS P48,
CASE WHEN weekNumber = 49 THEN surveyCount ELSE 0 END AS P49,
CASE WHEN weekNumber = 50 THEN surveyCount ELSE 0 END AS P50,
CASE WHEN weekNumber = 51 THEN surveyCount ELSE 0 END AS P51,
CASE WHEN weekNumber = 52 THEN surveyCount ELSE 0 END AS P52
FROM (
SELECT o.orgname as organization, datePart(ww, MAX(r.ResponseDate)) AS WeekNumber, COUNT(r.orgid) AS SurveyCount, convert(varchar, MAX(r.responseDate),110) as responseDate
FROM responses r Right Outer Join org o on r.orgID = o.orgID AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#">
where o.Display = 'Y'
GROUP BY o.orgname
) r
ORDER BY organization
```

```
SELECT organization,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 1 THEN 1 ELSE 0 END) AS P01,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 2 THEN 1 ELSE 0 END) AS P02,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 3 THEN 1 ELSE 0 END) AS P03,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 4 THEN 1 ELSE 0 END) AS P04,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 5 THEN 1 ELSE 0 END) AS P05,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 6 THEN 1 ELSE 0 END) AS P06,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 7 THEN 1 ELSE 0 END) AS P07,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 8 THEN 1 ELSE 0 END) AS P08,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 9 THEN 1 ELSE 0 END) AS P09,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 10 THEN 1 ELSE 0 END) AS P10,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 11 THEN 1 ELSE 0 END) AS P11,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 12 THEN 1 ELSE 0 END) AS P12,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 13 THEN 1 ELSE 0 END) AS P13,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 14 THEN 1 ELSE 0 END) AS P14,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 15 THEN 1 ELSE 0 END) AS P15,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 16 THEN 1 ELSE 0 END) AS P16,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 17 THEN 1 ELSE 0 END) AS P17,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 18 THEN 1 ELSE 0 END) AS P18,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 19 THEN 1 ELSE 0 END) AS P19,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 20 THEN 1 ELSE 0 END) AS P20,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 21 THEN 1 ELSE 0 END) AS P21,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 22 THEN 1 ELSE 0 END) AS P22,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 23 THEN 1 ELSE 0 END) AS P23,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 24 THEN 1 ELSE 0 END) AS P24,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 25 THEN 1 ELSE 0 END) AS P25,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 26 THEN 1 ELSE 0 END) AS P26,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 27 THEN 1 ELSE 0 END) AS P27,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 28 THEN 1 ELSE 0 END) AS P28,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 29 THEN 1 ELSE 0 END) AS P29,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 30 THEN 1 ELSE 0 END) AS P30,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 31 THEN 1 ELSE 0 END) AS P31,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 32 THEN 1 ELSE 0 END) AS P32,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 33 THEN 1 ELSE 0 END) AS P33,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 34 THEN 1 ELSE 0 END) AS P34,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 35 THEN 1 ELSE 0 END) AS P35,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 36 THEN 1 ELSE 0 END) AS P36,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 37 THEN 1 ELSE 0 END) AS P37,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 38 THEN 1 ELSE 0 END) AS P38,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 39 THEN 1 ELSE 0 END) AS P39,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 40 THEN 1 ELSE 0 END) AS P40,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 41 THEN 1 ELSE 0 END) AS P41,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 42 THEN 1 ELSE 0 END) AS P42,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 43 THEN 1 ELSE 0 END) AS P43,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 44 THEN 1 ELSE 0 END) AS P44,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 45 THEN 1 ELSE 0 END) AS P45,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 46 THEN 1 ELSE 0 END) AS P46,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 47 THEN 1 ELSE 0 END) AS P47,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 48 THEN 1 ELSE 0 END) AS P48,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 49 THEN 1 ELSE 0 END) AS P49,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 50 THEN 1 ELSE 0 END) AS P50,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 51 THEN 1 ELSE 0 END) AS P51,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 52 THEN 1 ELSE 0 END) AS P52
From Org o
outer join responses r on r.orgID = o.orgID
AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#">
group by o.organization
order by o.organization
```

SELECT o.organization

, datePart(ww, r.ResponseDate) as weekNumber

, sum(r.orgID) as responses

From Org o

left join responses r on r.orgID = o.orgID

and year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar"

group by o.organization, datePart(ww, r.ResponseDate)

order by o.organization, datePart(ww, r.ResponseDate)

Ru, really? ... if your column name is not organization then change it to the correct name...

looks like the column name should be "orgname"

```
SELECT orgname,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 1 THEN 1 ELSE 0 END) AS P01,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 2 THEN 1 ELSE 0 END) AS P02,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 3 THEN 1 ELSE 0 END) AS P03,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 4 THEN 1 ELSE 0 END) AS P04,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 5 THEN 1 ELSE 0 END) AS P05,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 6 THEN 1 ELSE 0 END) AS P06,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 7 THEN 1 ELSE 0 END) AS P07,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 8 THEN 1 ELSE 0 END) AS P08,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 9 THEN 1 ELSE 0 END) AS P09,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 10 THEN 1 ELSE 0 END) AS P10,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 11 THEN 1 ELSE 0 END) AS P11,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 12 THEN 1 ELSE 0 END) AS P12,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 13 THEN 1 ELSE 0 END) AS P13,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 14 THEN 1 ELSE 0 END) AS P14,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 15 THEN 1 ELSE 0 END) AS P15,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 16 THEN 1 ELSE 0 END) AS P16,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 17 THEN 1 ELSE 0 END) AS P17,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 18 THEN 1 ELSE 0 END) AS P18,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 19 THEN 1 ELSE 0 END) AS P19,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 20 THEN 1 ELSE 0 END) AS P20,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 21 THEN 1 ELSE 0 END) AS P21,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 22 THEN 1 ELSE 0 END) AS P22,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 23 THEN 1 ELSE 0 END) AS P23,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 24 THEN 1 ELSE 0 END) AS P24,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 25 THEN 1 ELSE 0 END) AS P25,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 26 THEN 1 ELSE 0 END) AS P26,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 27 THEN 1 ELSE 0 END) AS P27,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 28 THEN 1 ELSE 0 END) AS P28,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 29 THEN 1 ELSE 0 END) AS P29,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 30 THEN 1 ELSE 0 END) AS P30,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 31 THEN 1 ELSE 0 END) AS P31,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 32 THEN 1 ELSE 0 END) AS P32,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 33 THEN 1 ELSE 0 END) AS P33,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 34 THEN 1 ELSE 0 END) AS P34,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 35 THEN 1 ELSE 0 END) AS P35,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 36 THEN 1 ELSE 0 END) AS P36,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 37 THEN 1 ELSE 0 END) AS P37,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 38 THEN 1 ELSE 0 END) AS P38,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 39 THEN 1 ELSE 0 END) AS P39,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 40 THEN 1 ELSE 0 END) AS P40,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 41 THEN 1 ELSE 0 END) AS P41,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 42 THEN 1 ELSE 0 END) AS P42,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 43 THEN 1 ELSE 0 END) AS P43,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 44 THEN 1 ELSE 0 END) AS P44,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 45 THEN 1 ELSE 0 END) AS P45,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 46 THEN 1 ELSE 0 END) AS P46,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 47 THEN 1 ELSE 0 END) AS P47,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 48 THEN 1 ELSE 0 END) AS P48,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 49 THEN 1 ELSE 0 END) AS P49,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 50 THEN 1 ELSE 0 END) AS P50,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 51 THEN 1 ELSE 0 END) AS P51,
sum(CASE WHEN datePart(ww, r.ResponseDate) = 52 THEN 1 ELSE 0 END) AS P52
From Org o
left join responses r on r.orgID = o.orgID
AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#">
group by o.orgname
order by o.orgname
```

```
SELECT o.orgname
, datePart(ww, r.ResponseDate) as weekNumber
, sum(r.orgID) as responses
From Org o
left join responses r on r.orgID = o.orgID
and year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#">
group by o.orgname, datePart(ww, r.ResponseDate)
order by o.orgname, datePart(ww, r.ResponseDate)
```

```
SELECT organization, responsedate,
CASE WHEN weekNumber = 1 THEN surveyCount ELSE 0 END AS P01,
CASE WHEN weekNumber = 2 THEN surveyCount ELSE 0 END AS P02,
CASE WHEN weekNumber = 3 THEN surveyCount ELSE 0 END AS P03,
CASE WHEN weekNumber = 4 THEN surveyCount ELSE 0 END AS P04,
CASE WHEN weekNumber = 5 THEN surveyCount ELSE 0 END AS P05,
CASE WHEN weekNumber = 6 THEN surveyCount ELSE 0 END AS P06,
CASE WHEN weekNumber = 7 THEN surveyCount ELSE 0 END AS P07,
CASE WHEN weekNumber = 8 THEN surveyCount ELSE 0 END AS P08,
CASE WHEN weekNumber = 9 THEN surveyCount ELSE 0 END AS P09,
CASE WHEN weekNumber = 10 THEN surveyCount ELSE 0 END AS P10,
CASE WHEN weekNumber = 11 THEN surveyCount ELSE 0 END AS P11,
CASE WHEN weekNumber = 12 THEN surveyCount ELSE 0 END AS P12,
CASE WHEN weekNumber = 13 THEN surveyCount ELSE 0 END AS P13,
CASE WHEN weekNumber = 14 THEN surveyCount ELSE 0 END AS P14,
CASE WHEN weekNumber = 15 THEN surveyCount ELSE 0 END AS P15,
CASE WHEN weekNumber = 16 THEN surveyCount ELSE 0 END AS P16,
CASE WHEN weekNumber = 17 THEN surveyCount ELSE 0 END AS P17,
CASE WHEN weekNumber = 18 THEN surveyCount ELSE 0 END AS P18,
CASE WHEN weekNumber = 19 THEN surveyCount ELSE 0 END AS P19,
CASE WHEN weekNumber = 20 THEN surveyCount ELSE 0 END AS P20,
CASE WHEN weekNumber = 21 THEN surveyCount ELSE 0 END AS P21,
CASE WHEN weekNumber = 22 THEN surveyCount ELSE 0 END AS P22,
CASE WHEN weekNumber = 23 THEN surveyCount ELSE 0 END AS P23,
CASE WHEN weekNumber = 24 THEN surveyCount ELSE 0 END AS P24,
CASE WHEN weekNumber = 25 THEN surveyCount ELSE 0 END AS P25,
CASE WHEN weekNumber = 26 THEN surveyCount ELSE 0 END AS P26,
CASE WHEN weekNumber = 27 THEN surveyCount ELSE 0 END AS P27,
CASE WHEN weekNumber = 28 THEN surveyCount ELSE 0 END AS P28,
CASE WHEN weekNumber = 29 THEN surveyCount ELSE 0 END AS P29,
CASE WHEN weekNumber = 30 THEN surveyCount ELSE 0 END AS P30,
CASE WHEN weekNumber = 31 THEN surveyCount ELSE 0 END AS P31,
CASE WHEN weekNumber = 32 THEN surveyCount ELSE 0 END AS P32,
CASE WHEN weekNumber = 33 THEN surveyCount ELSE 0 END AS P33,
CASE WHEN weekNumber = 34 THEN surveyCount ELSE 0 END AS P34,
CASE WHEN weekNumber = 35 THEN surveyCount ELSE 0 END AS P35,
CASE WHEN weekNumber = 36 THEN surveyCount ELSE 0 END AS P36,
CASE WHEN weekNumber = 37 THEN surveyCount ELSE 0 END AS P37,
CASE WHEN weekNumber = 38 THEN surveyCount ELSE 0 END AS P38,
CASE WHEN weekNumber = 39 THEN surveyCount ELSE 0 END AS P39,
CASE WHEN weekNumber = 40 THEN surveyCount ELSE 0 END AS P40,
CASE WHEN weekNumber = 41 THEN surveyCount ELSE 0 END AS P41,
CASE WHEN weekNumber = 42 THEN surveyCount ELSE 0 END AS P42,
CASE WHEN weekNumber = 43 THEN surveyCount ELSE 0 END AS P43,
CASE WHEN weekNumber = 44 THEN surveyCount ELSE 0 END AS P44,
CASE WHEN weekNumber = 45 THEN surveyCount ELSE 0 END AS P45,
CASE WHEN weekNumber = 46 THEN surveyCount ELSE 0 END AS P46,
CASE WHEN weekNumber = 47 THEN surveyCount ELSE 0 END AS P47,
CASE WHEN weekNumber = 48 THEN surveyCount ELSE 0 END AS P48,
CASE WHEN weekNumber = 49 THEN surveyCount ELSE 0 END AS P49,
CASE WHEN weekNumber = 50 THEN surveyCount ELSE 0 END AS P50,
CASE WHEN weekNumber = 51 THEN surveyCount ELSE 0 END AS P51,
CASE WHEN weekNumber = 52 THEN surveyCount ELSE 0 END AS P52
FROM (
SELECT o.orgname as organization
, datePart(ww, r.ResponseDate) AS WeekNumber
, COUNT(r.orgid) AS SurveyCount
, convert(varchar,r.responseDate,110) as responseDate
FROM responses r
Right Outer Join org o
on r.orgID = o.orgID
where o.Display = 'Y'
AND r.ResponseDate = (SELECT MAX(ResponseDate)
FROM responses r2
where r2.orgID = o.orgID
AND year(r.ResponseDate) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#"> )
GROUP BY o.orgname, datePart(ww, r.ResponseDate), responseDate
) r
ORDER BY organization
```

AND r.ResponseDate = (SELECT MAX(ResponseDate)

If I change it to MIN(responseDate) it pushes all the values to the left based on the earliest reponseDate

and the other with just a few lines of code?

Title | # Comments | Views | Activity |
---|---|---|---|

How to select/populate table with most current records | 2 | 50 | |

(SQL)How to remove leading zeros | 4 | 26 | |

sql sproc | 2 | 23 | |

Memory Leak in Windows 2012, Non-Paged pool 8.5GB | 25 | 17 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**14** Experts available now in Live!