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, 2011 at 02:29 PM in Default

avatar image

siera_gld
1k 82 88 93

(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, 2011 at 03:10 PM

avatar image

Magnus Ahlkvist
21.5k 19 39 42

(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, 2011 at 03:31 PM

avatar image

siera_gld
1k 82 88 93

(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.

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:

x153
x5

asked: Jan 31, 2011 at 02:29 PM

Seen: 908 times

Last Updated: Jan 31, 2011 at 02:52 PM

Copyright 2016 Redgate Software. Privacy Policy