Using a table valued function

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

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...

Syntax is

SELECT * FROM Patients CROSS JOIN Dates

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

Greg

i want to call the tabled value function GetMeasurement for all possible values of Patients and Dates

Oh, sorry, I misunderstood.  You should use CROSS APPLY for a table valued function.

Greg

you mean this?



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

@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

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
 

the question is: is the function really a table valued function? resp: what is the output of that function?

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

Select allOpen in new window