• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Switch Command Equivalent

I have a field in my SQL query that pulls out the month part of a date field.  So, this field naturally displays when I run the query as 1, 2, 3, etc.  On the report, I would like to have the actual month name show.  I am working in the layout view in SQL Reporting services.  In the header, where the numbers currently show, I would like to write an expression that would replace the number 1 with Jan, the number 2 with Feb, the number 3 with Mar, etc.  In Access, I would use a Switch command to accomplish this.  I am not sure how to go about it in SQL Reporting Services.  Thanks in advance.
0
PHS_IT
Asked:
PHS_IT
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select case field when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' ... when 12 then 'Dec' else 'N/A' end as MonthName
0
 
bwdowhanCommented:
You can also use datename:

select datename(month,1)

Just replace the 1 with your variable and pass in the month number

Brian
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
AFAIK, you have to pass a date value for the second argument for datename, so you would rather needs something like this:

select datename(month,convert(datetime, '2000/' + right('00' + cast(month as varchar(2)), 2) + '/01', 120) )
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
bwdowhanCommented:
Good catch AngelIII... I was just coming to post an amendment.... datename needs a full date.. not just the number of the month.
0
 
PHS_ITAuthor Commented:
OK, I'm totally confused.  I'm no SQL guru.  Here is my complete code.  Where would I place the Select statement within this code?  I'm using SQL Reporting services, and I was hoping I could just build an expression right in the header field.

SELECT DISTINCT MONTH(TPM300_PAT_VISIT.adm_ts) AS ADMIT_MONTH, TSM040_PERSON_HDR.lst_nm, COUNT(*) AS ADMIT_COUNT
FROM         TPM300_PAT_VISIT INNER JOIN
                      TSM180_MST_COD_DTL ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id INNER JOIN
                      TPM315_VISIT_CARE_GIVER ON TPM300_PAT_VISIT.vst_int_id = TPM315_VISIT_CARE_GIVER.vst_int_id INNER JOIN
                      TPM114_CAR_GVR_FUNC ON TPM315_VISIT_CARE_GIVER.func_aso_int_id = TPM114_CAR_GVR_FUNC.func_aso_int_id INNER JOIN
                      TPM100_CARE_GIVER ON TPM114_CAR_GVR_FUNC.car_gvr_int_id = TPM100_CARE_GIVER.car_gvr_int_id INNER JOIN
                      TSM040_PERSON_HDR ON TPM100_CARE_GIVER.psn_int_id = TSM040_PERSON_HDR.psn_int_id
GROUP BY YEAR(TPM300_PAT_VISIT.adm_ts), MONTH(TPM300_PAT_VISIT.adm_ts), TSM180_MST_COD_DTL.cod_dtl_ext_id,
                      TSM040_PERSON_HDR.lst_nm
HAVING      (TSM180_MST_COD_DTL.cod_dtl_ext_id = 'E') AND (YEAR(TPM300_PAT_VISIT.adm_ts) = 2006)
ORDER BY MONTH(TPM300_PAT_VISIT.adm_ts), TSM040_PERSON_HDR.lst_nm
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

SELECT DISTINCT datename(MONTH,TPM300_PAT_VISIT.adm_ts) AS ADMIT_MONTH, TSM040_PERSON_HDR.lst_nm, COUNT(*) AS ADMIT_COUNT
FROM         TPM300_PAT_VISIT INNER JOIN
                      TSM180_MST_COD_DTL ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id INNER JOIN
                      TPM315_VISIT_CARE_GIVER ON TPM300_PAT_VISIT.vst_int_id = TPM315_VISIT_CARE_GIVER.vst_int_id INNER JOIN
                      TPM114_CAR_GVR_FUNC ON TPM315_VISIT_CARE_GIVER.func_aso_int_id = TPM114_CAR_GVR_FUNC.func_aso_int_id INNER JOIN
                      TPM100_CARE_GIVER ON TPM114_CAR_GVR_FUNC.car_gvr_int_id = TPM100_CARE_GIVER.car_gvr_int_id INNER JOIN
                      TSM040_PERSON_HDR ON TPM100_CARE_GIVER.psn_int_id = TSM040_PERSON_HDR.psn_int_id
GROUP BY YEAR(TPM300_PAT_VISIT.adm_ts), datename(MONTH,TPM300_PAT_VISIT.adm_ts) , MONTH(TPM300_PAT_VISIT.adm_ts), TSM180_MST_COD_DTL.cod_dtl_ext_id,
                      TSM040_PERSON_HDR.lst_nm
HAVING      (TSM180_MST_COD_DTL.cod_dtl_ext_id = 'E') AND (YEAR(TPM300_PAT_VISIT.adm_ts) = 2006)
ORDER BY MONTH(TPM300_PAT_VISIT.adm_ts), TSM040_PERSON_HDR.lst_nm

select
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ooops, ignore the last SELECT, forgot to remove that...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now