Case when is causing duplicates!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case when is causing duplicates!

Author

Topic

Lucy101
Starting Member

2 Posts

Posted - 2010-12-13 : 03:39:45

Hi guys,

I'm self taught in SQL and have completely hit a wall, please help!

I'm trying to use the 'Case when' function to flag any students that have arrived late as they have a particular condition entered against their record (in the dfcond table). The problem is that they also have many other conditions so I'm getting duplicates that I dont want! Here is my query:

SELECT df.dfkey, df.status, cq.cqtitle, dfpath.course, dfpath.weeks, df.ELICOS_rate, student_start,
student_end, sem_start, sem_end, dfpath.weeks*ELICOS_Rate AS Total_Inv_Due,
(Select SUM(tramt) from dff
where df.dfkey=dff.code
and trperiod between dfpath.sem_start and dfpath.sem_end
and trtype='I') AS Total_Inv_To_Date,
Case when enr_cond ='LA1' then 'Late X 1'
when enr_cond ='LA2' then 'Late X 2'
when enr_cond ='LA3' then 'Late X 3'
when enr_cond ='LA4' then 'Late X 4'
when enr_cond ='LA5' then 'Late X 5' else '' end as 'Late condition'
FROM df join dfpath on df.dfkey=dfpath.skey
JOIN dfcond on df.dfkey=dfcond.skey
JOIN dff on dfpath.skey=dff.code
JOIN kc on dfpath.course=kc.course
JOIN cq on df.scagent=cq.cqkey
WHERE dfpath.student_start>=[From start date]
AND dfpath.student_start<=[To start date]
GROUP BY df.dfkey, df.status, cq.cqtitle, dfpath.course, dfpath.weeks, df.ELICOS_rate, student_start,
student_end, sem_start, sem_end, enr_cond
order by df.dfkey

Does anyone know how I can prevent the duplicates? I can't specify that they must have a condition of 'LA' as there are records that didnt arrive late that I need also.

Any help is much appreciated!!

Lucy

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-13 : 04:00:43

It's more a requirements definition than a coding issue.
What do you want for a student if they have several enr_cond's?

maybe take enr_cond out of the group by clause and

Late_x_1 = max(Case when enr_cond ='LA1' then 'yes' else no end ,
Late_x_2 = max(Case when enr_cond ='LA2' then 'yes' else no end ,
Late_x_3 = max(Case when enr_cond ='LA3' then 'yes' else no end ,
Late_x_4 = max(Case when enr_cond ='LA4' then 'yes' else no end ,
Late_x_5 = max(Case when enr_cond ='LA5' then 'yes' else no end

or maybe
[Late condition]
max(Case when enr_cond ='LA1' then 'Late X 1'
when enr_cond ='LA2' then 'Late X 2'
when enr_cond ='LA3' then 'Late X 3'
when enr_cond ='LA4' then 'Late X 4'
when enr_cond ='LA5' then 'Late X 5' else '' end)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lucy101
Starting Member

2 Posts

Posted - 2010-12-14 : 03:32:22

Thanks for your help!

I've actually solved it by using SUM(CASE) instead as I then didn't have to put enr_cond in the group by clause. I couldn't use max as there were other enr_cond entries that weren't 'LA something'. These were the ones causing the problem!

SELECT df.dfkey, df.status, cq.cqtitle, dfpath.course, dfpath.weeks, df.ELICOS_rate, student_start,
student_end, sem_start, sem_end, dfpath.weeks*ELICOS_Rate AS Total_Inv_Due,
(Select SUM(tramt) from dff
WHERE df.dfkey=dff.code
AND trperiod between dfpath.sem_start and dfpath.sem_end
AND trtype='I'
AND left (code,1) <>'#'
AND dff.trdet not like '%books%'
AND dff.trdet not like '%OSHC%'
AND dff.trdet not like '%Material%') AS Total_Inv_To_Date,
(SELECT SUM(CASE WHEN (ENR_COND is null) THEN 0
WHEN (ENR_COND = 'LA1') THEN 1
WHEN (ENR_COND = 'LA2') THEN 2
WHEN (ENR_COND = 'LA3') THEN 3
WHEN (ENR_COND = 'LA4') THEN 4 ELSE 0 END)
FROM dfcond where df.dfkey=dfcond.skey)AS Weeks_late
FROM df join dfpath on df.dfkey=dfpath.skey
JOIN dff on dfpath.skey=dff.code
JOIN kc on dfpath.course=kc.course
JOIN cq on df.scagent=cq.cqkey
WHERE dfpath.student_start>=[From start date]
AND dfpath.student_start<=[To start date]
GROUP BY df.dfkey, df.status, cq.cqtitle, dfpath.course, dfpath.weeks, df.ELICOS_rate, student_start,
student_end, sem_start, sem_end
order by df.dfkey

Thanks so much for your help anyway!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources