-
Notifications
You must be signed in to change notification settings - Fork 5
/
Patrons checking out particular items during date range.sql
65 lines (30 loc) · 1.57 KB
/
Patrons checking out particular items during date range.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
--If you can get the infromation you need from the Find Tool, you can use this, collecitonID is specific to yourlibrary and ActionTakenID is checkouts:
select distinct irh.PatronID
from polaris.ItemRecordHistory irh (nolock)
inner join polaris.CircItemRecords ci (nolock)
on irh.ItemRecordID = ci.ItemRecordID
where irh.TransactionDate between '10/1/2013' and '12/31/2013'
and irh.PatronID is not null
and irh.ActionTakenID =13
and ci.AssignedCollectionID=6
--I built this with Simply Reports and added to my query above, so if you have access to your server, and SQL server management Studio you can use this:
select pr.Birthdate, pr.PatronFullName ,addr.StreetOne,addr.StreetTwo,pos.PostalCode,pos.City,pos.county
from Polaris.PatronRegistration pr with (nolock)
inner join Polaris.Patrons p with (nolock)
on (pr.PatronID = p.PatronID)
left join Polaris.PatronAddresses pa with (nolock)
on (pr.PatronID = pa.PatronID and pa.AddressTypeID = 2 )
left join Polaris.Addresses addr with (nolock)
on (pa.AddressID = addr.AddressID)
left join Polaris.PostalCodes pos with (nolock)
on (addr.PostalCodeID = pos.PostalCodeID)
inner join(
select distinct irh.PatronID
from polaris.ItemRecordHistory irh (nolock)
inner join polaris.CircItemRecords ci (nolock)
on irh.ItemRecordID = ci.ItemRecordID
where irh.TransactionDate between '10/1/2013' and '12/31/2013'
and irh.PatronID is not null
and irh.ActionTakenID =13
and ci.AssignedCollectionID=6
) sub on sub.patronid=p.Patronid