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
Ru1995Asked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
Cleaned versions:


 
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

Open in new window




 
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)

Open in new window

0
 
BartVxCommented:
try using DISTINCT :

...
FROM (SELECT DISTINCT...)

0
 
BartVxCommented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Ru1995Author Commented:
I tried using distinct and it didn't work
0
 
BartVxCommented:
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.
0
 
Ru1995Author Commented:
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
0
 
BartVxCommented:
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.

0
 
Ru1995Author Commented:
I hate to ask but can you show me what your referring too I'm kinda new to SQL
0
 
gdemariaCommented:
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

0
 
Ru1995Author Commented:
I got this error

Msg 156, Level 15, State 1, Line 55
Incorrect syntax near the keyword 'outer'.
0
 
gdemariaCommented:
oops... should be left

From Org o
 left join responses r on r.orgID = o.orgID
0
 
BartVxCommented:
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

0
 
gdemariaCommented:
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

0
 
Ru1995Author Commented:
well got rid of the duplicates but it counts everything on to the 42nd week of the year and removes anything before that
0
 
Ru1995Author Commented:
I was referring to BartV solution gdmaria on your solution i get an invalid column name "organization" error
0
 
gdemariaCommented:
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)
0
 
gdemariaCommented:
> 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"

0
 
BartVxConnect With a Mentor Commented:
How about this one?
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

Open in new window

0
 
Ru1995Author Commented:
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
0
 
Ru1995Author Commented:
But it does seem like we are getting closer
0
 
BartVxCommented:
Ru, what do you mean by "push to the right"?
0
 
Ru1995Author Commented:
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
0
 
gdemariaCommented:
... and my two queries...   the one with the simple join and no nested subqueries
 and the other with just a few lines of code?
0
 
BartVxCommented:
Sorry, i misunderstood the problem then. I thought the problem was to limit those who took the survey multiple times to just one time. :)

0
 
Ru1995Author Commented:
Actually gdemaria I didn't see your but it worked I'll assign points to everyone. Thanks so much for all your help
0
All Courses

From novice to tech pro — start learning today.