CODEWITHSUNDEEP
sql-servert-sqlselectsql-insertwindow-functions
Hell-1931
Hell-1931

Reputation: 509

T-SQL Lead () function issue while using GROUP BY clause

I am trying to insert data from several tables into the one resulting table.

But, since I am using the Lead() function in SELECT / GROUP BY - for creating one of the fields, I am having an error:

Windowed functions can only appear in the SELECT or ORDER BY clause

How should I modify my code so it would work?

      USE [WH];
      INSERT INTO [exp].[Profile] (

                   [MedicalConditionFK]
                 , [EHRProgramFK]
                 , [EffectiveDate]
                 , [ExpirationDate]

               )

       SELECT 
              c.medicalcondition_id
            , Max(ISNULL(epi.[EHRProgramFK], -1)) AS [EHRProgramFK]
            , p.[effective_date] AS [EffectiveDate]
            , DateAdd(day, -1, lead (p.[effective_date], 1, '2050-12-31') OVER 
                      (partition by p.[client_id], c.[medicalcondition_id] Order By 
                                                   p.[effective_date])) AS [ExpirationDate] 

      FROM 
            [exp].[Condition] c
            INNER JOIN [exp].[Medical] p ON 
                                         c.[ClientMedicalProfile_id] = p.[ClientMedicalProfile_id]
            LEFT JOIN [exp].[Episode] epi ON 
                                         (p.[effective_date] BETWEEN epi.[AdmissionDate] 
                                          AND epi.[DischargeDate] and epi.[DBCode] = 'CS-SM')

      WHERE
            (p.[effective_date] IS NOT NULL)


      GROUP BY
              c.medicalcondition_id
            , p.[effective_date]
            , DateAdd(day, -1, lead (p.[effective_date], 1, '2050-12-31') OVER 
                  (partition by p.[client_id], c.[medicalcondition_id] Order By p.[effective_date]))

Upvotes: 0

Views: 452

Answers (1)

GMB
GMB

Reputation: 222582

To make your query valid, you need to put the columns that are used in the window function in the group by clause (not the window function itself).

Basically it means that you have to add column client_id:

GROUP BY
    c.medicalcondition_id,
    p.[effective_date],
    p.[client_id]

While this will avoid the syntax error, I cannot tell if this will produce the resultset that you want. For this, you would probably need to ask a new question, providing proper sample data and desired results.

Upvotes: 1

Related Questions