Date Math
-
Hello, I need help with a stored proc. If anyone can help that would be excellent.
Here is my Code
create proc rpt_client_gcsaa_new_a_sm_chp_mem_sc
@startmonth datetime
as
SELECT mb_membership.mbr_asn_code,
Convert(varchar(10),mb_membership.mbr_renew_date, 101) as [Start Date],
Convert(varchar(10),mb_membership.mbr_expire_date, 101) as [End Date],
Convert(varchar(10),mb_membership.mbr_terminate_date, 101) as [Termination Date],
mb_member_type.mbt_code,
co_customer.cst_ind_full_name_dn,
co_customer.cst_id,
co_customer_x_customer.cxc_rlt_code,
convert(varchar(10), co_customer_x_customer.cxc_start_date, 101) as [cxcSDate],
convert(varchar(10),co_customer_x_customer.cxc_end_date, 101) as [cxcEDate],
co_chapter.chp_name,
vw_client_gcsaa_cst_preferredaddress_lar.FullStreetAddress,
vw_client_gcsaa_cst_preferredaddress_lar.adr_city,
vw_client_gcsaa_cst_preferredaddress_lar.adr_state,
vw_client_gcsaa_cst_preferredaddress_lar.adr_post_code,
co_customer.cst_phn_number_complete_dn,
co_customer.cst_fax_number_complete_dn,
co_customer.cst_org_name_dn
FROM mb_membership INNER JOIN
mb_member_type ON mb_membership.mbr_mbt_key = mb_member_type.mbt_key INNER JOIN
co_customer ON mb_membership.mbr_cst_key = co_customer.cst_key INNER JOIN
co_customer_x_customer ON co_customer.cst_key = co_customer_x_customer.cxc_cst_key_1 INNER JOIN
co_chapter ON co_customer_x_customer.cxc_cst_key_2 = co_chapter.chp_cst_key INNER JOIN
vw_client_gcsaa_cst_preferredaddress_lar ON co_customer.cst_key =
vw_client_gcsaa_cst_preferredaddress_lar.cst_key
WHERE (mb_member_type.mbt_code = N'a') OR (mb_member_type.mbt_code = N'sm') AND
(mb_membership.mbr_renew_date >= MONTH(@startmonth) AND mb_membership.mbr_renew_date >=
YEAR(@startmonth))
and (mb_membership.mbr_terminate_date IS NULL) AND (co_customer_x_customer.cxc_end_date IS NULL)
What I am trying to accomplish is find a date that is whatever the use enters and then pass logic that will see if that date is between the start of that month and year and the end of that month/Year for instance i want to see all the people that have a mb_membership.mbr_renew_date of 7/1/2007 and 7/31/2007.
The results I am getting from the above sp are giving me 2006 dates not 2007 dates.
Any assistance would be greatly appreciated.
-
To clarify, if @startmonth = 2007-07-15, you want all records where mb_membership.mbr_renew_date >= 2007-07-1 and mb_membership.mbr_renew_date < 2007-08-01?
-
Try this :
where mb_membership.mbr_renew_date >= dateadd
(mm,datediff(mm,0,@startmonth),0)
and mb_membership.mbr_renew_date < dateadd(mm,datediff(mm,0,@startmonth)+1,0)
-
Tried it I am still just pulling the 2006 dates. any thoughts
-
Would it make sense to build a (static) comparison table with the first date of each month in one column, and the last date of each month in another column (plus identifier column?), and then find where the appropriate dates are?
The end result would be that you could test for the appropriate row, and then correlate that row to determine whether it's time to renew, right? If you are testing a date for falling between startdate and enddate, isn't that fairly basic?
It's Friday afternoon for me - a dangerous time to produce code. However, if you'd rather see how I create the 'current' month's start and end dates for the reporting month (i.e., last month), I can forward that/post it here - whichever is your pleasure.
Have a great weekend!
-
How about the DDL for the table(s) and some sample data?
-
Stephen,
Looking at your search arguments, I find that you are going to get two types of return records:
(mb_member_type.mbt_code = N'a')
OR
(mb_member_type.mbt_code = N'sm') AND (mb_membership.mbr_renew_date >= MONTH(@startmonth) AND mb_membership.mbr_renew_date >= YEAR(@startmonth)) and (mb_membership.mbr_terminate_date IS NULL) AND (co_customer_x_customer.cxc_end_date IS NULL)
I am guessing that you are more likely trying to get the following:
(mb_member_type.mbt_code = N'a') AND (mb_membership.mbr_renew_date >= MONTH(@startmonth) AND mb_membership.mbr_renew_date >= YEAR(@startmonth)) and (mb_membership.mbr_terminate_date IS NULL) AND (co_customer_x_customer.cxc_end_date IS NULL)
OR
(mb_member_type.mbt_code = N'sm') AND (mb_membership.mbr_renew_date >= MONTH(@startmonth) AND mb_membership.mbr_renew_date >= YEAR(@startmonth)) and (mb_membership.mbr_terminate_date IS NULL) AND (co_customer_x_customer.cxc_end_date IS NULL)
If that is the case, group the OR clause with parentheses, like so:
WHERE ((mb_member_type.mbt_code = N'a') OR (mb_member_type.mbt_code = N'sm')) AND
(mb_membership.mbr_renew_date >= MONTH(@startmonth) AND mb_membership.mbr_renew_date >=
YEAR(@startmonth)) and (mb_membership.mbr_terminate_date IS NULL) AND (co_customer_x_customer.cxc_end_date IS NULL)
You can also remove the parentheses around the date arguments as they affect no change in the argument. I changed those to red.
This problem is probably why you were still getting incorrect results when using Lynn's suggestion above. I think you will find that if you make the changes to the parentheses in addition to using Lynn's code, you will get the results that you want.
You may want to test doing the date calculations before the query using variables. Some times the optimizer chooses poor indexes when it has calculations in the search arguments.
Good Luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply