x

Date Filtering

I have a query and for some reason my date is not letting the records pass thorugh to the results.

Below is the snippet where i do get successful results

SELECT  CUST_ACCT_ID, CNTRCT_LEAD_ID, BEG_DT, END_DT 
FROM REFERENCE.DBO.T_DCON_CONT_ELIG_HIST 
WHERE CUST_ACCT_ID IN ('419162')
AND CNTRCT_LEAD_ID IN ('124740', '838470', '208709')

--RESULTS CUST_ACCT_ID CNTRCT_LEAD_ID BEG_DT END_DT 419162 124740 2010-12-14 00:00:00.000 2011-01-31 00:00:00.000 419162 208709 2010-12-14 00:00:00.000 2011-01-31 00:00:00.000

Now fast forward when i try to use in a sproc with other tables this result is not there...

declare @BEG_DT DATETIME
set @BEG_DT  = '2010-12-01'

 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,
 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
    AND CONVERT(CHAR(10),@BEG_DT,110) BETWEEN E.BEG_DT AND E.END_DT
    LEFT JOIN REFERENCE.dbo.T_DCON_CONT_ELIG_HIST ostp
    ON ostp.CUST_ACCT_ID = r.CUST_ACCT_ID
    AND  ostp.CNTRCT_LEAD_ID IN ('124740', '838470', '208709')
    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'

    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'

    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'

    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
ORDER BY 1
more ▼

asked Jan 31 '11 at 02:29 PM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Jan 31 '11 at 03:10 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 31 '11 at 03:31 PM

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x111
x5

asked: Jan 31 '11 at 02:29 PM

Seen: 586 times

Last Updated: Jan 31 '11 at 02:52 PM