CODEWITHSUNDEEP
sqlsql-server
kurabdurbos
kurabdurbos

Reputation: 277

SQL - Name missing but it's not

So I built this query based on other posts here in SO. 

Declare @DatePeriod datetime

Set @DatePeriod = '2019-07-30'

SELECT  CustomSpecializationName,
        IsNull([1],0) as 'Week 1',
        IsNull([2],0) as 'Week 2',
        IsNull([3],0) as 'Week 3',
        IsNull([4],0) as 'Week 4',
        IsNull([5], 0) as 'Week 5'

FROM 
(
    SELECT  
        cs.CustomSpecializationName
        ,DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, r.MedicalReviewDate), 0), r.MedicalReviewDate) +1 as [Weeks]
    From Referral r
      LEFT OUTER JOIN CustomSpecialization cs ON cs.CustomSpecializationID = r.CustomSpecializationID
    WHERE 1=1
    AND DatePart(Month, r.MedicalReviewDate)= DatePart(Month, @DatePeriod )
    AND DatePart(Year, r.MedicalReviewDate)= DatePart(Year, @DatePeriod )
)p
    Pivot (COUNT(CustomSpecializationName) for Weeks in ([1],[2],[3],[4],[5])) as pv

The issue is that when I am trying to run this - SQL Profiler is telling me the the CustomSpecializationName is missing.....Not sure what I am missing?

Upvotes: 0

Views: 64

Answers (1)

zip
zip

Reputation: 4061

Run this query:

Declare @DatePeriod datetime

Set @DatePeriod = '2019-07-30'

    SELECT   
            IsNull([1],0) as 'Week 1',
            IsNull([2],0) as 'Week 2',
            IsNull([3],0) as 'Week 3',
            IsNull([4],0) as 'Week 4',
            IsNull([5], 0) as 'Week 5'

    FROM 
    (
        SELECT  
            cs.CustomSpecializationName
            ,DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, r.MedicalReviewDate), 0), r.MedicalReviewDate) +1 as [Weeks]
        From Referral r
          LEFT OUTER JOIN CustomSpecialization cs ON cs.CustomSpecializationID = r.CustomSpecializationID
        WHERE 1=1
        AND DatePart(Month, r.MedicalReviewDate)= DatePart(Month, @DatePeriod )
        AND DatePart(Year, r.MedicalReviewDate)= DatePart(Year, @DatePeriod )
    )p
        Pivot (COUNT(CustomSpecializationName) for Weeks in ([1],[2],[3],[4],[5])) as pv

It should work.

You are already using that column as "column being aggregated" in the pivoted columns 'Week 1' ... 'Week n'

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Upvotes: 1

Related Questions