1

In my following query I need to get the top 1st record's value in pivot row value. So basically I get all parts and if there is a service entry with that part and a revision number I used that revision number from the service entry part table and if not i use the revision number from part table(as you can see I do coalesce). Now I need to get the revision number from the most recent service entry if it exists based on the input date provided by the user and if that doesnt exist then colese will give revision number from part table. Currently it is not picking up the most recent service entry revision number

------Part Table------
ID    Description  RevisionNumber
123   xyz           100

-------ServiceEntryPart Table-----
ID   ServiceEntryID   PartID   PartRevisionNumber  SystemModuleID
1    20               123       123                 56
2    21               123       124                 56

-------ServiceEntryTable------
ID    SystemID    ServiceDateTime
20    31           1/1/2014
21    32           1/2/2014

So the correct query results would be as follows if I run the query on 1/3/2014. You can see that the revision number in the pivot value is the revision number from the service entry part table from the most recent service entry

   PartID  PartDescription  System System2  
    123    xyz              124    124 

if the part was not used in a service entry (no service entry part table record)then the result would look as follows.So the part record is still in the results but the revision number is the baseline-from part table since this part was not used in a service entry

   PartID  PartDescription  System1 System2  
    123    xyz              100     100 

Currently I get incorrect revision number and not the most recent one if the part was used in a service entry

   DECLARE @PivotColumnHeaders NVARCHAR(MAX)

SELECT @PivotColumnHeaders =  
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System  
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (       
select  Part.System as Subsystem, Part.ID as PartNumber, Part.PartDescription,System.SystemFullName,
COALESCE(ServiceEntryPart.PartRevisionNumber,Part.RevisionNumber) AS Revision
 from Part
    left outer join ServiceEntryPart on Part.ID = ServiceEntryPart.PartID
left outer join serviceentry
 on ServiceEntryPart.ServiceEntryID = (select top 1  ID from ServiceEntry where ServiceEntry.ServiceDateTime <= GETDATE() order by ServiceDateTime DESC)
left outer join System on System.PlatformID = Part.platformid


 ) AS PivotData
   PIVOT (
     MAX(Revision)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     ) 
   ) AS PivotTable   
' 
exec sp_executesql @PivotTableSQL

If I simply want to get the most recent revision number for a given part from a service entry i can do the following and it gives me the correct but im interested in doing it in a pivot and show every part from the part table

select top 1 ServiceDateTime,ServiceEntry.ID,ServiceEntryPart.PartRevisionNumber from ServiceEntry
inner join  ServiceEntryPart on ServiceEntry.ID = ServiceEntryPart.ServiceEntryID
where PartID = 670220 and ServiceDateTime <= getdate()
order by ServiceDateTime desc

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.