0

Alright so I've poked around at this for a while, and have found other cases with the same issue.

I understand this is usually caused by having duplicate columns w/ the same name. ("An item with the same key has already been added" Error on SSRS When Trying to Set Dataset)

However reviewing my code I can't seem to find where my duplicates are causing this issue, the ones that were I have re-aliased. Any clue on where I might be missing this would be greatly appreciated! Thank you.

@Staff Int ,
@StartDate Date,
@EndDate Date

CREATE TABLE #PostCardAppointmentBack
(
id_num int IDENTITY(1,1),
rnum Decimal,
[ServiceId] INT,
[Client ID] INT,
[Client Name] VARCHAR(250),
[DateTimeIn] DATETIME,
[DateTimeOut] DATETIME,
[Address] VARCHAR(250),
[City] VARCHAR(250),
[State] VARCHAR(16),
[Zip]  VARCHAR(255),
[LastName] VARCHAR(250),
[Building Address] VARCHAR(255),
[Building City] VARCHAR(255),
[Building State] VARCHAR(16),
[Building Zip]VARCHAR(255),
)


insert into #PostCardAppointmentBack 
(
[ServiceID],
[Client ID],
[Client Name],
[DateTimeIn],
[DateTimeOut],
[Address],
[City],
[State],
[Zip],
[LastName],
[Building Address],
[Building City],
[Building State],
[Building Zip]
)


select 
s.serviceId,
s.ClientID as [Client ID] ,
ISNULL(c.FirstName, ' ') + ', ' + ISNULL(c.LastName, ' ') AS [Client Name],
CONVERT(varchar(101), s.DateOfService, 100) AS [DateTimeIn],
CONVERT(varchar(101), s.EndDateOfService, 100) AS [DateTimeOut],
cad.Address as ClientAddress,
cad.City as cCity,
cad.State as cState,
cad.Zip as cZip,
st.LastName as stLastName,
cpv.Address as [Building Address],
cpv.city as [Building City],
cpv.State as [Bulding State],
cpv.ZipCode as [Building Zip]


FROM Services s
LEFT JOIN clients c ON c.ClientId = s.ClientId
LEFT JOIN Staff st ON (st.StaffId = s.clinicianId)

/* Join for Contacts */
LEFT JOIN (
      SELECT
      ca.clientid, ca.Address, ca.City, ca.State,
      ca.Zip, ca.addresstype
      FROM ClientAddresses ca
      WHERE ca.AddressType IN (90, 50319)
            AND ISNULL(ca.RecordDeleted, 'N') <> 'Y') 
      AS cad ON(cad.ClientId = s.ClientId AND cad.ClientId = c.ClientId)

/*Join for Client Programs*/
LEFT JOIN (
      SELECT
      cp.ClientId,cp.ProgramId,p.ProgramName, p.Address, p.City, p.State, p.ZipCode
      FROM ClientPrograms cp 
      JOIN Programs p ON (p.ProgramId = cp.ProgramId)
        AND cp.Status IN (50392)
        AND cp.ProgramId IN (207, 208)
      ) AS CPV on CPV.ClientId = s.ClientId 

WHERE s.status = 70
AND not s.ProcedureCodeId  = 125
AND (@Staff = s.ClinicianId OR @Staff IS NULL)
and @StartDate <= s.DateOfService 
and @EndDate >= s.DateOfService 



insert into #PostCardAppointmentBack ([ServiceId],[Client ID],[Client Name] ,DateTimeIn     ,DateTimeOut ,cad.Address ,cad.City ,cad.State ,cad.Zip,st.LastName ,[Building Address],[Building City],[Building State], [Building Zip] )
values (null,null,null,null,null,null,null,null,null,null,null,null,null,null),
(null,null,null,null,null,null,null,null,null,null,null,null,null,null),
(null,null,null,null,null,null,null,null,null,null,null,null,null,null),
(null,null,null,null,null,null,null,null,null,null,null,null,null,null)


select (Case id_num %4
When 1 Then 0.3 When 2 Then 0.4 When 3 Then 0.1 When 0 Then 0.2
END ) +
CAST(Id_num/4.01 as int) as rnum ,
* from #PostCardAppointmentBack


order by 
 (Case id_num %4
When 1 Then 0.3 When 2 Then 0.4 When 3 Then 0.1 When 0 Then 0.2
END ) +
CAST(Id_num/4.01 as int)
--drop table #PostCardAppointmentBack
Community
  • 1
  • 1
kspyy
  • 51
  • 1
  • 9

1 Answers1

1

You do indeed have two columns with the same name. Your expression aliased rnum and #PostCardAppointmentBack.rnum. Either rename one of these, or avoid using the * shortcut and specify your columns explicitly.

lc.
  • 113,939
  • 20
  • 158
  • 187