Skip to content

Instantly share code, notes, and snippets.

View StaniTr's full-sized avatar

Stanislava StaniTr

  • Bulgaria
View GitHub Profile
@StaniTr
StaniTr / TASK
Created January 30, 2015 16:31
max LOGON Date
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
@StaniTr
StaniTr / max(OFF)
Created February 2, 2015 15:26
max LOGOFF Datetime
--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
@StaniTr
StaniTr / LogON
Created February 2, 2015 15:44
LogON
---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)
@StaniTr
StaniTr / LogOFF
Created February 2, 2015 15:50
LogOFF
---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)
@StaniTr
StaniTr / PuzzleSolution
Last active August 29, 2015 14:14
PuzzleSolution - MS SQL Script
/*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=(
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