September 18, 2015 at 9:16 am
rkelly58 (9/18/2015)
Hi,Yes that's the code.
It pulls out 140,000 rows. -This is data with bookings for "all" rooms
On the Reporting Services, I have parameters called StartDate (this is the date the user wants to check) and a hidden EndDate(this just adds 1 to the date, so that only one day can be selected). Then there is a Site Parameter and a Building Parameter and Room Parameter.
If we can include these in the query, then the results will be filtered to that particular room.
The only slight issue is that the Room name in the Dropdown parameter is named slightly different from the Room Name in the V_LOCATION.Name column.
So for example the dropdown Room name says AB0.001, but the Roomname in V_LOCATION.Name is called AB0.001 Phys Lab, so when filtering the Room name, is it possible to do a LIKE/PATTERN where V_LOCATION.Name IS LIKE/PATTERN matches the Dropdown Roomname Parameter?
I've checked V_LOCATION.Name, all the Room names are the same as the Dropdown Roomname Paramter but some as above include some extra text in there after the initial room name in the same column.... Don't ask me why, it's only something I discovered when manually comparing the two Databases.
Hope that makes sense...
hey..I know naff all about Reporting services...sorry
there are plenty of others on here that do know...so suggest you post a new question that reflects what you now want to do.
to my mind it seems somewhat odd to "pull" 140k records into RS only for RS to filter that to 24 daily rows....but as I said I know diddly squat about RS 😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2015 at 10:47 am
No worries about the Reporting Services part.
Could you still help with combining your query into mine?
September 18, 2015 at 10:58 am
rkelly58 (9/18/2015)
No worries about the Reporting Services part.Could you still help with combining your query into mine?
as a pointer....why not try dumping your data into a temp table and then use the example code to go from there
how does that sound to start with?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 18, 2015 at 11:15 am
This works for my query and allows me to add the parameters:
SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,
data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)
So i get just the records for the date i want + room selected records only as per the following output:
Training09/09/2014 08:00:0009/09/2014 17:00:00A0.002
Exam09/09/2014 17:00:0009/09/2014 18:00:00A0.002
The bit i'm stuck on is I don't know how do to combine this with your code to get the outcome from your query to take the results from above result and then split into hourly just as your query does?
Kind Regards,
September 18, 2015 at 11:31 am
assuming you have created the "roomhours" table
then something akin to this should work ....
with cte as (
SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,
data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)
)
SELECT
r.hour_description
, cte.description
, cte.StartDateTime
, cte.EndDateTime
, cte.Name
FROM roomhours AS r LEFT OUTER JOIN
cte ON r.hour_number >= datepart(hh,cte.StartDateTime)
and r.hour_number < datepart(hh,cte.EndDateTime);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 21, 2015 at 4:22 am
Hi J,
It's Monday and i'm back on the case.
I cannot create the roomhours table, as the user i have access to does not have write permissions to the datasource, the best i can do it temporary tables.
I added the Roomtable code to the top, but getting loads of errors:
The Create TABLE SQL construct or statement is not supported
Error source. Net Sqlclient data provider
Error message: Must declare the scalar variable "@StartDate".
CREATE TABLE #roomhours(
hour_number INTEGER NOT NULL PRIMARY KEY
,hour_description VARCHAR(5) NOT NULL
);
INSERT INTO #roomhours(hour_number,hour_description) VALUES (0,'12 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (1,'1 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (2,'2 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (3,'3 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (4,'4 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (5,'5 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (6,'6 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (7,'7 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (8,'8 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (9,'9 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (10,'10 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (11,'11 am');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (12,'12 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (13,'1 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (14,'2 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (15,'3 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (16,'4 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (17,'5 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (18,'6 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (19,'7 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (20,'8 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (21,'9 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (22,'10 pm');
INSERT INTO #roomhours(hour_number,hour_description) VALUES (23,'11 pm');
with cte as (
SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,
data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)
)
SELECT
r.hour_description
, cte.description
, cte.StartDateTime
, cte.EndDateTime
, cte.Name
FROM roomhours AS r LEFT OUTER JOIN
cte ON r.hour_number >= datepart(hh,cte.StartDateTime)
and r.hour_number < datepart(hh,cte.EndDateTime);
Hope you can help,
Thanks
September 21, 2015 at 9:51 am
try this
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' )
,cte as (
SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,
data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)
)
SELECT
r.hour_description
, cte.description
, cte.StartDateTime
, cte.EndDateTime
, cte.Name
FROM roomhours AS r LEFT OUTER JOIN
cte ON r.hour_number >= datepart(hh,cte.StartDateTime)
and r.hour_number < datepart(hh,cte.EndDateTime);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 22, 2015 at 10:18 am
Hi J,
Me again, this works great. As part of my back story, I mentioned PC logins also need to be included in the hour slots.
Is there a way to combine a tally (total number of PC logins which were being used i.e. logged in) in a column within your script...?
Expected output would be:
Hour, Booking, Number of PCs logged in
12 am
1 am
2 am
3 am
4 am
5 am,13
6 am
7 am
6 pm
7 pm,9
8 pm,10
9 pm
10 pm
11 pm
8 am induction, 20
9 am induction, 19
10 am induction, 16
11 am, 2
12 pm induction, 20
1 pm induction, 20
2 pm induction, 19
3 pm induction, 19
4 pm induction, 18
5 pm induction, 16
The Bookings information (which you helped me with were from a table on a different server from the pc logins info which is one a different server)
For the PC logins, here are the fields and query I have to grab the necessary info from the various tables:
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_Lab_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
This produces:
CampusID, Campus, BuildingID, RoomID, Room, LabID, Lab, Machine, Logontime, Logofftime, logonhour, logonffhour
1, Main, 23, Training Building, AB0.1, Exam Room, PC-1, 01/01/2015 09:00:00, 01/01/2015 11:45:34, 9, 11
Hope you can help
September 22, 2015 at 10:32 am
rkelly58 (9/22/2015)
Hi J,Me again, this works great. As part of my back story, I mentioned PC logins also need to be included in the hour slots.
Is there a way to combine a tally (total number of PC logins which were being used i.e. logged in) in a column within your script...?
Expected output would be:
Hour, Booking, Number of PCs logged in
12 am
1 am
2 am
3 am
4 am
5 am,13
6 am
7 am
6 pm
7 pm,9
8 pm,10
9 pm
10 pm
11 pm
8 am induction, 20
9 am induction, 19
10 am induction, 16
11 am, 2
12 pm induction, 20
1 pm induction, 20
2 pm induction, 19
3 pm induction, 19
4 pm induction, 18
5 pm induction, 16
The Bookings information (which you helped me with were from a table on a different server from the pc logins info which is one a different server)
For the PC logins, here are the fields and query I have to grab the necessary info from the various tables:
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_Lab_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
This produces:
CampusID, Campus, BuildingID, RoomID, Room, LabID, Lab, Machine, Logontime, Logofftime, logonhour, logonffhour
1, Main, 23, Training Building, AB0.1, Exam Room, PC-1, 01/01/2015 09:00:00, 01/01/2015 11:45:34, 9, 11
Hope you can help
hi again 😉
you have queries against tables that I cannot see...so lets just get some example data for both data sets and see if they can be combined.
this means you need to provide some sample scripts and suitable data that allows us to easily cut and paste into SSMS.
over to you ...please read http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/...we will also need your expert thoughts on what the results should be
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 23, 2015 at 7:22 am
Hi J, I've spent some time gathering the info for you guys, here goes, apologies if I've missed anything.
DataSet1 is to do with PC Logins,
Here is the SQL:
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
Here is the Sample Output for the above code:
DROP TABLE mytable;
CREATE TABLE mytable(
CampusID BIT NOT NULL PRIMARY KEY
,Campus VARCHAR(22) NOT NULL
,BuildingID INTEGER NOT NULL
,Building VARCHAR(19) NOT NULL
,RoomID INTEGER NOT NULL
,Room VARCHAR(19) NOT NULL
,LabID INTEGER NOT NULL
,Lab VARCHAR(9) NOT NULL
,Machine VARCHAR(9) NOT NULL
,LogonTime VARCHAR(19) NOT NULL
,LogoffTime VARCHAR(19) NOT NULL
,LogonHour INTEGER NOT NULL
,LogoffHour INTEGER NOT NULL
);
INSERT INTO mytable(CampusID,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',34,'Teaching Building',282,'1st Floor Open Area',326,'OB','TEA-12345','14/09/2015 14:40:39','14/09/2015 14:49:30',14,14);
INSERT INTO mytable(CampusID,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',19,'Sports Block',792,'S4.12',953,'Main Area','SPO-92260','04/09/2015 15:08:32','04/09/2015 15:20:37',15,15);
INSERT INTO mytable(CampusID,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',1,'Main Building',255,'M3.10',269,'Lab','MAN-12441','09/09/2015 16:06:34','09/09/2015 16:13:36',16,16);
INSERT INTO mytable(CampusID,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',34,'Teaching Building',282,'1st Floor Open Area',326,'OB','TEA-44606','14/09/2015 13:45:53','14/09/2015 17:57:59',13,17);
The table above has the logintime and logofftime, so this could be used in the same way as counting the hours the PC is logged off for.
i.e. PC TEA-44606 was logged in on hour 13 through till hour 17, so can we count each hour the pc was logged in for and add that to the Tally we used in your Bookings code?
The common field between the above table and the Bookings Dataset table is "Room"
You have already done all the work on Dataset 2 which was the Bookings Code you gave me.
So when both of these sets are combined, my Output should be like this
Parameters which the user picks:
Campus,
Building,
Room
Start Date
End Date is hidden and is the Start Date + 1 day (i'm doing this report so the user can only search one day for simplicity), maybe we can expand this to a date rang later because it might be more CPU intensive crunching a data range rather than a single day using the SQL script. In your opinion, is it easier to produce this report on a day basis or can it work just as quick using a date range? -i'm thinking the single day so that bookings and logins are displayed easier and quick to process...
The OUTPUT would be:
User picks Campus, Building, Room and Start Date
Output:
Hour, Booking, Number of PCs logged in
12:00 AM,
1:00 AM,
2:00 AM,
3:00 AM,
4:00 AM,
5:00 AM,
6:00 AM,
7:00 AM,
8:00 AM,
9:00 AM,Group Work, 20
10:00 AM, Group Work, 19
11:00 AM, 10
12:00 PM, 4
1:00 PM,
2:00 PM,
3:00 PM, Exam, 18
4:00 PM, Exam, 18
5:00 PM, 4
6:00 PM, 3
7:00 PM, 2
8:00 PM,
9:00 PM,
10:00 PM,
11:00 PM,
Hope this helps, happy to provide any more info.
Thanks,
September 23, 2015 at 7:44 am
edit...deleted
seems you were posting at same time as I was.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 23, 2015 at 7:53 am
sorry bout that, I wanted to see if the code was properly formatted before posting the rest of it.
No more edits to it now 🙂
September 23, 2015 at 7:56 am
rkelly58 (9/23/2015)
sorry bout that, I wanted to see if the code was properly formatted before posting the rest of it.No more edits to it now 🙂
me thinks it will best if we can have scripts (that work please!) for both sets of data....what you have just provided as dataset1 doesnt tie in with any previous data you have provided ???
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 23, 2015 at 8:09 am
DataSet1 Script (Logins)
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
DataSet2 Script (The Bookings one that you did)
WITH roomhours(hour_number, hour_description) AS (SELECT 0 AS Expr1, '12 am' AS Expr2
UNION ALL
SELECT 1 AS Expr1, '1 am' AS Expr2
UNION ALL
SELECT 2 AS Expr1, '2 am' AS Expr2
UNION ALL
SELECT 3 AS Expr1, '3 am' AS Expr2
UNION ALL
SELECT 4 AS Expr1, '4 am' AS Expr2
UNION ALL
SELECT 5 AS Expr1, '5 am' AS Expr2
UNION ALL
SELECT 6 AS Expr1, '6 am' AS Expr2
UNION ALL
SELECT 7 AS Expr1, '7 am' AS Expr2
UNION ALL
SELECT 8 AS Expr1, '8 am' AS Expr2
UNION ALL
SELECT 9 AS Expr1, '9 am' AS Expr2
UNION ALL
SELECT 10 AS Expr1, '10 am' AS Expr2
UNION ALL
SELECT 11 AS Expr1, '11 am' AS Expr2
UNION ALL
SELECT 12 AS Expr1, '12 pm' AS Expr2
UNION ALL
SELECT 13 AS Expr1, '1 pm' AS Expr2
UNION ALL
SELECT 14 AS Expr1, '2 pm' AS Expr2
UNION ALL
SELECT 15 AS Expr1, '3 pm' AS Expr2
UNION ALL
SELECT 16 AS Expr1, '4 pm' AS Expr2
UNION ALL
SELECT 17 AS Expr1, '5 pm' AS Expr2
UNION ALL
SELECT 18 AS Expr1, '6 pm' AS Expr2
UNION ALL
SELECT 19 AS Expr1, '7 pm' AS Expr2
UNION ALL
SELECT 20 AS Expr1, '8 pm' AS Expr2
UNION ALL
SELECT 21 AS Expr1, '9 pm' AS Expr2
UNION ALL
SELECT 22 AS Expr1, '10 pm' AS Expr2
UNION ALL
SELECT 23 AS Expr1, '11 pm' AS Expr2), cte AS
(SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT rdowner.V_ACTIVITY.Description, rdowner.V_ACTIVITY_DATETIME.StartDateTime, rdowner.V_ACTIVITY_DATETIME.EndDateTime,
rdowner.V_LOCATION.Name
FROM rdowner.V_ACTIVITY INNER JOIN
rdowner.V_ACTIVITY_DATETIME ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
rdowner.V_ACTIVITY_LOCATION ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
rdowner.V_LOCATION ON rdowner.V_ACTIVITY_LOCATION.LocationId = rdowner.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room))
SELECT r.hour_description, cte_1.Description, cte_1.StartDateTime, cte_1.EndDateTime, cte_1.Name, r.hour_number
FROM roomhours AS r LEFT OUTER JOIN
cte AS cte_1 ON r.hour_number >= DATEPART(hh, cte_1.StartDateTime) AND r.hour_number < DATEPART(hh, cte_1.EndDateTime)
ORDER BY r.hour_number
Both of these work separately.
Both Dataset Tables are on separate Databases on different servers
September 23, 2015 at 8:36 am
rkelly58 (9/23/2015)
DataSet1 Script (Logins)
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
DataSet2 Script (The Bookings one that you did)
WITH roomhours(hour_number, hour_description) AS (SELECT 0 AS Expr1, '12 am' AS Expr2UNION ALL
SELECT 1 AS Expr1, '1 am' AS Expr2
UNION ALL
SELECT 2 AS Expr1, '2 am' AS Expr2
UNION ALL
SELECT 3 AS Expr1, '3 am' AS Expr2
UNION ALL
SELECT 4 AS Expr1, '4 am' AS Expr2
UNION ALL
SELECT 5 AS Expr1, '5 am' AS Expr2
UNION ALL
SELECT 6 AS Expr1, '6 am' AS Expr2
UNION ALL
SELECT 7 AS Expr1, '7 am' AS Expr2
UNION ALL
SELECT 8 AS Expr1, '8 am' AS Expr2
UNION ALL
SELECT 9 AS Expr1, '9 am' AS Expr2
UNION ALL
SELECT 10 AS Expr1, '10 am' AS Expr2
UNION ALL
SELECT 11 AS Expr1, '11 am' AS Expr2
UNION ALL
SELECT 12 AS Expr1, '12 pm' AS Expr2
UNION ALL
SELECT 13 AS Expr1, '1 pm' AS Expr2
UNION ALL
SELECT 14 AS Expr1, '2 pm' AS Expr2
UNION ALL
SELECT 15 AS Expr1, '3 pm' AS Expr2
UNION ALL
SELECT 16 AS Expr1, '4 pm' AS Expr2
UNION ALL
SELECT 17 AS Expr1, '5 pm' AS Expr2
UNION ALL
SELECT 18 AS Expr1, '6 pm' AS Expr2
UNION ALL
SELECT 19 AS Expr1, '7 pm' AS Expr2
UNION ALL
SELECT 20 AS Expr1, '8 pm' AS Expr2
UNION ALL
SELECT 21 AS Expr1, '9 pm' AS Expr2
UNION ALL
SELECT 22 AS Expr1, '10 pm' AS Expr2
UNION ALL
SELECT 23 AS Expr1, '11 pm' AS Expr2), cte AS
(SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT rdowner.V_ACTIVITY.Description, rdowner.V_ACTIVITY_DATETIME.StartDateTime, rdowner.V_ACTIVITY_DATETIME.EndDateTime,
rdowner.V_LOCATION.Name
FROM rdowner.V_ACTIVITY INNER JOIN
rdowner.V_ACTIVITY_DATETIME ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
rdowner.V_ACTIVITY_LOCATION ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
rdowner.V_LOCATION ON rdowner.V_ACTIVITY_LOCATION.LocationId = rdowner.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room))
SELECT r.hour_description, cte_1.Description, cte_1.StartDateTime, cte_1.EndDateTime, cte_1.Name, r.hour_number
FROM roomhours AS r LEFT OUTER JOIN
cte AS cte_1 ON r.hour_number >= DATEPART(hh, cte_1.StartDateTime) AND r.hour_number < DATEPART(hh, cte_1.EndDateTime)
ORDER BY r.hour_number
Both of these work separately.
Both Dataset Tables are on separate Databases on different servers
I know what the queries are...what you havent given is example data for both query outputs...with data that actually can be tied together in someway...ie matching rooms/matching dates......what we have so far doesnt do this.
once you do this and provide your expected results based on the sample data....we stand a good chance
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply