October 7, 2015 at 8:59 pm
deleting topic....
October 7, 2015 at 9:55 pm
If this is beginner homework (which I doubt!), then someone is really yanking your chain.
I would probably write a stored procedure to link up the Login to the Logout times... something like this:
SELECT Acct
, EventDate
, LAG(EventDate,1) OVER (PARTITION BY Acct ORDER BY EventDate) AS PrevEvent
, DATEDIFF(minute,LAG(EventDate,1) OVER (PARTITION BY Acct ORDER BY EventDate),EventDate) AS TimeBetween
FROM
(SELECT [Account] AS Acct
, [Time] AS EventDate
, CASE WHEN EventType IN ('Agent_Connected', 'Agent_Reconnected') THEN 'Login' ELSE 'Logout' END AS EventTypeGroup
FROM HVD_UTIL_Temp_Table) x;
I renamed a couple of columns because EVENT, and TIME are reserved words in T-SQL...
Hopefully this gets you going in the right direction.
October 7, 2015 at 10:57 pm
deleting topic...
October 8, 2015 at 1:38 am
This should work for SQL2008.
Note: I cheated and created a table of dates, so that I could force the existence of every date in the range, regardless of whether there were logins/logouts on the date in question. Here's the Calendar table code:
CREATE TABLE [dbo].[Calendar](
[TheDate] [date] NOT NULL,
CONSTRAINT pkCalendar PRIMARY KEY (TheDate));
/* Create and populate Calendar */
DECLARE @ThisDate DATE = '16-Sep-2015';
WHILE @ThisDate<='29-Sep-2015'
BEGIN
INSERT INTO Calendar(TheDate) VALUES (@ThisDate);
SET @ThisDate = DATEADD(day,1,@ThisDate);
END
Now that we have a Calendar, we can use it in our query...
SELECT TheDate, COUNT(Account) As LoggedInOnDate
FROM
(SELECT DISTINCT TheDate, Account
FROM Dates d LEFT JOIN
(SELECT Account
, [Time]
, CONVERT(VARCHAR(10),EH.[Time],111) AS EventDate
, [EventType]
, CASE WHEN [EventType] IN ('Agent_Disconnected','Agent_Ended') THEN 'OUT' ELSE 'IN' END AS LoginType
FROM HVD_UTIL_Temp_Table AS EH) u
ON d.TheDate = u.EventDate) x
GROUP BY TheDate
ORDER BY TheDate;
The CONVERT() nonsense is to strip the times off the dates in the Login/Out data. Not pretty, but I'm not terribly awake, or as good at T-SQL as Eirikur, who I'm sure will laugh really hard at this and rewrite it the correct way that's orders of magnitude faster.
To answer your question, No, I don't think you can do this purely in ReportBuilder with the tables you have at present. You're missing the Calendar table, and the solution won't work without it. You can build execute the above query in ReportBuilder and build your report once you have the Calendar table, though. (Maybe you could create a union query of all the dates you need, but that would be a serious hassle!)
Hope this gets you started. Enjoy the journey, young Jedi!
Pieter
October 13, 2015 at 7:10 pm
deleting topic...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply