Solved

Help witha query

Posted on 2014-11-18
2
60 Views
Last Modified: 2015-02-03
Hello,

I have a function that returns a table.  It wokrs ok but now the users want the column names to include a prefix/postfix based on the parameter @Month.

So if i was to call the function like ReadControlMeasurement( 22, 3)

then each column name in the returned table should be prefixed or postfixed with a 3.  Such as:

 msr_id-3
 kli_Kliniknr-3
 pat_id-3
 

Open in new window


The function gets called many times for different months and they need some help idenitifying the columns in the different tablesets returned.

Is there any way to do this? Can i use dynamic sql here?


ALTER FUNCTION [dbo].[ReadControlMeasurement]
(
	-- Add the parameters for the function here
	@pat_id int,
	@TheMonth int
)


RETURNS TABLE as
  return
  (

    SELECT  [msr_id]
      ,M.[kli_Kliniknr]
      ,M.[pat_id]
      ,convert( date, [msr_Date]) as msr_Date 
      ,[msr_Q1Frequency]
      ,[msr_Q1Severity]
      ,[msr_Q1Total]
      ,[msr_Q2Frequency]
      ,[msr_Q2Severity]
      ,[msr_Q2Total]
      ,[msr_Q3Frequency]
      ,[msr_Q3Severity]
      ,[msr_Q3Total]
      ,[msr_Q4Frequency]
      ,[msr_Q4Severity]
      ,[msr_Q4Total]
      ,[msr_Q5Frequency]
      ,[msr_Q5Severity]
      ,[msr_Q5Total]
      ,[msr_Q6Frequency]
      ,[msr_Q6Severity]
      ,[msr_Q6Total]
      ,[msr_Q7Frequency]
      ,[msr_Q7Severity]
      ,[msr_Q7Total]
      ,[msr_Q8Frequency]
      ,[msr_Q8Severity]
      ,[msr_Q8Total]
      ,[msr_Q9Frequency]
      ,[msr_Q9Severity]
      ,[msr_Q9Total]
      ,[msr_Q10Frequency]
      ,[msr_Q10Severity]
      ,[msr_Q10Total]
      ,[msr_Q11Frequency]
      ,[msr_Q11Severity]
      ,[msr_Q11Total]
      ,[msr_Q12Severity]
      ,[msr_Q12Frequency]
      ,[msr_Q12Total]
      ,[msr_Total]
      ,[msr_Pain]

      ,(select case when max(pm.med_id) is not null then 'Ja' else '' end as [Analgetikabehandling] from [PatientMedicines] PM 
          inner join Medicines Med on PM.med_id = med.med_id 
          inner join MedicineGroups MG on med.mgp_id = MG.mgp_id 
          and (MG.mgp_ATCCode = 'N 02') 
		where PM.pat_id = M.pat_id) as  [Analgetikabehandling]

      ,case when [msr_Activation] is not null then 'Ja' else '' end as [msr_Activation]
      ,case when [msr_PhysicalActivity] is not null then 'Ja' else '' end as [msr_PhysicalActivity]
      ,case when [msr_CalmEnvironment] is not null then 'Ja' else '' end as [msr_CalmEnvironment]
      ,case when [msr_Massage] is not null then 'Ja' else '' end as [msr_Massage]
      ,case when [msr_Music] is not null then 'Ja' else '' end as [msr_Music]
      ,case when [msr_Outdoors] is not null then 'Ja' else '' end as [msr_Outdoors]
      ,case when [msr_ExtraSupportMeals] is not null then 'Ja' else '' end as [msr_ExtraSupportMeals]
      ,case when [msr_ExtraSupportAnxiety] is not null then 'Ja' else '' end as [msr_ExtraSupportAnxiety]
      ,case when [msr_ExtraSupportOther] is not null then 'Ja' else '' end as [msr_ExtraSupportOther]
      ,case when [msr_OtherActivity] is not null then 'Ja' else '' end as [msr_OtherActivity]
	   ,mc.*
      ,[msr_Approved]
      ,[msr_ApprovedDate]
  
  FROM [Measurements] M outer apply dbo.ReadPatientMedicineCounts( m.pat_id, m.msr_date ) mc
  where msr_id = dbo.GetControlMeasurement_msr_id( @pat_id, @TheMonth )  
  and msr_Approved = 'Y'
  )

Open in new window

0
Comment
Question by:soozh
2 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 250 total points
ID: 40450129
Instead of modifying the column names, maybe just return the provided value as another column in your results?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40450160
Agreed.  

Dynamic set names is generally a bad idea as any downstream process may not necessarily be able to handle changed names.  SSRS, like SSIS, requires a 'contract' between data source and destination, and changing column names would break that contract every time (unless you're using SSRS matrixes).

Maybe  contain in the set for all columns, or column names like current_month, current_month_minus_one, current_month_minus_two, etc.?
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query to calculate avaerage 21 52
Negative isnull? 3 15
IIF in access query 19 25
Disable TLS1.0 on Win 2012 server 7 29
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question