►
From YouTube: TTEOT Training Video - S1E12 - SQL: The Dashboard Finale
Description
You've watched the life-sucking training videos that led you step-by-step through each detail of George's twisted mind. Now watch the video that brings it all together in a slow, unrelenting madness as you take control of Koha and become the master of SQL and item records. (Maniacal laughing inserted here.)
Referenced code: https://wiki.koha-community.org/wiki/SQL_Reports_Library
Additional Resources: https://will1410.github.io/notebook/presentations/itemdashboard.html
Music: https://www.bensound.com
A
A
A
A
A
A
So
let
me
set
up
three
of
these
to
run
so
you
know
at
my
library
we
have
for
my
cohort.
We
have
51
libraries
using
the
same
cohort
and
we
have
staff
members
in
some
libraries
that
have
master's
degree
in
library,
science
they're,
the
director
they've
got.
You
know
our
biggest
libraries
in
the
town
of
about
35
40,
000
people.
Our
smallest
library,
though,
is
in
a
town
of
about
less
than
150
people.
A
A
I
can
scan
the
item
and
try
and
find
it
just
doing
the
normal
search
or
I
can
run
a
report
to
see
if
it's
in
the
deleted
items
table
there's
a
whole
bunch
of
different
reports
I
can
run
to
see
you
know.
Is
this
thing
in
transit
from
one
place
to
another,
hasn't
been
bouncing
around
from
library
to
library,
because
it's
getting
misrouted
there's
all
kinds
of
things
I
can
do
with
it
with
just
a
barcode
number,
but
if
what
I?
A
What
I
wanted,
though,
is
you
know
to
find
all
those
reports
I've
got
to
go
searching
usually
so
somebody
calls
it
and
they
said
this
barcode
number
doesn't
work
right
or
there's
a
problem.
With
this
item
there's
like
20
different
places,
I
can
go
to
find
out
information
about
it
and
I
wanted
one
place
where
I
could
just
plug
in
a
barcode
number
and
get
a
result.
A
So
this
report,
if
I
plug
in
a
barcode
number
for
an
item,
that's
currently
in
the
catalog,
I
get
a
result
and
it's
a
not
the
fastest
report
in
the
world
because
it's
doing
a
lot
of
things
right.
But
what
I
wanted
is
you
know
if
I
were
to
go
to
the
item
record
and
the
bibliographic
record,
I
would
get
all
this
information,
but
here
I
have
it
in
a
way
that
I
can
just
cut.
A
A
Does
it
have
a
not
for
loan
status?
This
one
is
damaged,
lost
and
withdrawn
because
I
wanted
some
dating
in
there
for
the
for
an
example,
so
it
was
damaged
in
shipment
today
it
was
lost
today
and
it
was
withdrawn
today,
but
then
I've
also
got
if
it's
checked
out
there'll
be
a
link.
Well,
if
it's
in
transit
there'll
be
transit
information
from
the
branch
transfers
table.
A
A
A
If
it
was
a
title
level
request
or
if
it
was
a
item
level
request,
so
I
get
all
those
things
in
one
place.
So
if
somebody
calls
me
and
gives
me
a
barcode
number,
I
don't
have
to
go
hunt
for
reports.
I've
just
got
them
all
right
there
and
then,
if
I've,
if
I
use
this
on
a
if
I
scan
in
the
same
report,
if
I
scan.
A
If
I
enter
a
barcode
number
and
the
item
has
been
deleted,
that
also
gives
me
a
result
and
I've
got
this
banner
at
the
top.
That
says,
it's
been
deleted
and
it
does
the
same
thing.
It
gives
me
all
the
item.
It
gives
me
slightly
different
item
information
because
there's
less
information,
I
need
to
know
if
it's
been
deleted,
but
and.
B
B
How
are
you
pulling
the
I'm
just
out
of
curiosity?
I
was
I'm
just
following
along
and
realizing
you've
said
at
the
bottom
that
this
was
deleted
from
the
catalog
within
the
past
13
months.
How
are
you
even
figuring
that
out,
because
the
deleted
table
doesn't
record
when
it
was
deleted?
It
only
has
the
last
time
stamp.
A
So
if
it's,
if
it's
not
in
this,
won't
look
for
things
that
are
not
in
the
deleted
items
or
the
items.
So
I
know
this
was
in
the
last
13
months
because
because
it's
still
there
there's
still
data
there,
and
I
picked
this
barcode
number
out
specifically
because
because
I
knew
it
was
still
there,
I
went
on
a
hunt
a
couple
of
weeks
ago
we
talked
about.
We
did
another
one
where
I
used
a
different
barcode
number
and.
B
A
A
A
B
A
A
That
was
the
whole
idea
about
this.
I
spent
hours
and
hours
and
hours
building
this
report
over
a
period
of
about
two
or
three
years,
and
the
whole
idea
was
that,
if
I
put
this
all
together
and
do
it
this
way,
if
I
spend
100
hours
on
this,
that'll
save
me
like
two
minutes
each
time
I
I
get
one
of
these
phone
calls,
and
so
this
is
purely
out
of
laziness.
I
spent
a
ton
of
time
working
on
this
purely
out
of
laziness
out.
A
A
A
And
so
what
I
thought
I
would
do
today
is
just
walk
through
the
steps
and
that's
what
I've
got
on
the
website
and
the
the
first
thing
I
say
after
that
is
yes,
I
know
it's
a
big
report,
there's
a
lot
going
on
here,
and
so
I've
got
each
step
laid
out.
How
I
got
to
this
point,
and
so
the
first
step
I'll
run
that
in
your
window,
what.
B
A
A
A
report-
that's
only
looking
at
items
the
items
table
and
it's
not
concatenating
anything.
So
I'm
getting
everything
all
of
this
data
that
I'm
interested
in
it's
being
presented
as
a
long
table
just
the
raw
data.
This
is
just
the
raw
data,
and
so
that's
this
is
usually
the
way
I
build.
A
report
is
the
first
thing
I
want
to
do
is
I
want
to
get.
I
want
to
figure
out.
What's
the
raw
data
I
need,
and
then
I
start
doing
things
to
clean
it
up,
and
so
I
think
in
step
two.
A
A
So
what
the
sub
query
is
doing
is
it's
just
generating
a
list
of
all
of
the
collection
codes
and
I've
got
their
authorized
value
in
this
column
and
their
description
in
the
other
column,
and
so
essentially,
what
I'm
doing
is
I'm
saying
every
time
that
it
sees
prof
call
it's
going
to
replace
it
with
professional
collection,
okay,
and
so
that's
what
gets
us.
You
know
professional
collection
instead
of
instead
of
the
code-
and
you
know
the
collection
codes
aren't
really
horrible.
I
do
have
one
called
xxx.
That
is
not
pornography.
A
So,
in
the
next
step
I've
got,
you
know,
I
show
what
it
looks
like
when
all
of
these
things
have
all
of
those
authorized
values
all
of
those
codes.
This.
What
this
next
step
does
is.
It
shows
all
of
them
as
a
description
instead
of
as
codes
so
damaged
in
shipment
lost
and
withdrawn
instead
of
one
or
two
one
and
five
or
whatever
it
is.
A
A
A
So
up
to
now,
I've
been
putting
the
status
and
the
status
date
in
two
separate
columns,
and
so
the
next
thing
I
do
is
I
concatenate
them
together.
So
we've
got
damaged
in
shipment
and
the
date
and
lost
and
the
date
and
drawn
in
the
date,
and
that
way
I'm
trying
to
I'm
starting
to
do
things
to
make
it
look.
A
A
A
There's
yeah
report
number
seven:
this
is
gonna,
do
what
we
talked
about
in
the
video
about
extract
mark.
So
if
you
go
to
this
website
for
this,
how
to
do
this?
I've
got
links
to
all
of
the
videos
that
we've
done
so
far
so
step
four
involves
using
concatenation
and
if
statements,
and
so
there's
a
link
here
to
that
video.
So
if
you
haven't
seen
that
yet
and
the
step
three
has
got
the
sub
queries
and
so
there's
a
link
in
there
to
the
video.
A
A
Website
you're
going
to
see
all
the
things
that
we've
been
talking
about
over
time.
So
so
in
this
one
in
the
first
one
we've
got,
the
title
is
expect
more
and
that's
just
the
245
subfield
a
from
the
mark
record.
But
here
I'm
able
to
add
in
if
it
has
a
b
or
an
n
or
a
p
field,
I'm
putting
all
of
those
together
into
the
report
here,
so
that
the
title
is
not
just
the
not
just
the
title.
It's
going
to
have
all
the
parts
of
the
title
right
and
that's
something.
A
A
A
Well,
no,
we
did
talk
about
this.
Excuse
me
there's
a
lot
going
on
in
this
report
and
it's
hard
to
keep
it
straight.
What
I'm
doing
here
is
in
order
to
get
the
in
the
item
record,
you
can't
tell
you
can
tell
if
an
item
is
checked
out
or
not,
but
you
can't
tell
who
it's
checked
out
too,
and
so
this
part
of
the
report
involves
doing
a
sub
query.
A
Issues
table
then
that's
where
you're
going
to
find
the
borrower
number
right.
So
this
part
of
the
report
takes
a
sub
query
that
looks
at
the
issues
table
and
it
connects
that
where
the
issues
item
number
equals.
The
item
item
number
and
then
up
here,
I'm
concatenating
that
borrower
number
from
the
issues
table
into
a
link
so
that
if
it
is
checked
out,
it's
going
to
take
us
there.
But
then.
A
B
A
So
the
subquery
is
getting
all
of
the
data
from
the
from
the
branch
transfers
table
where
the
items
haven't
arrived
and
then
actually
in
the
report,
I've
got
an
if-then
statement
that
says:
if
the
branch
transfer
data
is
null,
then
it's
not
gonna.
If
there's
no
branch
transfer
result,
then
it's
gonna
give
me
a
dash,
but
if
it
is
in
the
branch
transfers,
it's
gonna
show
me
where
it
was
shipped
from
where
it
was
shipped
to
and
when
it
was
shipped.
A
A
Link
to
the
borrower
account,
so
I'm
concating,
I'm
doing
a
concatenation
where
I'm
saying
put
this
text
and
the
previous.
If
then
statement
with
the
link
in
it,
and
so
essentially
what
I'm
saying
is
that
whenever
I
have
that
link
to
the
borrower,
if
there
is
a
link
to
the
borrower
there,
it's
going
to
have
a
piece
of
text
in
front
of
it.
That
says,
link
to
the
borrower.
A
A
A
You
can
take
the
parts
of
the
report,
you
know
you
can
take
this
this
url
and
put
it
in
another
browser
and
log
into
coho
and
enter
that
url
and
it's
going
to
run
the
report
yeah,
and
so
all
we
have
to
do
is.
Can
cap
is
concatenate
in
everything,
from
the
slash
before
the
cgi
bin
up
to
the
place
where
you
want
the
parameter.
A
A
B
B
I
can
add
that
information,
but
not
everybody's,
going
to
want
to
see
that
information
in
the
report
and
I
thought
I
could
duplicate
this
report,
but
I
thought
why
not
just
create
a
link
to
that
information
for
that
specific
item.
I
thought
I
hadn't
done
that
before
you
know
I
can
link
to
that
and
I
can
include
that
parameter.
So
it
goes
to
that
item
and
expands
on
that
information.
So
it's
it's
kind
of
like
a
report.
Drill
down
it's
really
nice
yeah.
A
B
B
A
Cirque
history
there's
a
possibility.
I
might
want
to
see
the
history
in
the
action
logs.
I
might
want
to
see
the
entrance
in
history.
I
might
want
to
see
the
request
history
and
that's
so
much
to
put
in
one
report.
It
might
not
even
run
if
I
were
to
actually
try
to
load
that
wall
directly
and
report
it.
It
would
probably
fail.
A
A
A
A
So
I've
got
you
know
the
home
library,
colon,
current
library
called
permanent
location
colon.
So
eventually,
when
I
plug
this,
also
it's
just
that
one
easy
layout
I've
got
all
of
the
all
of
the
things
are
labeled
as
to
what
they
are,
and
this
is
you
know,
a
lot
of
concatenation,
because
I'm
concatenating
this
string
home
library,
up
against
items,
home
branch
and
current
library
up
against
items
holding
branch
yeah
and
for
you
know
for
title,
since
I've
got
the
ex
the
biblio
metadata
here,
I've
got
I'm.
A
Is
the
the
reason
that
this
step
I
feel
is
necessary
is
because
you
know,
like
I
said,
I'm
trying
to
get
to
a
point
where
I
can
copy
and
paste
this
data
if
I
need
to
in
an
email-
and
it
makes
sense
to
the
people
that
are
getting
it?
Who
might
not?
You
know,
think
about
you
know
when
they
see
the
statuses
they
they're
going
to
need
something
there
that
tells
them
it's
a
damage,
status
or
withdrawn
status,
because
the
like,
I
said,
the
level
of
experience
among
the
staff
varies
yeah.
A
A
B
A
A
And
the
way
that
I
do,
that
is
actually
pretty
simple.
I
know
that
I
want
a
section
break
between
like
this.
This
is
kind
of
the
item
data
about
you
know.
These
are
the
things
about
the
item.
It's
kind
of
like
the
item
metadata
and
then
I
get
down
to
like
the
items,
statistic,
data
and
the
statuses,
and
so
all
I
do
is
I
stick
a
line
break
before
checkouts
there
you
go
okay,.
A
And
I
I
threw
some
screenshots
in
there,
so
people
can
get
an
idea
of
what's
going
on,
and
so
then
the
next
thing
I
wanted
to
do.
There
are
some
libraries
that
I
tell
them
how
to
run
this.
I
should
point
out
this
report
to
them.
I
show
them
how
to
run
it,
and
so
that
means
I'm
not
the
only
one.
That's
running
this
report
and,
like
I
said
varying
staff
members
varying
levels
of
experience,
everybody's
different.
A
A
Add
another
piece
of
html
here
that
says
this
item
is
currently
in
the
catalog
and
I've
got
all
the
html
there
to
I've
got
the
color
of
the
text
is
white.
The
background
color
is
this
color
green
I've
got
it
aligned
in
the
center
and
I've
got
the
text
I
want,
and
I've
just
got
that
copied
at
the
beginning
and
at
the
end
and.
A
You
know
andrew
at
bywater
had
for
a
while
when
he
was
experimenting
with
sound.
You
would,
I
don't
remember
what
it
was,
but
he
would
open
something
and
it
would
play
part
of
the
batman
theme
from
the
1960s
adam
west,
batman
tv
show,
so
this
report
once
once
I
get
to
this
point,
we're
at
a
spot
where,
essentially
this
is
done
as
far
as
items
that
are
stolen,
the
system
goes
and
I've
got
you
know
I
I
wrote
these
reports
on
my
system
said
this
top
one
is
a
barcode.
A
A
A
B
A
Where
I
can
plug
in
a
deleted
item
and
get
a
result
also,
so
that's
what
the
rest
of
this
does.
This
is
the
section
that
goes
through
and
looks
for
deleted
items,
and
I
did
it
almost
exactly
the
same
way
that
I
did
the
other
one
where
I
start
off
with
you
know,
this
is
getting
the
basic
data
just
from
the
deleted
items
table.
A
A
A
B
So
talking
about
coalesce
real,
quick,
I
I
I
love
coalesce.
You
opened
my
eyes
to
to
a
great
function
that
I
didn't
know
about
before,
but
I
was
wondering
when
you,
you
did
the
first
section
for
an
item
that
exists
that
isn't
deleted
right.
The
section
for
the
deleted
items.
Did
you
just
add
duplicate
code
to
handle
that,
or
are
you
using
any
kind
of
coalesce
to
determine
you
know
if
there's
something
from
an
items
table
put
it
here
if
not
coalesce,
to
a
deleted
items?
If
not,
then.
A
A
B
A
And
that's
where
the
coalesce
comes
in,
because
it's
saying
you
know
if
deleted
items,
if
there's
no
connection
between,
if
there's
nothing
found
in
biblio,
where
biblio
number
equals
deleted
items,
biblio
number
and
since
it's
a
left
join.
Essentially
it's
looking
at
deleted
items.
Bibliomy
are
deleted
biblio
and
biblio
at
the
same
time,
but
it's
only
going
to
give
a
result
and
here
by
coalescing
them
it's
getting
where
biblioauthor
is
known,
then
it's
going
to
coalesce
into
deleting
biblio
author.
Does
that
make
sense,
yeah.
B
A
A
A
A
A
There
are
a
lot
fewer
reports
and
links
in
this
work
in
the
parts
that
for
the
deleted
items
and
that's
because
most
of
those
reports,
like
the
items,
request,
history
and
the
in
transit
history
and
all
of
those
things
once
the
item's
been
deleted.
All
that
data
is
gone.
Yeah
there's
no
way
you
can
run
a
branch
transfers
report.
An
item
that's
been
deleted,
because
when
you
delete
the
item
it
kills.
A
A
B
B
A
A
A
A
A
If
the
item
is
live,
that's
what
this
top
report
is
for
and
then
in
the
middle
I've
got
this
union
and
everything
from
the
deleted
items
table.
That's
everything
from
here
down
and
the
way
that
they
both
know.
What
the
barcode
number
is
is
cohab
will
do
this
if
you
have
enter
item
barcode
number
in
double
angle
brackets,
if
you
have
that
at
two
places
in
one
report
and
they're,
both
spelled
exactly
the
same.
A
A
A
B
To
do
that,
yeah
it
was
it
was,
it
worked,
it
was
tricky
to
get
it
to
work
and
every
version
that
came
out
of
quahog
something
would
break
he'd
have
to
fix
it.
It
was
like
yeah,
it
was.
It
was
a
nightmare
to
try
and
keep
up
with.
This
was
probably
one
of
the
single
most
important
updates.
They
did
to
reports.
Was
you
know,
yeah
doing
this,
so
you
don't
have
to
enter
the
same
information
twice
and
you
don't
have
to
mess
with
variables.
A
A
You
know
making
it
so
that
when
you
do
have
the
double
angle
brackets
and
everything
in
between
them
is
spelled
exactly
the
same,
and
it
recognizes
that
that's
the
same
variable
that
helped
a
lot
and
it
helped
even
more
when
the
first
release
that
was
in
there
was
a
bug
in
it
and
fix
it
when
they
fixed
that
bug
that
made
it
perfect.
So.
A
A
B
A
All
I
got
to
do
is
plug
the
barcode
number
in
there
and
I
get
a
result
and
the
result
tells
me
everything.
I
need
to
know
about
an
item
and
it
makes
it
easy
to
find
all
of
that
stuff
in
one
place
and
if
I
need
to
send
this
data
in
an
email,
this
is
a
lot
easier
to
cut
and
paste
into
an
email
than
this
is.
B
A
B
B
A
B
A
B
A
Would
say
again,
you
know
if
you
go
the
reports
that
I
link
to
all
the
reports
that
I
use
are
available
if
I
can
find
the
right
spot
yeah.
All
of
this
click
for
report
click
here
for
these
reports.
This
will
take
you
to
the
github
site
that
I
use
all
of
the
sql
that
we
use
at
nipples
I
store.
I,
I
run
a
report
to
put
all
of
this
stuff,
essentially
to
back
it
up
on
github.
A
I
put
a
lot
of
stuff
in
there,
but
there's
some
stuff.
I
don't
use
because
I
do
use
some
special,
some
specially
created
authorized
values
in
our
reports
and
if
you
use
those,
if
I
have
a
report
that
uses
those,
it
can't
really
be
it's
hard
to
transfer
to
another
library.
If
you
don't
have
that
same
authorized
values
table
got
it.
That
was
a
presentation
I
did
at
the
conference
at
the
koha
us
conference
in
coeur
d'alene
that
maybe
somewhere
down
the
road.
A
Video
on
it,
but
it's
it's
an
easy
way.
You
know
for
us,
we
have
libraries
and
four
of
them
are
with
one
are
in
one
district,
so
they
want
data.
Sometimes
they
want
data
just
for
donovan,
county
elwood
branch
or
just
for
donovan
county
highland
branch,
but
the
because
I've
got
them
because
all
of
their
location,
their
library
code,
starts
with
don.
I
I've
got
an
authorized
values
table
that
is
essentially
a
duplicate
of
all
of
the
branch
codes,
but
then
there
is
one
of
those
is
don
I
wild
card.
A
Because
I've
got
the
branch
codes
so
that
the
first
several
letters
of
the
branch
code
match
and
only
match
their
libraries
nice.
So
it's
harder
to
do
with
like
when
I
worked
at
layton
county
library,
the
codes
for
layton
county
libraries
were
bov,
dea,
j-u-l,
m-o-s
and
etc.
So
none
of
them
had
any
common
pieces
to
them.
So
that
made
it
a
lot
harder
to
do
there.
A
B
B
B
A
B
B
Again
I
have
I've
learned
a
lot
you've
added
to
my
arsenal
for
report
writing
and
I
am
looking
forward
to
using
those
tools.
I've
already
been
using
some
of
those
tools
and
enjoying
that.
So
thank
you
so
much
for
everything
that
you've
shared
and
everything
that
you've
done
and
thank
you
for
being
a
part
of
our
training
videos.