Date Range and Less Than

[BaSk]

I'm trying to run a date range query which works, but I also need to get data outside of the date range inside of the same query which I get to work. The issue I'm having is when the start date is in say 2011 and the end date is 2012. I need to get some information that is in 2010 and 2009. Is there a way to do this?

Below here is the query I'm working on,

SELECT * FROM tbl_customers
INNER JOIN tbl_plans ON tbl_plans.ProductID = tbl_customers.ProductID
WHERE Status = "Active" AND State = "UT" AND (DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "1") OR ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "12"))
ORDER BY DateSignup asc;

I'm having a hard time trying to explain this, so if you need some information please let me know.

Thanks for your time
-BaSk

[smantscheff]

It is not clear what your problem is. Submit some test data and the results you would like to have.
I only noticed that the in the where clause
(DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "1") OR ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "12")
you are duplicating the DateSignup conditon which might lead to erroneous coding. I'd rather code: ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1') AND (PaymentCycle = "1" OR PaymentCycle = "12"))

[BaSk]

Thanks for the reply.

What I'm trying to do is get all the Policy's that have a Payment cycle of 12 between the date range, but also of all previous years.

So if my date range is 2010-11-1 through 2011-1-31 then it would get all Policy's with a Payment Cycle of 1 between the dates and then get all of the Policy's with a Payment Cycle of 12 between the dates. Then what I need it to do is get all of the Policy's with a Payment Cycle of 12 between the date range but for all of the previous years, so 2009, 2008, 2007.

I hope this helps if you still need more info please let me know.

Thanks
-BaSk

[smantscheff]

Test data would really help.
If I understand you correct, the WHERE condition would change to:
((DateSignup BETWEEN '2010-11-1' AND '2010-12-1') AND (PaymentCycle = "1" OR PaymentCycle = "12"))
OR
((DateSignup < '2010-01-01') AND (PaymentCycle = "12"))

[BaSk]

I'm sorry I can supply test data, as there are customer info stored just as phone numbers and emails, I don't want to give that out nor do I want to go through and change the info.

The above where clause that you posted wont work, because the second statement

DateSignup < '2010-01-01'

This will get everything less than that date, with a PaymentCycle of 12. I need it to stay within the date range;

1) Get all Policy's with Payment Cycle of 1 or 12 Within the date range ( Can Do )
2) Get all Policy's within the date range with an Payment Cycle of 12 BUT in all previous years.

I thought about doing,

(Year(DateSignup) < (Year(Startdate) - INTERVAL 1 YEAR) AND (Year(EndDate) - INERVAL 1 YEAR))

But the above would only work for less than 1 year. So if the current year was 2010 it would only get data from 2009, but i would need it to get data from 2009, 2008, 2007.

Hope this helps
-BaSk

[BaSk]

I'm finally getting this to work somewhat. I can pull data from the pervious years if the date range is between 2010-11-01 and 2010-11-31 but if it is the first of another month it will not show the result. ( 2010-11-01 and 2010-12-01 ). Also if my date range is 2010-11-01 through 2010-12-15 it wont show anything after the 15th of November.

Is there a fix for this? Below is my query.

SELECT PolicyNumber, DateSignup, PaymentCycle FROM tbl_commissions
WHERE AssociateID = "WAJJ1010" AND AssociateType = "Manager" AND (DateSignup BETWEEN '2010-11-01' AND '2010-12-01' OR PaymentCycle = "1" AND DateSignup <= '2010-12-01') OR (PaymentCycle = "12" AND (Year(DateSignup) < Year('2010-11-01') AND Year(DateSignup) < Year('2010-12-01') AND Month(DateSignup) >= Month('2010-11-01') AND Month(DateSignup) <= Month('2010-12-1') AND Day(DateSignup) BETWEEN DAY('2010-11-01') AND DAY('2010-12-01') ))
ORDER BY DateSignup asc;

[smantscheff]

If you are not able to prepare some test data - which do not have to be your actual customer's data - you should go back to square 1 and try again.
Apart from that your WHERE clause is quite unreadable. I doubt that even you fully understand what you are doing there.
Bracket your AND and OR clause to avoid logical errors.
Your WHERE clause
(DateSignup BETWEEN '2010-11-01' AND '2010-12-01' OR PaymentCycle = "1" AND DateSignup <= '2010-12-01')
OR
(PaymentCycle = "12" AND
(YEAR(DateSignup) < YEAR('2010-11-01')
AND YEAR(DateSignup) < YEAR('2010-12-01')
AND MONTH(DateSignup) >= MONTH('2010-11-01')
AND MONTH(DateSignup) <= MONTH('2010-12-1')
AND DAY(DateSignup) BETWEEN DAY('2010-11-01')
AND DAY('2010-12-01')
))

can be recoded as ( (DateSignup BETWEEN '2010-11-01' AND '2010-12-01')
OR
(PaymentCycle = "1" AND DateSignup <= '2010-12-01')
)

OR
(PaymentCycle = "12" AND
(YEAR(DateSignup) < 2010
AND YEAR(DateSignup) < 2010
AND MONTH(DateSignup) >= 11
AND MONTH(DateSignup) <= 12
AND DAY(DateSignup) BETWEEN 1 AND 1
))
which is obviously nonsense.

So prepare some test data and we'll see how we can help with that.

[BaSk]

Solved this.

Heres the code.

SELECT PolicyNumber, DateSignup, PaymentCycle, AssociateID FROM tbl_commissions


WHERE AssociateID = "WAJJ1010" AND AssociateType = "Manager" AND 


(YEAR(DateSignup) BETWEEN 2000 and 2009 AND DATE_FORMAT(DateSignup, "%m-%d") BETWEEN "11-1" AND "12-2"


AND  PaymentCycle = "12" 

OR

(DateSignup BETWEEN '2010-11-1' AND '2010-12-2' OR  PaymentCycle = "1" AND DateSignup <= '2010-11-1'))

ORDER BY DateSignup asc;