Conditional if within a Select Where statement?

Update #tempResourceMetrics
Set RequestsStartPeriod = (Select Count(Distinct ProjectID) From #tempResourceAllocation
Where #tempResourceAllocation.ParentDepartmentID = #tempResourceMetrics.ProjectDivisionID
And (Month(#tempResourceAllocation.StartDate) = Month(GETDATE()) - 1)
And #tempResourceAllocation.ProjectStatusID In (1, 2, 3, 4)
And #tempResourceAllocation.ProjectCategoryID = 1333)

In the second condition I'm using Month() to ensure that the totals I get for this column are calculated from the entries created in the preceeding month. The problem appears in January when the preceeding month becomes 12 as opposed to 1(what my code would think) and also the year changes.

How can I modify my select or update statements so that this logic would be included correctly?

Try:

> And (Month(#tempResourceAllocation.StartDate) = Month(GETDATE()) - 1)

And (

#tempResourceAllocation.StartDate >= convert(varchar(6) , dateadd(month, -1, getdate()), 112) + '01'

and

#tempResourceAllocation.StartDate < convert(varchar(6) , getdate()) + '01'

)

AMB

|||

This should work also, AND has the advantage that it will use any indexing on StartDate:

Code Snippet

AND ( #tempResourceAllocation.StartDate >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND #tempResourceAllocation.StartDate < dateadd( month, datediff( month, 0, getdate() ), 0 )

)

|||
Thanks for your help guys! I'll try out both solutions.