Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sergeant_time
Yak Posting Veteran
73 Posts |
 Posted - 2010-02-04 : 15:07:14
|
| I am having trouble with creating a subquery. My overall objective is to return results for three different Splits (PCCC,HBCC, FPCC) which would be reported in drop down menus in the final report. I keep revieving the error conversion failed when converting datetime from character string. SELECT CASE WHEN INS_PRODUCT = 'HB' THEN 'HBCC' ELSE CAST(INS_PRODUCT AS varchar(2)) END AS Split, SR_NUM AS SRNumber, INS_PRODUCT AS Type, SR_SUB_AREA AS SubAreaFROM (SELECT CASE WHEN SR_SUB_AREA = 'Complaint' OR SR_SUB_AREA = 'Compliment' OR SR_SUB_AREA = 'Crisis Call' OR SR_SUB_AREA = 'Death Notification' OR SR_SUB_AREA = 'Eligibility' OR SR_SUB_AREA = 'Enrollment' OR SR_SUB_AREA = 'Facility' OR SR_SUB_AREA = 'Fee Basis' OR SR_SUB_AREA = 'General' OR SR_SUB_AREA = 'Hardship' OR SR_SUB_AREA = 'HealthierUS Vet' OR SR_SUB_AREA = 'HEC' OR SR_SUB_AREA = 'IRIS' OR SR_SUB_AREA = 'Medicare' OR SR_SUB_AREA = 'Medicare Part D' OR SR_SUB_AREA = 'NARA' OR SR_SUB_AREA = 'NCOA' OR SR_SUB_AREA = 'NON VA Questions' OR SR_SUB_AREA = 'Pharmacy' OR SR_SUB_AREA = 'Privacy Mailing' OR SR_SUB_AREA = 'Project Hero' OR SR_SUB_AREA = 'Regional Office' OR SR_SUB_AREA = 'Repayment Plan' OR SR_SUB_AREA = 'Transcription' OR SR_SUB_AREA = 'Transferred Calls' OR SR_SUB_AREA = 'VA Survey-HEalth C' OR SR_SUB_AREA = 'VIC General Question' OR SR_SUB_AREA = 'Waiver' OR SR_SUB_AREA IS NULL THEN 'Other' ELSE SR_SUB_AREA END AS SubArea, SR_SUB_AREA, INS_PRODUCT, SR_NUM FROM S_SRV_REQ WHERE (INS_PRODUCT = 'HB')) AS aWHERE (SR_NUM IS NOT NULL) AND (SR_SUB_AREA IS NOT NULL) AND (INS_PRODUCT IS NOT NULL) AND (SR_SUB_AREA >= @Startdate) AND (SR_SUB_AREA < DATEADD(d, 1, @Enddate))GROUP BY INS_PRODUCT, SR_SUB_AREA, SR_NUMORDER BY SubArea |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-04 : 16:55:08
|
| What is the datatype of the field SR_SUB_AREA ,@Startdate and @Enddate. What values are you passing to those variables? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 09:22:50
|
| your posted query has comparison of SR_SUB_AREA with text values as well as datefield. can i ask what its actual data type is? |
|
|
|
|
|
|
|
|
Subquery