September 25, 2015 at 11:32 am
rkelly58 (9/25/2015)
Can't u just do it for me?I won't accuse u of barking again, promise 🙂
are you telling me that there is no one else in your "business" that can help you out?.....after all, you say that you are new to SQL. so I was thinking that you probably have colleagues?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 28, 2015 at 3:41 am
Hi J,
I work in a new out reach team, there is nobody else that has SQL in the team.
If there was someone, I wouldn't have had the need to post here and pester you.
If anyone asks me for help, its not in my nature just to give them small nuggets to fix it themselves, I help them achieve their goal, so that they can learn how the solution was done.
Thing is, you've helped a lot, and I appreciate it, but can't you just do the last bit and bring everything together using the scripts and sample data provided?
Kind Regards,
September 29, 2015 at 8:31 am
J,
Not sure the PMs are working, did you get the code I just sent u?
September 29, 2015 at 9:26 am
rkelly58 (9/29/2015)
J,Not sure the PMs are working, did you get the code I just sent u?
yeah..I did....several
busy at the moment....if I get a chance I may take a look this evening.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 30, 2015 at 9:58 am
-- 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
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply