asked on

Report showing multiple lines when pulling 1 record

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

BartVx

try using DISTINCT :

...
FROM (SELECT DISTINCT...)

BartVx

as a side note: as far as I can see , you can move the 'Where display=..' to the inner query and remove Display from the select (and group by) list for some performance gain.

Ru1995

ASKER

I tried using distinct and it didn't work

BartVx

I think i see the problem, if a survey is taken twice in the same week, the weekpart will be identical, but the responsedate itself will be different, so the 'double' row will not be eliminated by the DISTINCT.

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.

Ru1995

ASKER

Well they aren't allowed to take the survey twice in the same week and when duplicate organizations are being displayed they took it in different weeks

BartVx

ok, the reasoning stays the same then.

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

Ru1995

ASKER

I hate to ask but can you show me what your referring too I'm kinda new to SQL

gdemaria

Here's one approach..

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

Open in new window

Ru1995

ASKER

I got this error

Msg 156, Level 15, State 1, Line 55
Incorrect syntax near the keyword 'outer'.

gdemaria

oops... should be left

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

BartVx

I don't have any SQL available atm, so not sure if it's entirely correct, but something along the lines of:

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

Open in new window

gdemaria

oops again - I forgot to remove the surveyCount. Assuming each record of "responses" corresponds to 1 and only 1 respond, then I just put "1" in the THEN clause of each case statement...

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

Open in new window

Ru1995

ASKER

well got rid of the duplicates but it counts everything on to the 42nd week of the year and removes anything before that

Ru1995

ASKER

I was referring to BartV solution gdmaria on your solution i get an invalid column name "organization" error

gdemaria

My prefence would be to do it this way... I wouldn't want to deal with 52 seperate columns..

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" value="#arguments.date#">
group by o.organization, datePart(ww, r.ResponseDate)
order by o.organization, datePart(ww, r.ResponseDate)

gdemaria

> I was referring to BartV solution gdmaria on your solution i get an invalid column name "organization" error

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

looks like the column name should be "orgname"

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

BartVx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ru1995

ASKER

Well the problem with bartvx solution that it is pushing all the values to the right because of

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

Ru1995

ASKER

But it does seem like we are getting closer

BartVx

Ru, what do you mean by "push to the right"?

Ru1995

ASKER

It takes the max date that the user took the survey and puts it into the weeknumber So if someone took the survey this week it marks a 1 under P42. However if that same person took the survey in 2 weeks ago it doesn't mark a 1 under P40 like its supposed to. The opposite happens if I replace Max with Min

gdemaria

... and my two queries... the one with the simple join and no nested subqueries
and the other with just a few lines of code?

BartVx

Sorry, i misunderstood the problem then. I thought the problem was to limit those who took the survey multiple times to just one time. :)

Ru1995

ASKER

Actually gdemaria I didn't see your but it worked I'll assign points to everyone. Thanks so much for all your help