This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT max(cntOn) | |
from | |
( | |
SELECT logontime, count(logontime) cntOn --25 rows | |
from | |
--q3:select all entries with logon - logof > 0 | |
(SELECT l1.logontime,l2.logofftime | |
FROM | |
--SELECT entries both with LOGON and LOGOFF |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--maxOff - find out max of LOGOFF datetime counts | |
SELECT max(cntOff) maxLogOffDatetime------------------------------------------------------------------------------------------------------max cnt logofftime - maxOff (1) | |
FROM | |
( -- l1 and l2 select all entries with logOns and logOffs | |
SELECT logofftime, count(logofftime) cntOff --118 rows---------------------------------------------------------------------count logofftime >0 (2) | |
from | |
--q3:select all entries with logon - logof > 0 | |
--l1 - all logOns | |
(SELECT l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3)---q3 START | |
FROM |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
---select * for max datetime for logons | |
Select * from puzzSessions where ActionName='LOGON' and Timestamp=( | |
SELECT logontime --103 rows---------------------------------------------------------------------get date with maximum count | |
from | |
--q3:select all entries with logon - logof > 0 | |
--l1 - all logOns | |
(SELECT l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3) | |
FROM | |
--SELECT entries both with LOGON and LOGOFF | |
(SELECT Session_ID, TIMESTAMP logontime -----------------------get logOn (where has logOff) - l1 (4) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
---select * for max datetime for logoffs | |
Select * from puzzSessions where ActionName='LOGOFF' and Timestamp=( | |
SELECT logofftime | |
from | |
--q3:select all entries with logon - logof > 0 | |
--l1 - all logOns | |
(SELECT l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3) | |
FROM | |
--SELECT entries both with LOGON and LOGOFF | |
(SELECT Session_ID, TIMESTAMP logontime -----------------------get logOn (where has logOff) - l1 (4) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*The most simultaneous LOGONNs and LOGOFFs for sessions, which have difference between LOGON and LOGOFF more than 0 hours | |
2 main sub-queries: | |
1) The most simultaneous LOGONs | |
UNION all | |
2) The most simultaneous LOGOFFs | |
*/ | |
--The most simultaneous LOGONs | |
Select * from puzzSessions where ActionName='LOGON' and Timestamp=( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
QuartersMap: | |
MAPPING LOAD | |
rowno() as Month, | |
'Q' & Ceil (rowno()/3) as Quarter | |
AUTOGENERATE (12); | |
//Create min and max date (max=current date for the assignment) | |
Temp: | |
load date(minDate,'YYYY-MM-DD') AS minDate, date(maxDate,'YYYY-MM-DD') AS maxDate inline [ | |
minDate, maxDate |
OlderNewer