question

**EDIT** I forgot to ask the perhaps most relevant question as a response: What do you get when you ommit the ** BETWEEN AND ** part of the query? **END EDIT** Why do you cast your datetime-variable to a char(10)? If you want it as a char(10), why not make it a char(10) to begin with? Are the **E.BEG\_DT** and **E.END\_DT** columns **datetime** columns or **char(10)** columns? If they are char() columns, what's their format? If you want YYYY-MM-DD you should pass **120** as the last parameter to **convert** function. Before the query, after assigning a value to @BEG_DT do: PRINT @BEG_DT That's to check that you don't run into problems with your date-input, due to local/regional settings. The format **YYYYMMDD hh:mm:ss.nnn** (eg **20101201 00:00:00.000**) always works if you want to assign a string value to a datetime-column, it's independent of regional settings.

Values passed in by SSRS should be in date in order to use the calandering.... I'm making progress but the effective date was 12-14-2010 and if i use a date range that encompasses the whole month - i do not understand why it is not being picked up. result should has a '01' position also currently it displays null declare @EFF_DT DATETIME set @EFF_DT = '2010-12-01' declare @END_DT DATETIME set @END_DT = '2010-12-31' -- SELECT DISTINCT r.CUST_ACCT_ID AS ACCT_NUM, r.PRI_ACCT_IND AS PRIMARY_ACCOUNT, r.CUST_NUM AS PRI_ACCT_NUM, r.CUST_ACCT_NAM, r.ASAP as ASAP_Enrolled, r.OS_ACCT, MAX(CASE WHEN (rtl.CNTRCT_LEAD_ID) IS NULL THEN 'N' ELSE 'Y' END) AS RTL_ACCT, MAX(CASE WHEN (ostp.CNTRCT_LEAD_ID) IS NULL THEN 'N' ELSE 'Y' END) AS ONESTOP_LEAD, (ostp.PRTY_CONT) AS OS_PSTN, MAX(CASE WHEN (carve.CNTRCT_LEAD_ID) IS NULL THEN 'N' ELSE 'Y' END) AS CARVOUT_LEAD, (carve.PRTY_CONT) as CARV_POS, MAX(CASE WHEN (lvnx.CNTRCT_LEAD_ID) IS NULL THEN 'N' ELSE 'Y' END) AS LVNX_LEAD, (lvnx.PRTY_CONT) AS LOV_POS FROM REFERENCE.dbo.T_DCON_CONT_ELIG_HIST e LEFT JOIN REFERENCE.dbo.t_IW_CUST_ACCT r ON e.CUST_ACCT_ID = r.CUST_ACCT_ID LEFT JOIN REFERENCE.dbo.T_DCON_CONT_ELIG_HIST ostp ON ostp.CUST_ACCT_ID = E.CUST_ACCT_ID AND ostp.CNTRCT_LEAD_ID IN ('124740', '838470', '208709') AND CONVERT(CHAR(10),@EFF_DT,110) > ostp.BEG_DT AND CONVERT(CHAR(10),@END_DT,110)< ostp.END_DT LEFT JOIN REFERENCE.dbo.T_DCON_CONT_ELIG_HIST lvnx ON lvnx.CUST_ACCT_ID = r.CUST_ACCT_ID AND lvnx.cntrct_lead_id = '208709' AND CONVERT(CHAR(10),@EFF_DT,110) > lvnx.BEG_DT AND CONVERT(CHAR(10),@END_DT,110)< lvnx.END_DT LEFT JOIN REFERENCE.dbo.T_DCON_CONT_ELIG_HIST rtl ON rtl.CUST_ACCT_ID = r.CUST_ACCT_ID AND rtl.cntrct_lead_id = '838470' AND CONVERT(CHAR(10),@EFF_DT,110) > rtl.BEG_DT AND CONVERT(CHAR(10),@END_DT,110)< rtl.END_DT LEFT JOIN REFERENCE.dbo.T_DCON_CONT_ELIG_HIST carve ON carve.CUST_ACCT_ID = r.CUST_ACCT_ID AND carve.cntrct_lead_id = '124740' AND CONVERT(CHAR(10),@EFF_DT,110) > carve.BEG_DT AND CONVERT(CHAR(10),@END_DT,110)< carve.END_DT LEFT JOIN REFERENCE.dbo.T_CNTRC_INFO info ON e.CNTRCT_LEAD_ID = info.CNTRC_LEAD_ID WHERE r.CUST_CHN_ID = 512 GROUP BY r.CUST_ACCT_ID , r.PRI_ACCT_IND , r.CUST_NUM , r.CUST_ACCT_NAM, r.ASAP, r.OS_ACCT, OSTP.PRTY_CONT, lvnx.PRTY_CONT, carve.PRTY_CONT ORDER BY 1