|
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 --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...
(comments are locked)
|
|
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: 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.
(comments are locked)
|
|
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
(comments are locked)
|
