[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using a table valued function

Posted on 2011-10-28
12
Medium Priority
?
263 Views
Last Modified: 2012-05-12
Hello,

I am using SQL Server 2008.

I have a table called Dates with a column called DateValue, and a table of Patients with a column called PatientID.

I have also written a table valued function called GetMeasurement that takes two parameters which are DateValue and PatientID.

How do i write an SQL query that returns the table returned by GetMeasurement for all possible combinations of PatientID and DateValue?

0
Comment
Question by:soozh
  • 5
  • 4
  • 3
12 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37044214
What is the relationship between Dates and Patients?  From your description, it doesn't sound like they have any common columns between them.  If you are just looking at creating a Cartesian product between the two, you could use a cross join, but I'm not sure if that is what you are looking for or not.

Greg

0
 

Author Comment

by:soozh
ID: 37044225
Yes a cross join is what i want... all values for all possible data and patients.

Not sure of the syntax of the cross join...
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37044236
Syntax is

SELECT * FROM Patients CROSS JOIN Dates

You don't need "ON" like most joins because you joining on every row.

Greg


0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:soozh
ID: 37044254
i want to call the tabled value function GetMeasurement for all possible values of Patients and Dates
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37044291
Oh, sorry, I misunderstood.  You should use CROSS APPLY for a table valued function.

Greg
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37044305
you mean this?



SELECT p.PatientID, d.Date, GetMesurement(p.patientid, d.date) measure
FROM Patients p
CROSS JOIN Dates d
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37044331
@angelIII

GetMeasurement is a table valued function.  Now that I think about it, CROSS APPLY won't work because that is more like an INNER JOIN.  OUTER APPLY is like a OUTER JOIN.  I'm not sure that a Cartesian product is an option with table valued functions.  

Greg

0
 

Author Comment

by:soozh
ID: 37044473
This is my statement.  But i get an error when i run it.  It says "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetMeasurement", or the name is ambiguous."  


SELECT p.pat_Personnummer, d.DateValue, dbo.GetMeasurement( p.pat_Personnummer, @kli_kliniknr, d.DateValue ) m
  FROM "#ReportDates" d
   CROSS JOIN (
     SELECT DISTINCT pat_Personnummer
     FROM vw_Measurements
     WHERE msr_MeasurementKlinik = @kli_kliniknr
     and msr_Approved = 'Y'
   
    ) p
 
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 400 total points
ID: 37044562
You get that error because you are trying to use a table valued function like a scalar function.  It doesn't work that way.  As long as you are cross joining the two tables, you should be able to cross apply the function to get the same effect.  Try this:

SELECT p.pat_Personnummer, d.DateValue,  <Add additional columns including ones from GetMeasurement function>
  FROM "#ReportDates" d
   CROSS JOIN (
     SELECT DISTINCT pat_Personnummer
     FROM vw_Measurements
     WHERE msr_MeasurementKlinik = @kli_kliniknr
     and msr_Approved = 'Y') p CROSS APPLY
     dbo.GetMeasurement( p.pat_Personnummer, @kli_kliniknr, d.DateValue ) m

Greg

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37044599
the question is: is the function really a table valued function? resp: what is the output of that function?
0
 

Author Comment

by:soozh
ID: 37044802
this is it
USE [BPSD]
GO
/****** Object:  UserDefinedFunction [dbo].[GetMeasurement]    Script Date: 10/28/2011 15:45:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[GetMeasurement] 
(
	-- Add the parameters for the function here
	@pn varchar(15),
    @kli_Kliniknr int,
	@date date
)
RETURNS 
 @Measurement TABLE 
(
    mea_id int,
	mea_Kliniknr smallint,
	mea_Personnummer varchar(15),
	mea_MeasurementDate datetime,
	mea_NPITotal int,
	mea_Food char(1),
	mea_Fluids char(1),
	mea_Sleep char(1),
	mea_Urine char(1),
	mea_Sight char(1),
	mea_Hearing char(1),
	mea_Pain char(1),
	mea_Cooperation char(1),
	mea_Temp char(1),
	mea_Pulse char(1),
	mea_BloodPreasure char(1),
	mea_Breathing char(1),
	mea_BloodSugar char(1),
	mea_UrineTest char(1),
	mea_Activation char(1),
	mea_PhysicalActivity char(1),
	mea_CalmEnvironment char(1),
	mea_Massage char(1),
	mea_Music char(1),
	mea_ExtraSupportMeals char(1),
	mea_ExtraSupportAnxiety char(1),
	mea_ExtraSupportOther varchar(1),
	mea_Outdoors char(1),
	mea_OtherActivity char(255),
	mea_Doctor char(1),
	mea_Faeces char(1),
	mea_CarePlan char(1),
	mea_CarriedForward char(1)
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
--  declare @msr_Total int ;
--  declare @msr_Date date ;
  declare @FutureMeasurementCount int ;
  declare @PatientsClinic int ;
  declare @pat_Status int ;
  declare @pat_StatusDate date ;
  declare @Monthcount int ; 

  declare @mea_id int;
  declare @mea_Kliniknr smallint;
  declare @mea_Personnummer varchar(15);
  declare @mea_MeasurementDate datetime;
  declare @mea_NPITotal int;
  declare @mea_Food char(1);
  declare @mea_Fluids char(1);
  declare @mea_Sleep char(1);
  declare @mea_Urine char(1);
  declare @mea_Sight char(1);
  declare @mea_Hearing char(1);
  declare @mea_Pain char(1);
  declare @mea_Cooperation char(1);
  declare @mea_Temp char(1);
  declare @mea_Pulse char(1);
  declare @mea_BloodPreasure char(1);
  declare @mea_Breathing char(1);
  declare @mea_BloodSugar char(1);
  declare @mea_UrineTest char(1);
  declare @mea_Activation char(1);
  declare @mea_PhysicalActivity char(1);
  declare @mea_CalmEnvironment char(1);
  declare @mea_Massage char(1);
  declare @mea_Music char(1);
  declare @mea_ExtraSupportMeals char(1);
  declare @mea_ExtraSupportAnxiety char(1);
  declare @mea_ExtraSupportOther char(1);
  declare @mea_Outdoors char(1);
  declare @mea_OtherActivity char(1);
  declare @mea_Doctor char(1);
  declare @mea_Faeces char(1);
  declare @mea_CarePlan char(1);
  declare @mea_CarriedForward char(1);
  
  select top 1
  @mea_id = msr_id,
  @mea_Kliniknr = kli_kliniknr,
  @mea_Personnummer = pat_Personnummer,
  @mea_MeasurementDate = msr_Date,
  @mea_NPITotal = msr_Total,
  @mea_Food = msr_Food,
  @mea_Fluids = msr_Fluids,
  @mea_Sleep = msr_Sleep,
  @mea_Urine = msr_Urine,
  @mea_Sight = msr_Sight ,
  @mea_Hearing = msr_Hearing,
  @mea_Pain = msr_Pain,
  @mea_Cooperation = msr_Cooperation,
  @mea_Temp = msr_Temp,
  @mea_Pulse = msr_Pulse,
  @mea_BloodPreasure = msr_BloodPreasure,
  @mea_Breathing = msr_Breathing,
  @mea_BloodSugar = msr_BloodSugar ,
  @mea_UrineTest = msr_UrineTest,
  @mea_Activation = CASE len( msr_Activation ) when 0 then 'N' else 'Y' end,
  @mea_PhysicalActivity = CASE len( msr_PhysicalActivity ) when 0 then 'N' else 'Y' end,
  @mea_CalmEnvironment = CASE len( msr_CalmEnvironment ) when 0 then 'N' else 'Y' end,
  @mea_Massage = CASE len( msr_Massage ) when 0 then 'N' else 'Y' end,
  @mea_Music = CASE len( msr_Music ) when 0 then 'N' else 'Y' end,
  @mea_ExtraSupportMeals = msr_ExtraSupportMeals,
  @mea_ExtraSupportAnxiety = msr_ExtraSupportAnxiety,
  @mea_ExtraSupportOther = CASE len( msr_ExtraSupportOther ) when 0 then 'N' else 'Y' end,
  @mea_Outdoors = CASE len( msr_Outdoors ) when 0 then 'N' else 'Y' end,
  @mea_OtherActivity = CASE len( msr_ExtraSupportOther ) when 0 then 'N' else 'Y' end,
  @mea_Doctor = msr_Doctor,
  @mea_Faeces = msr_Faeces,
  @mea_CarePlan = CASE len( msr_CarePlan ) when 0 then 'N' else 'Y' end
  
--   @msr_Total = msr_Total, @msr_Date = msr_Date 
   from Measurements 
  where pat_Personnummer = @pn and msr_Date <= @date and kli_Kliniknr = @kli_KlinikNr and msr_Approved = 'Y'
  order by msr_Date desc;
  
  if not (@mea_id is null)
  begin
    
    -- If this measurement was not taken this month then we need to work out if we should carry it forward.
    if @mea_MeasurementDate < DateAdd( month, -1, @date )
    begin

      -- Set the value negative to indicate it was carried forward.
      set @mea_CarriedForward = 'Y' ;
        
      select @FutureMeasurementCount = COUNT(*) from vw_Measurements where pat_Personnummer = @pn and msr_Date > @date and msr_MeasurementKlinik = @kli_KlinikNr ;
  
      -- If there are no measurements in the future then check if the patient is still at the bodende
      if @FutureMeasurementCount = 0 
      begin

        select @PatientsClinic = kli_Kliniknr, @pat_Status = pat_Status, @pat_StatusDate = pat_StatusDate from Patients where pat_Personnummer = @pn ;
      
        -- The patient is still at the same boende. 
        if @PatientsClinic =  @kli_Kliniknr
        begin

          -- Now check the patienst status. 1 = Aktiv, 2 - Avliden, 3 - Avslutad, 4 = Flytadd
          -- If the patient is not active then check that the status date is after the month we are
          -- looking at.  i.e. if the patient is dead then did he die on a date after @date.      
      
          if (@pat_Status <> 1) 
          begin
          
            if @pat_StatusDate < @date 
              set @mea_id = NULL ;
       
          end ;
    
        end
        else set @mea_id = NULL ;
       
      end ;
 
    end ;  
 
  end ;
  if not (@mea_id is null)
    insert @Measurement
    select @mea_id,
  @mea_Kliniknr,
  @mea_Personnummer,
  @mea_MeasurementDate,
  @mea_NPITotal,
  @mea_Food,
  @mea_Fluids,
  @mea_Sleep,
  @mea_Urine,
  @mea_Sight,
  @mea_Hearing,
  @mea_Pain,
  @mea_Cooperation,
  @mea_Temp,
  @mea_Pulse,
  @mea_BloodPreasure,
  @mea_Breathing,
  @mea_BloodSugar,
  @mea_UrineTest,
  @mea_Activation,
  @mea_PhysicalActivity,
  @mea_CalmEnvironment,
  @mea_Massage,
  @mea_Music,
  @mea_ExtraSupportMeals,
  @mea_ExtraSupportAnxiety,
  @mea_ExtraSupportOther,
  @mea_Outdoors,
  @mea_OtherActivity,
  @mea_Doctor,
  @mea_Faeces,
  @mea_CarePlan,
  @mea_CarriedForward
   
  RETURN 
END

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 37044933
then it shall be this:
SELECT p.PatientID, d.Date, f.*
FROM Patients p
CROSS JOIN Dates d
CROSS APPLY dbo.,GetMesurement(p.patientid, 111, d.date) f

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 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