SQL query join with nested select

SSAS and SSRS
1

I am trying to write a query needed for a report. I first need to get the following data, which is working.

SELECT MachineID, Caption00, CSDVersion00, CountryCode00, Version00
FROM OPERATING_SYSTEM_EXT_DATA
INNER JOIN
Operating_System_DATA ON OPERATING_SYSTEM_EXT_DATA.MachineID = Operating_System_DATA.MachineID
WHERE OPERATING_SYSTEM_EXT_DATA.Name00 LIKE '%Server%'

I then need to run the following query on the results of the query above.

Select * FROM vSites WHERE ReportToSite = 'ABC'

I have tried nesting it and using an alias, but no luck so far. I then need to count the distinct captions and group by ReportToSite. I am a newbie and have been struggling with this all week. Any help would be appreciated.

Thank you!

2

what column ties vSites to the OPERATING_SYSTEM_EXT_DATA and/or Operating_System_DATA

3

Sounds like you want an inline table function with a @ReportToSite parameter. You provided no DDL for the tables so I guessed at where the columns are but something like this should do:

IF Object_Id(N'dbo.OS_SystemExtData') IS NOT NULL
    DROP FUNCTION
       dbo.OS_SystemExtData;
GO

CREATE FUNCTION dbo.OS_SystemExtData
(@ReportToSite VARCHAR(50)
)
RETURNS TABLE
AS
     RETURN
(
    SELECT
       osed.MachineID
     , osed.Caption00
     , osed.CSDVersion00
     , osed.CountryCode00
     , osed.Version00
    FROM
       dbo.OPERATING_SYSTEM_EXT_DATA osed
       INNER JOIN Operating_System_DATA osd ON osed.MachineID = osd.MachineID
    WHERE osed.Name00 LIKE '%Server%'
          AND ReportToSite = @ReportToSite
);
GO
4

Thank you so much for the help. This looks like it may work. I am tweaking it now.

Thanks,

Karen