CODEWITHSUNDEEP
sqlt-sqlfunctionsql-server-2008-r2
ajspacemanspiff
ajspacemanspiff

Reputation: 478

T_SQL Function with 'with' clause

I am wondering why I cannot create the following T-SQL function:

CREATE FUNCTION DaysIncarceratedInYear
--Requires a Patient ID and a year
-- will return the number of days the patient was incarcerated for the year

--incarcerated is stored in a flowsheet
-- the FlowdataID are:
--                Incarceration Start Date:  'ZZZZZ00071
--                Incarceration End Date  :  'ZZZZZ00072'


(
@PID varchar(10),
@YEAR int
)

RETURNS int

--DECLARE @PID varchar(10)
--DECLARE @YEAR int
--set @PID = 'ZZZZZ000L6'
--set @YEAR =2012

AS
BEGIN
declare @R int


SELECT @R = Numdays from (
                ;with startdates as
                (
                SELECT
                dbo.View_PatientFlowValue.FlowValue_Value as val,
                ROW_NUMBER() OVER(ORDER BY dbo.View_PatientFlowValue.FlowValue_Value) AS RowNumber
                FROM dbo.View_PatientFlowValue
                WHERE dbo.View_PatientFlowValue.FlowData_ID = 'ZZZZZ00071' 
                AND dbo.View_PatientFlowValue.FlowValue_RecordState<>1
                AND Patient_ID = @PID

                )
                ,enddates as
                (
                SELECT
                dbo.View_PatientFlowValue.FlowValue_Value val,
                ROW_NUMBER() OVER(ORDER BY dbo.View_PatientFlowValue.FlowValue_Value) AS RowNumber
                FROM dbo.View_PatientFlowValue
                WHERE dbo.View_PatientFlowValue.FlowData_ID = 'ZZZZZ00072' 
                AND dbo.View_PatientFlowValue.FlowValue_RecordState<>1
                AND Person_ID = @PID

                )

                Select sum(DATEDIFF(d,CalcStart, CalcEnd)) as NumDays

                from (
                        select 
                            case 
                                when DATEDIFF(d, cast(str(@YEAR*10000+1*100+1) as date), s.val) < 1 then '1/1/' + str(@YEAR)
                                else s.val
                            end As CalcStart,
                            ISNULL(e.Val, cast(str((@YEAR+1)*10000+1*100+1) as date)) as CalcEnd,
                        s.val as realstart, e.val as realend
                        FROM StartDates s
                        LEFT OUTER JOIN EndDates e ON s.RowNumber = e.RowNumber
                    ) accountforyear 

        ) 

return @R
END
GO

It states there is incorrect syntax near ";" , but if I take the ";" out, it tells me there is incorrect syntax near the keyword "with". What is the proper syntax here?

This query works fine standalone.

Upvotes: 1

Views: 4207

Answers (3)

Moho
Moho

Reputation: 16498

Define your CTEs before the query:

;with CTE_A( ColA, ColB)
as
(
    select
        ColA
        , ColB
    from
        SomeTable
)
,CTE_B( ColA, ColC )
as
(
    select
        ColA
        , ColC
    from
        SomeTable2
)


select
    *
from
    CTE_A a
    inner join CTE_B b
     on a.ColA = b.ColB

Upvotes: 0

devio
devio

Reputation: 37215

I've never seen a CTE as a subquery.

Try re-writing as

;
with startdates as
(
   ... copy everything

)
Select @R = sum(DATEDIFF(d,CalcStart, CalcEnd))
FROM ...

return @R

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to put the with statement before the select:

        with startdates as
        (
        SELECT
        dbo.View_PatientFlowValue.FlowValue_Value as val,
        ROW_NUMBER() OVER(ORDER BY dbo.View_PatientFlowValue.FlowValue_Value) AS RowNumber
        FROM dbo.View_PatientFlowValue
        WHERE dbo.View_PatientFlowValue.FlowData_ID = 'ZZZZZ00071' 
        AND dbo.View_PatientFlowValue.FlowValue_RecordState<>1
        AND Patient_ID = @PID

        )
        ,enddates as
        (
        SELECT
        dbo.View_PatientFlowValue.FlowValue_Value val,
        ROW_NUMBER() OVER(ORDER BY dbo.View_PatientFlowValue.FlowValue_Value) AS RowNumber
        FROM dbo.View_PatientFlowValue
        WHERE dbo.View_PatientFlowValue.FlowData_ID = 'ZZZZZ00072' 
        AND dbo.View_PatientFlowValue.FlowValue_RecordState<>1
        AND Person_ID = @PID

        )

        Select @R = sum(DATEDIFF(d,CalcStart, CalcEnd)) as NumDays

        from (
                select 
                    case 
                        when DATEDIFF(d, cast(str(@YEAR*10000+1*100+1) as date), s.val) < 1 then '1/1/' + str(@YEAR)
                        else s.val
                    end As CalcStart,
                    ISNULL(e.Val, cast(str((@YEAR+1)*10000+1*100+1) as date)) as CalcEnd,
                s.val as realstart, e.val as realend
                FROM StartDates s
                LEFT OUTER JOIN EndDates e ON s.RowNumber = e.RowNumber
            ) accountforyear;

So, the @R = goes in the select after the with. You don't need a subquery here, so I just added the assignment in.

Upvotes: 2

Related Questions