CODEWITHSUNDEEP
mysql
gary
gary

Reputation: 319

mysql where clause spanning multiple years?

I have mysql query that outputs years leads v sales and calculates the conversions rates which worked great in 2012 as it only held a years data. Now its 2013 I need to change how it works but have become stuck. If a lead came in December 2012, then became a sale in 2013 its is ommitted from my query output as it only asks for CaseDate of 2013.

Select
  q.*,
  ROUND(100 * q.Comms / q.Total, 2) As Conversion,
  If(q.Adviser Is Null, 1, 0) As remove
From
  (Select
      a.ContactFullName As Adviser,
      Sum(Year(b.CaseDate) = 2013 And Month(b.CaseDate) = 1) As Jan,
      Sum(Year(b.CaseDate) = 2013 And Month(b.CaseDate) = 2) As Feb,
      Sum(Year(b.CaseDate) = 2013 And Month(b.CaseDate) = 3) As Mar,
      ..... Dec
      Sum(Case
        When Year(b.CaseDate) = 2013 And Month(b.CaseDate) Between '1'
        And '12' Then 1 Else 0 End) As Total,
      Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(b.StatusSubmittedDate) = ".(int)$_POST['year']." And Month(b.StatusSubmittedDate) Between ".(int)$_POST['start']." And ".(int)$_POST['end']." Then 1 Else 0 End) As Comms
    From
      tblcontacts a Inner Join
      tblcases b On a.ContactID = b.ContactAssignedTo
    Group By
      a.ContactFullName With Rollup) q

This works;

Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(b.StatusSubmittedDate) = 2013 And Month(b.CaseDate) Between '1' And '12'  Then 1 Else 0 End) As Comms

This donly shows leads entered & submitted in 2013;

Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(b.StatusSubmittedDate) = ".(int)$_POST['year']." And Month(b.CaseDate) Between ".(int)$_POST['start']." And ".(int)$_POST['end']." Then 1 Else 0 End) As Comms

Many Thanks

Upvotes: 0

Views: 62

Answers (1)

EvilBob22
EvilBob22

Reputation: 732

It depends on your business logic. If the logic says you only show leads that came in the same year, then your code is perfectly correct as-is (as-was?). If you want to include 2012 or 2013 (which it looks like you do), you need to spell it out completely, the server doesn't understand "2012 or 2013"

Sum(Case When (Year(b.CaseDate) = 2012 Or Year(b.CaseDate) = 2013) And Year(....

Notice the parenthesis around the Or statement, this is to make sure that CaseYear=2012 or CaseYear=2013 and SubmitYear=2013 is treated as (CaseYear=2012 or CaseYear=2013) and SubmitYear=2013 instead of CaseYear=2012 or (CaseYear=2013 and SubmitYear=2013)

Of course if the business logic does not care about the CaseYear, then take that out of the Sum.

Upvotes: 1

Related Questions