forked from GMILCS/Polaris-TSQL
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Hourly circulation by user.sql
101 lines (47 loc) · 2.14 KB
/
Hourly circulation by user.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
select pu.Name,
--torg.name as TransactionBranchName,
--tstc.TransactionSubTypeCodeDesc as CheckoutType,
DATEPART(yy,th.TranClientDate) as 'Year',
DATEPART(mm,th.TranClientDate) as 'Month',
DATEPART(dd,th.TranClientDate) as 'Day',
DATEPART(dw,th.TranClientDate) as 'WeekDay',
case
when DATEPART(dw,th.TranClientDate) = 1 then 'Sunday'
when DATEPART(dw,th.TranClientDate) = 2 then 'Monday'
when datepart(dw,th.TranClientDate) = 3 then 'Tuesday'
when datepart(dw,th.TranClientDate) = 4 then 'Wednesday'
when datepart(dw,th.TranClientDate) = 5 then 'Thursday'
when datepart(dw,th.TranClientDate) = 6 then 'Friday'
when datepart(dw,th.TranClientDate) = 7 then 'Saturday'
end as 'Day',
DATEPART(hh,th.TranClientDate) AS 'Hour',
count(distinct th.transactionid) as Total
from PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
left outer join PolarisTransactions.Polaris.TransactionDetails td (nolock)
on (th.TransactionID = td.TransactionID)
inner join Polaris.Polaris.Organizations torg (nolock)
on (th.OrganizationID = torg.OrganizationID)
inner join PolarisTransactions.Polaris.TransactionSubTypeCodes tstc with (nolock)
on (td.TransactionSubTypeID = tstc.TransactionSubTypeID) and (td.numvalue = tstc.TransactionSubTypeCode)
INNER JOIN Polaris.Polaris.PolarisUsers pu (NOLOCK)
ON pu.PolarisUserID = th.PolarisUserID
where th.TransactionTypeID = 6001 and td.TransactionSubTypeID = 145
and th.TranClientDate between @StartDate and @EndDate + ' 23:59:59'
and th.OrganizationID in (@Branch)
and tstc.TransactionSubTypeCode in (15,23)
Group by pu.Name,
--tstc.TransactionSubTypeCodeDesc,
DATEPART(yy,th.TranClientDate),
DATEPART(mm,th.TranClientDate),
DATEPART(dd,th.TranClientDate),
DATEPART(dw,th.TranClientDate),
DATEPART(hh,th.TranClientDate)
Order by
--tstc.TransactionSubTypeCodeDesc,
--DATENAME(dw,th.TranClientDate),
DATEPART(yy,th.TranClientDate),
DATEPART(mm,th.TranClientDate),
DATEPART(dd,th.TranClientDate),
DATEPART(dw,th.TranClientDate),
DATEPART(hh,th.TranClientDate),
pu.name