Trouble with Having clause - SQL Server Forums

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

Database Design and Application Architecture

Trouble with Having clause

Author

Topic

meanmyrlin
Starting Member

15 Posts

Posted - 2008-06-06 : 11:52:23

I am currently working with the following query:

SELECT w.styleId, w.modelyear, w.vehdesc,

ISNULL(CONVERT(varchar,wp.algfmv-isnull(ROUND(vp.value,0),0)),'') AS previousPeriodBeforeAlgAnalystFactor,

CONVERT(varchar,ISNULL(ROUND(vp.value,0),'')) as previousPeriodAlgAnalystFactor,

CONVERT(varchar,ISNULL(wp.algFmv,'')) AS previousPeriodAlgFmvAfterAnalystFactor,

CONVERT(varchar,ISNULL(vpaf.value,'')) As previousPeriodIntAnalystFactor,

CONVERT(varchar,ISNULL(wp.intFinalFmv,'')) AS previousPeriodFinalIntFmv,

CONVERT(varchar,ISNULL(w.algFmv,'')) AS currentPeriodAlgFmv,

'' AS currentPeriodAlgAnalystFactor

FROM RPM_CHR.dbo.Workspace w

LEFT OUTER JOIN RPM_CHR.dbo.Workspace wp ON wp.periodId = w.periodId - 1 AND wp.styleId = w.styleId

LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vp ON vp.periodId = wp.periodId AND vp.styleId = wp.styleId AND vp.valtype=45

LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vpaf ON vpaf.periodId = wp.periodId AND vpaf.styleId = wp.styleId AND vpaf.valtype = 20

WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)

order by w.vehdesc, w.modelyear desc

This works well but instead of having to say "w.periodid=194" I would like w.periodid = max(w.perioidid). When I try to accomplish this using a having clause by replacing

"WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)

order by w.vehdesc, w.modelyear desc
"

with

"WHERE (wp.algfmv > 0 or w.algfmv>0)

group by w.periodid, w.styleId, w.modelyear, w.vehdesc, w.algFmv,wp.intFinalFmv,vpaf.value,wp.algFmv,
vp.value

Having max(w.periodid)= w.periodid

order by w.vehdesc, w.modelyear desc"

it returns all the records in my table. Could anyone suggest a better way of doing this, or point out where I am erring?

Any help would be greatly appreciated.

I think I should add, the whole point of doing this is that there is a new perioidid each week and I want to only look at records with the current periodid.

Thanks,
Tasha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-08 : 06:06:53

[code]SELECT w.styleId, w.modelyear, w.vehdesc,

ISNULL(CONVERT(varchar,wp.algfmv-isnull(ROUND(vp.value,0),0)),'') AS previousPeriodBeforeAlgAnalystFactor,

CONVERT(varchar,ISNULL(ROUND(vp.value,0),'')) as previousPeriodAlgAnalystFactor,

CONVERT(varchar,ISNULL(wp.algFmv,'')) AS previousPeriodAlgFmvAfterAnalystFactor,

CONVERT(varchar,ISNULL(vpaf.value,'')) As previousPeriodIntAnalystFactor,

CONVERT(varchar,ISNULL(wp.intFinalFmv,'')) AS previousPeriodFinalIntFmv,

CONVERT(varchar,ISNULL(w.algFmv,'')) AS currentPeriodAlgFmv,

'' AS currentPeriodAlgAnalystFactor

FROM RPM_CHR.dbo.Workspace w
INNER JOIN (SELECT max(periodid) AS maxperiodid FROM RPM_CHR.dbo.Workspace) w1
ON w1.maxperiodid=w.periodid

LEFT OUTER JOIN RPM_CHR.dbo.Workspace wp ON wp.periodId = w.periodId - 1 AND wp.styleId = w.styleId

LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vp ON vp.periodId = wp.periodId AND vp.styleId = wp.styleId AND vp.valtype=45

LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vpaf ON vpaf.periodId = wp.periodId AND vpaf.styleId = wp.styleId AND vpaf.valtype = 20

WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)

order by w.vehdesc, w.modelyear desc[/code]

meanmyrlin
Starting Member

15 Posts

Posted - 2008-06-10 : 14:14:14

Thank you. This was exactly the type of guidance I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources