Link to home
Create AccountLog in
Avatar of BobRosas
BobRosas

asked on

Combine 2 Where clauses in stored procedure

I have a report in Report Services and I've created a Stored Procedure in SQL Server.
I need to show
   "New Client" Count for a parameter date range
   "Avg Wage" for a parameter date range
    "YTD" Count for Clients  for the current year.

Avg Wage          New Clients                YTD
      $9.03                  3                               7

This code (in part) gives me the correct "YTD" count
WHERE       (DATEPART( year, tblPlacement.PlacementDate) BETWEEN DATEPART(year, @From) AND DATEPART(Year, @To))
                GROUP BY tblClients.OriginalStartDate, tblClients.ClientFileNo, tblGoals.PlacementGoal, tblPlacement.PlacementDate,tblPlacement.PlacementWageAmt

This one gives me the correct "Avg Wage"
WHERE    tblPlacement.PlacementDate BETWEEN (@From) AND (@To)
                GROUP BY tblClients.OriginalStartDate, tblClients.ClientFileNo, tblGoals.PlacementGoal, tblPlacement.PlacementDate,tblPlacement.PlacementWageAmt

Is there a way to combine the two?  I tried a UNION but that throws off my count because the 2nd WHERE is already included in the YTD WHERE.
ASKER CERTIFIED SOLUTION
Avatar of Harish Varghese
Harish Varghese
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of BobRosas
BobRosas

ASKER

That is exactly what I needed!  Thank you so much for your help!
Perfect!  Thanks!