-- you say you have a remote server, and lookin back thro the various code

-- you have sent me I am not sure which is/is not relevant

-- therefore please insert your code of choice below where indcated for your two exg queries

-- you will note, I hope, that the latter part of the code references the names of the ctes we have created

-- and are not random #names

-- if you need to declare variables put them here

-- note the semicolon (this has been explained to you)

; -- this is the semi colon

WITH roomhours (hour_number ,hour_description) as

(

SELECT 0, '12 am' UNION ALL

SELECT 1, '1 am' UNION ALL

SELECT 2, '2 am' UNION ALL

SELECT 3, '3 am' UNION ALL

SELECT 4, '4 am' UNION ALL

SELECT 5, '5 am' UNION ALL

SELECT 6, '6 am' UNION ALL

SELECT 7, '7 am' UNION ALL

SELECT 8, '8 am' UNION ALL

SELECT 9, '9 am' UNION ALL

SELECT 10, '10 am' UNION ALL

SELECT 11, '11 am' UNION ALL

SELECT 12, '12 pm' UNION ALL

SELECT 13, '1 pm' UNION ALL

SELECT 14, '2 pm' UNION ALL

SELECT 15, '3 pm' UNION ALL

SELECT 16, '4 pm' UNION ALL

SELECT 17, '5 pm' UNION ALL

SELECT 18, '6 pm' UNION ALL

SELECT 19, '7 pm' UNION ALL

SELECT 20, '8 pm' UNION ALL

SELECT 21, '9 pm' UNION ALL

SELECT 22, '10 pm' UNION ALL

SELECT 23, '11 pm' )

, roombookdata as (--insert your room query here between the round brackets-- )

, pclogdata as ( --insert your pc query here between the round brackets--)

, pcsummary as (

SELECT

r.hour_number

, r.hour_description

, p.machine

, p.logontime

, p.logofftime

FROM roomhours AS r LEFT OUTER JOIN

pclogdata p ON r.hour_number >= p.logonhour

and r.hour_number < p.logoffhour)

, pctotal as (

SELECT

hour_description

, COUNT(DISTINCT machine) AS pclogin

FROM pcsummary

GROUP BY

hour_number

, hour_description

)

, roomsummary as (

SELECT

r.hour_number

, r.hour_description

, i.description

, i.StartDateTime

, i.EndDateTime

, i.Name

FROM roomhours AS r LEFT OUTER JOIN

roombookdata AS i ON r.hour_number >= DATEPART(hh , i.StartDateTime)

AND r.hour_number < DATEPART(hh , i.EndDateTime)

)

SELECT

rs.hour_description

, rs.description

, rs.StartDateTime

, rs.EndDateTime

, rs.Name

, pt.pclogin

FROM roomsummary AS rs INNER JOIN

pctotal AS pt ON rs.hour_description = pt.hour_description

ORDER BY

rs.hour_number;

edit typo